/******************************************************************************
 * Programme : Obtener los detalles de ejecución de una consulta FedSQL
 * Reference : OBTENE4B55
 * Source    : https://www.wearecas.eu/en/sampleCode/OBTENE4B55
 ******************************************************************************/

/* --- BLOC 1 --- */
data casuser.crimeCensus;
    infile datalines dlm=';';
    input primary_type $ date :yymmdd10. community_area hardship_index per_capita_income;
    format date yymmdd10.;
    datalines;
THEFT;2015-01-15;10;80;25000
THEFT;2015-02-20;10;80;25000
THEFT;2015-03-01;10;80;25000
THEFT;2015-04-10;10;80;25000
THEFT;2015-05-05;10;80;25000
THEFT;2016-01-20;10;80;25000
THEFT;2016-02-25;10;80;25000
THEFT;2016-03-05;10;80;25000
THEFT;2016-04-15;10;80;25000
THEFT;2016-05-10;10;80;25000
ASSAULT;2015-01-01;20;70;30000
ASSAULT;2015-01-02;20;70;30000
ASSAULT;2015-01-03;20;70;30000
ASSAULT;2015-01-04;20;70;30000
ASSAULT;2015-01-05;20;70;30000
ROBBERY;2016-06-01;30;90;20000
ROBBERY;2016-06-02;30;90;20000
ROBBERY;2016-06-03;30;90;20000
ROBBERY;2016-06-04;30;90;20000
ROBBERY;2016-06-05;30;90;20000
;
run;

proc cas;
  fedsql.execdirect 
  query="select primary_type, count(*) as count from crimeCensus group by primary_type order by count desc";
quit;


/* --- BLOC 2 --- */
proc cas;
  fedsql.execdirect 
  showStages=true 
  query="select primary_type, count(*) as count, community_area, 
       hardship_index, per_capita_income from crimeCensus 
       where primary_type='THEFT' 
       and date between date '2015-01-01' and date'2016-01-01'
       group by primary_type, community_area, hardship_index, per_capita_income
       having count(*) >= 5
       order by count desc";
quit;

/* --- BLOC 3 --- */
data casuser.population_data;
    infile datalines dlm=';';
    input community_area $ population;
    datalines;
10;50000
20;75000
30;60000
;
run;

proc cas;
  fedsql.execdirect 
  showStages=true 
  query="select c.primary_type, count(c.*) as total_incidents, 
       c.community_area, p.population, 
       (count(c.*) * 1000 / p.population) as incidents_per_1000 
       from crimeCensus as c 
       join population_data as p 
       on c.community_area = p.community_area 
       where c.primary_type in ('THEFT', 'ROBBERY') 
       and c.date between date '2015-01-01' and date '2016-12-31' 
       group by c.primary_type, c.community_area, p.population 
       having count(c.*) >= 2 
       order by incidents_per_1000 desc";
quit;

/* --- BLOC 4 --- */
proc cas;
    /* Créer la table crimeCensus si elle n'existe pas déjà (dupliqué pour l'autonomie) */
    data casuser.crimeCensus;
        infile datalines dlm=';';
        input primary_type $ date :yymmdd10. community_area hardship_index per_capita_income;
        format date yymmdd10.;
        datalines;
THEFT;2015-01-15;10;80;25000
THEFT;2015-02-20;10;80;25000
THEFT;2015-03-01;10;80;25000
THEFT;2015-04-10;10;80;25000
THEFT;2015-05-05;10;80;25000
THEFT;2016-01-20;10;80;25000
THEFT;2016-02-25;10;80;25000
THEFT;2016-03-05;10;80;25000
THEFT;2016-04-15;10;80;25000
THEFT;2016-05-10;10;80;25000
ASSAULT;2015-01-01;20;70;30000
ASSAULT;2015-01-02;20;70;30000
ASSAULT;2015-01-03;20;70;30000
ASSAULT;2015-01-04;20;70;30000
ASSAULT;2015-01-05;20;70;30000
ROBBERY;2016-06-01;30;90;20000
ROBBERY;2016-06-02;30;90;20000
ROBBERY;2016-06-03;30;90;20000
ROBBERY;2016-06-04;30;90;20000
ROBBERY;2016-06-05;30;90;20000
;
    run;

    /* Répliquer la table crimeCensus dans CAS pour une meilleure performance */
    table.replicate / 
        name='crimeCensus',
        caslib='casuser',
        target='crimeCensus_replicated',
        numreplicas=1; /* Répliquer sur un seul worker pour une petite table */

    /* Exécuter une requête FedSQL sur la table répliquée avec showStages */
    fedsql.execdirect 
    showStages=true 
    query="select primary_type, count(*) as count from crimeCensus_replicated group by primary_type order by count desc";
quit;