/******************************************************************************
 * Programme : Tabellenerstellung über SELECT mit SAS/ACCESS
 * Reference : TABELL44D3
 * Source    : https://www.wearecas.eu/fr/sampleCode/TABELL44D3
 ******************************************************************************/

/* --- BLOC 1 --- */
proc delete data=mydblib.RDTAB78; run;
proc delete data=mydblib.CRTAB78A; run;
proc delete data=mydblib.CRTAB78B; run;

/* --- BLOC 2 --- */
data mydblib.RDTAB78;
do x = 1 to 10;
do y = 1 to 10;
output; end; end; run;

/* --- BLOC 3 --- */
options nodbidirectexec;

proc sql noerrorstop;

 create table mydblib.CRTAB78A as
  select y from mydblib.RDTAB78
  where x gt 5 order by y;

 /* WITH IP TRIGGER */
 create table mydblib.CRTAB78B as
  select distinct y from mydblib.RDTAB78
  where x gt 5 order by y;

 quit;

/* --- BLOC 4 --- */
data work.noexeA; set mydblib.CRTAB78A; by y; run;
data work.noexeB; set mydblib.CRTAB78B; by y; run;

/* --- BLOC 5 --- */
proc delete data=mydblib.CRTAB78A; run;
proc delete data=mydblib.CRTAB78B; run;

/* --- BLOC 6 --- */
options dbidirectexec;

proc sql noerrorstop;

 create table mydblib.CRTAB78A as
  select y from mydblib.RDTAB78
  where x gt 5 order by y;



 create table mydblib.CRTAB78B as
  select distinct y from mydblib.RDTAB78
  where x gt 5 order by y;

 quit;

/* --- BLOC 7 --- */
data work.exeA; set mydblib.CRTAB78A; by y; run;
data work.exeB; set mydblib.CRTAB78B; by y; run;

/* --- BLOC 8 --- */
proc compare base=work.noexeA
             comp=work.exeA
             error briefsummary note;run;

proc compare base=work.exeB
             comp=work.exeB
             error briefsummary note;run;