topic Re: ERROR in "case" proc sql in SAS Programming

ERROR in "case" proc sql

SASdevAnneMarie — Tue, 04 May 2021 15:25:42 GMT

Hello Experts,

I can't resolve the error in "case" of proc sql. My code seems to be correct :

%let mois=10;
%let jour=10;
%let annee_20=2020;
%let annee_21=2021;

proc sql;
    create table Prestations_RT as
    select distinct
        is_contrat,
        datepart(d_eff_sin)        as date_sinistre            format  = ddmmyy10.,
        year(datepart(d_bilan))    as annee_bilan,
        sum(mt_brut_prest * (cd_dossier = "DCASS"))        as Mt_DC,
        sum(mt_brut_prest * (cd_dossier = "DCCCS"))        as Mt_DC_rentes,
        sum(mt_brut_prest * (cd_dossier = "RT"))        as Mt_RT,
        sum(mt_brut_prest * (cd_dossier = "RTCCS"))        as Mt_RT_rentes,
        Case
            when (mdy(01,01,2020) <= datepart(date_sinistre) <= mdy(&mois.,&jour.,&annee_20.) then "T1_2020")
            when (mdy(01,01,2021) <= datepart(date_sinistre) <= mdy(&mois.,&jour.,&annee_21.) then "T1_2021")
        end                                                as Trimestre
    from Table
    where /*substr(is_contrat,1,4) in &Liste_produits.
    and */(mdy(01,01,2020) < (datepart(date_sinistre)) < mdy(&mois..,&jour.,&annee_20.))
    and (mdy(01,01,2021) < (datepart(date_sinistre)) < mdy(&mois.,&jour.,&annee_21.))
    group by
        is_contrat,
        year(datepart(d_bilan));
quit;

I have this error all time, I don't know why.

I attached the data.

Thank you for your help !

Re: ERROR in "case" proc sql

Kurt_Bremser — Tue, 04 May 2021 15:29:45 GMT

Just remove the unnecessary parentheses:

when mdy(01,01,2020) <= datepart(date_sinistre) <= mdy(&mois.,&jour.,&annee_20.) then "T1_2020"

The way you wrote it, the "then" was still part of the condition.

Re: ERROR in "case" proc sql

Kurt_Bremser — Tue, 04 May 2021 15:30:52 GMT

PS since we're talking SAS here, provide data as SAS datasets, not Excel spreadsheets.

Datasets are posted in code as data steps with datalines.

Re: ERROR in "case" proc sql

SASdevAnneMarie — Tue, 04 May 2021 15:52:09 GMT

Thank you, Kurt, I see !

Re: ERROR in "case" proc sql

SASdevAnneMarie — Tue, 04 May 2021 15:52:31 GMT

That works !
Thank you !