topic proc sql: the where clause in SAS Programming

proc sql: the where clause

SASdevAnneMarie — Fri, 05 Feb 2021 18:43:21 GMT

Hello Experts,

I'm wondering about the "where" clause in sql code. I need to apply the where clause in the beginning only for the PSAEU11.DB_DOSSIER data, but I suppose that my syntaxe is not right.


proc  sql;
create table test as select rga.cd_rga,sousc.no_police from PSAEU11.db_dossier sousc (where sousc.cd_dossier = 'SOUSC' and sousc.lp_etat_doss not in ('ANNUL','A30','IMPAY') and sousc.no_police = 'I99300001') left join  PSAEU11.db_garantie gar on sousc.is_protocole=gar.is_protocole and gar.cd_garantie='DEDIE' 
    left join PSAEU11.db_ctrat_support ctrats on ctrats.is_garantie = gar.is_garantie and ctrats.is_dossier = sousc.is_dossier 
    left join PSAEU11.db_param_mandat_op mandaop on ctrats.is_param_mandat_op = mandaop.is_param_mandat_op  
    left join PSAEU11.db_rga rga on mandaop.is_rga=rga.is_rga ;
	quit;

Thank you for your help !

Re: proc sql: the where clause

PhilC — Fri, 05 Feb 2021 18:55:03 GMT

This is where some SAS is interjected into SQL syntax for PROC SQL, namely the SAS dataset options. Try this

replace this

/*...*/
from PSAEU11.db_dossier sousc (where sousc.cd_dossier = 'SOUSC' and sousc.lp_etat_doss not in ('ANNUL','A30','IMPAY') and sousc.no_police = 'I99300001') left join  /*...*/

with this

/*...*/
      from PSAEU11.db_dossier (where=(cd_dossier = 'SOUSC' 
                                  and lp_etat_doss not in ('ANNUL','A30','IMPAY') 
                                  and no_police = 'I99300001')) as sousc left join /*...*/

This little example works as well

proc sql;
   select cars.Make, cars.model
     from SASHELP.CARS(where=(Make="Audi")) as cars;
quit;

Re: proc sql: the where clause

utrocketeng — Fri, 05 Feb 2021 18:56:01 GMT

give this a whirl...

proc  sql;
create table test as 
select rga.cd_rga,sousc.no_police 
from 
	(SELECT * FROM PSAEU11.db_dossier where cd_dossier = 'SOUSC' and lp_etat_doss not in ('ANNUL','A30','IMPAY') and no_police = 'I99300001') sousc
	left join PSAEU11.db_garantie gar on sousc.is_protocole=gar.is_protocole and gar.cd_garantie='DEDIE' 
    left join PSAEU11.db_ctrat_support ctrats on ctrats.is_garantie = gar.is_garantie and ctrats.is_dossier = sousc.is_dossier 
    left join PSAEU11.db_param_mandat_op mandaop on ctrats.is_param_mandat_op = mandaop.is_param_mandat_op  
    left join PSAEU11.db_rga rga on mandaop.is_rga=rga.is_rga 
;
quit;