/******************************************************************************
 * Programme : Optimisation SGBD : Maîtrisez le SQL Pass-Through et les moteurs SAS/ACCESS
 * Reference : EXEMPL0672
 * Source    : https://www.wearecas.eu/en/sampleCode/EXEMPL0672
 ******************************************************************************/

/* --- BLOC 1 --- */
filename chol temp;
proc http
     url="http://support.sas.com/documentation/onlinedoc/viya/exampledatasets/cholesterol.csv"
     out=chol;
quit;
proc import datafile=chol
     out=work.mycholesterol
     dbms=csv
     replace;
run;
proc print data=work.mycholesterol;
run;

/* --- BLOC 2 --- */
options validvarname=v7;
proc import datafile='file-path/cholesterol.xlsx'
     dbms=xlsx
     out=work.mycholesterol
     replace;
run;

/* --- BLOC 3 --- */
libname mytddata teradata server=mytera user=myid password=mypw;
data mytddata.grades;
   input student $ test1 test2 final;
   datalines;
Fred 66 80 70
Wilma 97 91 98
;
proc datasets library=mytddata;
run;
quit;

/* --- BLOC 4 --- */
libname viewlib v9 'library-path';
proc sql;
   create view viewlib.mygrades as
      select *
         from mytddata.grades
         using libname mytddata teradata
                       server=mytera
                       user=myid password=mypw;
quit;
proc print data=viewlib.mygrades noobs;
run;

/* --- BLOC 5 --- */
proc sql;
   connect to teradata as myconn (server=mytera
      user=myid password=mypw);
   select *
      from connection to myconn
         (select *
            from grades
            where final gt 90);
   disconnect from myconn;
quit;

/* --- BLOC 6 --- */
filename nhl 'file-path/nhl.xml';
filename map 'file-path/nhlgenerate.map';
libname nhl xmlv2 automap=replace xmlmap=map;
proc print data=nhl.team noobs;
   var TEAM_name TEAM_abbrev;
run;

/* --- BLOC 7 --- */
libname mydata json '/file-path/example.json';
proc datasets lib=mydata;
run;
quit;