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 !
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.