/******************************************************************************
 * Programme : Beispiele für Joins in CASL und FedSQL
 * Reference : BEISPIF796
 * Source    : https://www.wearecas.eu/en/sampleCode/BEISPIF796
 ******************************************************************************/

/* --- BLOC 1 --- */
/* cas casauto terminate; */
cas;
caslib _all_ assign;

/* --- BLOC 2 --- */
data casuser.baseball_location;
   set sashelp.baseball;
   keep name team div division league position;
run;

/* --- BLOC 3 --- */
data casuser.baseball_stats;
   set sashelp.baseball;
   drop team div division league position ;
run;

/* --- BLOC 4 --- */
/* set the active CASLIB */
options caslib=casuser;

/* --- BLOC 5 --- */
/* FedSQL Left join example */
proc FEDSQL sessref=casauto;
     create table fedsql as
     select distinct a.div, a.division, a.league, a.position, a.team, b.*
	  from baseball_location as a left join
	  baseball_stats as b
         on a.name=b.name;
quit;

/* --- BLOC 6 --- */
/* CASL Join Examples */
proc cas;
loadactionset 'searchAnalytics';
loadactionset 'deepLearn';
quit;

/* --- BLOC 7 --- */
/* Deep Learning Action Set dljon action: Examples */
/* joinType="APPEND" | "FULL" | "INNER" | "LEFT" | "RIGHT" */
proc cas;
  deepLearn.dlJoin /       
      joinType="LEFT"                           
      annotatedTable={name="baseball_location"}
      casOut={name="dlJoin", replace=TRUE}
      id="name"
      table={name="baseball_stats"};
   run;
quit;

/* --- BLOC 8 --- */
/* searchAnalytics Action Set searchJoin action: Examples */
/* joinType="APPEND" | "FULL" | "INNER" | "LEFT" | "RIGHT" */
proc cas;
   searchAnalytics.searchJoin /   
      joinType="LEFT"                         
      casOut={name="searchJoin", replace=TRUE}
      leftTable={columns={{isKey=TRUE, name="name"},  
                          {name="name", 
                           reName="name_left"
                           }
                         }
                 table={name="baseball_location"}
                }
      rightTable={columns={{isKey=TRUE, name="name"},  
                           {name="name", 
                            reName="name_right"
                           }
                          }
                  table={name="baseball_stats"}
                 };
run;
quit;