/******************************************************************************
 * Programme : Joining Tables from Multiple Caslibs with fedSql.execDirect
 * Reference : JOININ3A47
 * Source    : https://www.wearecas.eu/de/sampleCode/JOININ3A47
 ******************************************************************************/

/* --- BLOC 1 --- */
/* --- Données de démonstration pour l'exemple 1 --- */
data Customers_data;
    infile datalines dsd;
    input CUSTID NAME $ COUNTRY $;
    datalines;
1,Peter Frank,USA
2,Jim Stewart,USA
3,Janet Chien,Japan
4,Qing Ziao,Japan
5,Humberto Sertu,Argentina
;
run;

data Sales_data;
    infile datalines dsd;
    input PRODID CUSTID TOTALS;
    datalines;
3421,4,781183
1424,3,555789
3975,5,899453
3421,2,2789654
3234,1,189400
1424,1,123456
3422,2,987654
;
run;

proc casutil;
    load data=Customers_data outcaslib="casuser" casout="Customers_Basic" replace;
    load data=Sales_data outcaslib="casuser" casout="Sales_Basic" replace;
quit;

/* --- Exemple SAS --- */
proc cas;
    fedSql.execDirect query="
        create table results_basic {options replace=true} as
        select
            C.CUSTID,
            C.NAME,
            S.TOTALS
        from
            casuser.Customers_Basic as C,
            casuser.Sales_Basic as S
        where
            C.CUSTID = S.CUSTID
    ";
    fedSql.execDirect query="select * from results_basic";
quit;

/* --- BLOC 2 --- */
/* --- Données de démonstration pour l'exemple 2 --- */
data Customers_data;
    infile datalines dsd;
    input CUSTID NAME $ COUNTRY $;
    datalines;
1,Peter Frank,USA
2,Jim Stewart,USA
3,Janet Chien,Japan
4,Qing Ziao,Japan
5,Humberto Sertu,Argentina
;
run;

data Products_data;
    infile datalines dsd;
    input PRODID PRODUCT $;
    datalines;
3234,Rice
1424,Corn
3421,Wheat
3422,Oat
3975,Barley
;
run;

data Sales_data;
    infile datalines dsd;
    input PRODID CUSTID TOTALS;
    datalines;
3421,4,781183
1424,3,555789
3975,5,899453
3421,2,2789654
3234,1,189400
1424,1,123456
3422,2,987654
;
run;

proc casutil;
    load data=Customers_data outcaslib="casuser" casout="Customers_Common" replace;
    load data=Products_data outcaslib="casuser" casout="Products_Common" replace;
    load data=Sales_data outcaslib="casuser" casout="Sales_Common" replace;
quit;

/* --- Exemple SAS --- */
proc cas;
    fedSql.execDirect query="
        create table results_common {options replace=true} as
        select
            P.PRODUCT,
            C.NAME,
            S.TOTALS,
            C.COUNTRY
        from
            casuser.Products_Common as P,
            casuser.Sales_Common as S,
            casuser.Customers_Common as C
        where
            P.PRODID = S.PRODID and
            C.CUSTID = S.CUSTID and
            C.COUNTRY = 'USA'
        order by S.TOTALS desc
    ";
    fedSql.execDirect query="select * from results_common";
quit;

/* --- BLOC 3 --- */
/* --- Données de démonstration pour l'exemple 3 --- */
data Customers_data;
    infile datalines dsd;
    input CUSTID NAME $ COUNTRY $;
    datalines;
1,Peter Frank,USA
2,Jim Stewart,USA
3,Janet Chien,Japan
4,Qing Ziao,Japan
5,Humberto Sertu,Argentina
;
run;

data Products_data;
    infile datalines dsd;
    input PRODID PRODUCT $;
    datalines;
3234,Rice
1424,Corn
3421,Wheat
3422,Oat
3975,Barley
;
run;

data Sales_data;
    infile datalines dsd; 
    input PRODID CUSTID TOTALS;
    datalines;
3421,4,781183
1424,3,555789
3975,5,899453
3421,2,2789654
3234,1,189400
1424,1,123456
3422,2,987654
;
run;

proc casutil;
    load data=Customers_data outcaslib="casuser" casout="Customers_Adv" replace;
    load data=Products_data outcaslib="casuser" casout="Products_Adv" replace;
    load data=Sales_data outcaslib="casuser" casout="Sales_Adv" replace;
quit;

/* --- Exemple SAS --- */
proc cas;
    fedSql.execDirect query="
        create table top_selling_products {options replace=true} as
        select
            P.PRODUCT,
            SUM(S.TOTALS) as TOTAL_SALES
        from
            casuser.Products_Adv as P,
            casuser.Sales_Adv as S
        where
            P.PRODID = S.PRODID
        group by
            P.PRODUCT
        having
            SUM(S.TOTALS) > 1000000
        order by
            TOTAL_SALES desc
    ";
    fedSql.execDirect query="select * from top_selling_products";
quit;

/* --- BLOC 4 --- */
/* --- Données de démonstration pour l'exemple 4 --- */
data Customers_data;
    infile datalines dsd;
    input CUSTID NAME $ COUNTRY $;
    datalines;
1,Peter Frank,USA
2,Jim Stewart,USA
3,Janet Chien,Japan
4,Qing Ziao,Japan
5,Humberto Sertu,Argentina
;
run;

data Sales_data;
    infile datalines dsd;
    input PRODID CUSTID TOTALS;
    datalines;
3421,4,781183
1424,3,555789
3975,5,899453
3421,2,2789654
3234,1,189400
1424,1,123456
3422,2,987654
;
run;

proc casutil;
    load data=Customers_data outcaslib="casuser" casout="Customers_Viya" replace;
    load data=Sales_data outcaslib="casuser" casout="Sales_Viya" replace;
quit;

/* --- Exemple SAS --- */
proc cas;
    /* Créer une vue FedSQL sur des tables CAS */
    fedSql.execDirect query="
        create view customer_sales_view {options replace=true} as
        select
            C.NAME,
            C.COUNTRY,
            S.TOTALS
        from
            casuser.Customers_Viya as C,
            casuser.Sales_Viya as S
        where
            C.CUSTID = S.CUSTID
    ";

    /* Interroger la vue FedSQL */
    fedSql.execDirect query="select * from customer_sales_view where TOTALS > 500000";
quit;