if it is zero ,then table is empty .   %let dsid=%sysfunc(open(sashelp.class)); %let nobs=%sysfunc(attrn(&dsid,nlobs)); %let dsid=%sysfunc(close(&dsid)); %put nobs= &nobs ; ... View more

It depends on how you supply the week number.  In your original example, you do it with:   %let week2=35;   If that's going to be the way you supply a value, you would use:   set have(keep=prov %sysfunc( putn(&week2,wkVarName.) ) ;   The example provided by @AhmedAl_Attar assumes that you want the program to calculate the value for &WEEK2 based on the date, instead of supplying it with a %LET statement.   ... View more

Macro language won't need quotes around "v" so the middle statement becomes:   %let week = %sysfunc(week(&today, v)); ... View more

Why have a parameter which tells the code which library to look at?  That just seems like an unnecessary step.  Code you program to look  at a library, the only switch needs to be where you assign the library: libname curr "%sysfunc(pathname(symu))"; /* Replace with this for prod libname curr "%sysfunc(pathname(i2symu))"; */ proc sql; ... from CURR....; quit; ... View more

I have found a solution... Here's the code that is working   /*options mprint symbolgen;*/ %MACRO MotifRap; %global Annee FirstWday FirstWorkingDay Debut; %let sysyear= %sysfunc(year("&sysdate"d)); %if &MotifRapport EQ FINANCIER %THEN %DO; %let Annee=&sysyear; %let FirstWDay=%sysfunc(NWKDOM(1,2,1,&Annee)); %let FirstWorkingDay=%sysfunc(inputn(&FirstWDay, z8.), date9.); %let Debut=%sysfunc(compress(%bquote('&FirstWorkingDay'd))); %END; %ELSE %DO; %let Annee=%eval(&sysyear-1); %let Debut=%sysfunc(compress(%BQUOTE('31OCT&Annee.'D))); %END; RUN; %mend MotifRap; %let MotifRapport = FINANCIER; %put &MotifRapport; %MotifRap; %put &Annee &FirstWDay &FirstWorkingDay &Debut; %let MotifRapport = OPERATIONAL; %put &MotifRapport; %MotifRap; %put &Annee &Debut; ... View more

I am not sure I understand what your code is doing.   I have tryed two consecutive join as below.  What do you think about that?   proc sql; create table provnbqt1 as select table1.*, table5.dtcreationVIP, table5.hrcreationVIP, table5.VIP, table6.clicassure, datepart(table6.started) as dtcreationCLA format yymmdd10., timepart(table6.started) as hrcreationCLA format time8., table7.rentecoll, datepart(table7.started) as dtcreationRCO format yymmdd10., timepart(table7.started) as hrcreationRCO format time8., table8.web FROM provnbqt as table1 left join ref_vip as table5 on table1.ACCOUNTNUMBER = table5.ACCOUNT_NUMBER and table1.dttrnsys = table5.dtcreationVIP /*201607 CP je veux ajouter la provenace clic assure la note est au niveau account et on a un delai de 7 jours admissible on associe que les soum on va passer par une provenance tempo pour les ventes*/ left join clicassure as table6 on table1.ACCOUNTNUMBER = table6.primaryentityid and datepart(table6.started)<=table1.dttrnsys<=sum(datepart(table6.started),7)/* and table1.type_lot="QT"*/ /*201609 CP je veux ajouter la provenace rente collective la note est au niveau account et on a un delai de 7 jours admissible on associe que les soum on va passer par une provenance tempo pour les ventes*/ left join rente_coll as table7 on table1.ACCOUNTNUMBER = table7.primaryentityid and datepart(table7.started)<=table1.dttrnsys<=sum(datepart(table7.started),7) /*and table1.type_lot="QT"*/ left join SoumEnLigne (where=( PRIMARYENTITYCLASS eq "Quote")) as table8 on table1.POLICYNUMBER = table8.primaryentityid and datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) left join SoumEnLigne (where =(PRIMARYENTITYCLASS eq "Account")) as table9 on table1.ACCOUNTNUMBER = table9.primaryentityid and datepart(table9.started)<=table1.dttrnsys<=sum(datepart(table9.started),7) /*and table1.type_lot="QT"*/ ; QUIT;         ... View more