/******************************************************************************
 * Programme : Descargar un archivo CSV local y aplicar un formato SAS
 * Reference : DESCAR8792
 * Source    : https://www.wearecas.eu/en/sampleCode/DESCAR8792
 ******************************************************************************/

/* --- BLOC 1 --- */
/* Création du fichier CSV local */
DATA _NULL_;
  FILE "/tmp/myusage_basic.csv" DSD LRECL=200;
  PUT 'account,high,low,recorddt,startdt,enddt,kwh';
  PUT '1000001,82,71,07/03/2013,07/01/2013,07/02/2013,54';
  PUT '1000001,85,70,07/02/2013,06/30/2013,07/01/2013,70';
  PUT '1000001,85,68,07/01/2013,06/29/2013,06/30/2013,78';
RUN;

/* Connexion au serveur CAS (remplacer par vos infos) */
*options cashost='cloud.example.com' casport=5570;
cas casauto;

proc cas;
   session casauto;
   upload path="/tmp/myusage_basic.csv"
          casout={name="myusage_basic", replace=True},
          importOptions={
             fileType="csv",
             getNames=True,
             vars={
                recorddt={informat="mmddyy10.", format="nldate20."},
                startdt={informat="mmddyy10.", format="nldate20."},
                enddt={informat="mmddyy10.", format="nldate20."}
            }};

   print myusage_basic(obs=3);
run;
quit;

/* Suppression du fichier CSV temporaire */
x "rm /tmp/myusage_basic.csv";

/* --- BLOC 2 --- */
/* Création du fichier CSV local avec plus de données */
DATA _NULL_;
  FILE "/tmp/myusage_inter.csv" DSD LRECL=200;
  PUT 'account,high,low,recorddt,startdt,enddt,kwh,cost';
  PUT '1000001,82,71,07/03/2013,07/01/2013,07/02/2013,54,12.50';
  PUT '1000001,85,70,07/02/2013,06/30/2013,07/01/2013,70,15.75';
  PUT '1000002,85,68,07/01/2013,06/29/2013,06/30/2013,78,20.00';
  PUT '1000002,90,70,06/30/2013,06/28/2013,06/29/2013,72,18.25';
  PUT '1000003,88,68,06/29/2013,06/27/2013,06/28/2013,54,10.00';
RUN;

/* Connexion au serveur CAS (remplacer par vos infos) */
*options cashost='cloud.example.com' casport=5570;
cas casauto;

proc cas;
   session casauto;
   upload path="/tmp/myusage_inter.csv"
          casout={name="myusage_inter", replace=True, promote=True},
          importOptions={
             fileType="csv",
             getNames=True,
             vars={
                recorddt={informat="mmddyy10.", format="nldate20."},
                startdt={informat="mmddyy10.", format="nldate20."},
                enddt={informat="mmddyy10.", format="nldate20."},
                cost={informat="comma8.2", format="dollar10.2"}
            }};

   table.columnInfo result=r / table={name='myusage_inter'};
   print r;

   print myusage_inter;
run;
quit;

/* Suppression du fichier CSV temporaire */
x "rm /tmp/myusage_inter.csv";

/* --- BLOC 3 --- */
/* Création du fichier CSV local */
%LET CSV_FILE = /tmp/mydata_advanced_&sysdate._%sysfunc(compress(&systime.)).csv;
%LET CAS_TABLE = mydata_advanced;
%LET CAS_LIB = CASUSER;

DATA _NULL_;
  FILE "&CSV_FILE" DSD LRECL=200;
  PUT 'ID,Category,Value,EntryDate';
  PUT '1,A,123.45,12-JAN-2023';
  PUT '2,B,678.90,01-FEB-2023';
  PUT '3,A,111.22,03-MAR-2023';
  PUT '4,C,333.33,15-APR-2023';
RUN;

/* Connexion au serveur CAS (remplacer par vos infos) */
*options cashost='cloud.example.com' casport=5570;
cas casauto;

proc cas;
   session casauto;
   /* Vérifier si la table existe déjà et la supprimer si nécessaire */
   if table.tableInfo(caslib="&CAS_LIB", name="&CAS_TABLE") then do;
      table.dropTable caslib="&CAS_LIB" name="&CAS_TABLE";
   end;

   upload path="&CSV_FILE"
          casout={name="&CAS_TABLE", caslib="&CAS_LIB", replace=True, promote=True},
          importOptions={
             fileType="csv",
             getNames=True,
             vars={
                EntryDate={informat="date9.", format="nldate20."},
                Value={informat="comma10.2", format="dollar10.2"}
            }};

   print &CAS_TABLE;
run;
quit;

/* Supprimer le fichier CSV temporaire après utilisation */
x "rm &CSV_FILE";

/* --- BLOC 4 --- */
/* Création du fichier CSV local */
DATA _NULL_;
  FILE "/tmp/transactions.csv" DSD LRECL=200;
  PUT 'TransactionID,CustomerID,Amount,TransactionDate,ProductCode';
  PUT '1,C001,150.75,2023-01-15,PROD001';
  PUT '2,C002,230.00,2023-01-16,PROD002';
  PUT '3,C001,50.25,2023-01-15,PROD003';
  PUT '4,C003,100.00,2023-01-17,PROD001';
  PUT '5,C002,75.50,2023-01-16,PROD004';
RUN;

/* Connexion au serveur CAS (remplacer par vos infos) */
*options cashost='cloud.example.com' casport=5570;
cas casauto;

proc cas;
   session casauto;
   caslib _all_ assign;

   /* 1. Charger le fichier CSV */
   upload path="/tmp/transactions.csv"
          casout={name="raw_transactions", replace=True, promote=True},
          importOptions={
             fileType="csv",
             getNames=True,
             vars={
                TransactionDate={informat="yymmdd10.", format="nldate20."},
                Amount={informat="comma10.2", format="dollar10.2"}
            }};

   /* 2. Effectuer une agrégation avec FedSQL sur la table CAS */
   fedsql.execDirect result=res / query="
      CREATE TABLE aggregated_transactions AS
      SELECT
         CustomerID,
         COUNT(TransactionID) AS TotalTransactions,
         SUM(Amount) AS TotalAmountSpent
      FROM raw_transactions
      GROUP BY CustomerID
      ORDER BY TotalAmountSpent DESC;
   ";
   print res;

   /* 3. Sauvegarder la table agrégée dans une caslib (ex: CASUSER) */
   table.save result=saveres / 
      table={name="aggregated_transactions", caslib="CASUSER"},
      name="aggregated_transactions.sashdat",
      replace=True;
   print saveres;

   /* 4. Afficher les résultats agrégés */
   print aggregated_transactions;
run;
quit;

x "rm /tmp/transactions.csv";