Hello, based on the type of report (operational or financial) I am trying to get the good starting date (see below)   For an operational report, the starting date (debut) should be equal to '02jan2017'd; For a financial report, the starting date (Debut) should be equal to '31oct2016'd;   Here's my code below but It does work and I don’t know Why. Does someone could help me with that issue?   %let motifrap=FINANCIER; *%let MotifRap=OPERATIONNEL; %let sysyear= %sysfunc(year("&sysdate"d)); %MACRO MotifRap; %if &motifrap 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; %MotifRap; %put &Annee; %put &FirstWDay; %put &FirstWorkingDay; %put &Debut; %put &motifrap;   ... View more

Asking the expert!   I would like to make multiple left join in order to create a new table.   One of my table is SoumEnLigne as table8 In this table, I have the variable primaryidentityclass which could take only two values (quote or account)   Therefore based on the value of this variable I would like to make two different left join ex: case 1, primaryidentityclass=quote then   on table1.POLICYNUMBER = table8.primaryentityid and datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT";   Case2, primaryidentityclass=account then   n table1.ACCOUNTNUMBER = table8.primaryentityid and datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT";   So if the primaryidentityclass eq quote the we compare the policynumber of table1 with the primaryidentityid of table8  if the primaryidentityclass eq account the we compare the accountnumber of table1 with the primaryidentityid of table8      How can we do that ?   Here's a part of my log file   53 on table1.POLICYNUMBER = table8.primaryentityid and 54 datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT"; 55 %end; 56 57 %if table8.primaryentityclass eq "Account" %then 58 %do; 59 60 on table1.ACCOUNTNUMBER = table8.primaryentityid and 61 datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT"; 62 63 %end; 64 ; 65 QUIT; 66 %mend provbqt1; 67 %provbqt1; MLOGIC(PROVBQT1): Début de l'exécution. MPRINT(PROVBQT1): proc sql; MLOGIC(PROVBQT1) : La condition %IF table8.primaryentityclass eq "Quote" est FALSE MLOGIC(PROVBQT1) : La condition %IF table8.primaryentityclass eq "Account" est FALSE NOTE: Line generated by the invoked macro "PROVBQT1". 67 ; _ 73 76 MPRINT(PROVBQT1): 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 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" 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 as table8 ; ERROR 73-322: Expecting an ON. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. MPRINT(PROVBQT1): QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): ... View more

Here's the problem.   I am dealing with a table which contains 271 variables and 3.5 Millions of observations. Moreover, I have no information on this table such as the meaning of each variable and the values each variable could take, especially the characters variables.   I have made a proc contents to obtain the number of observations and the list of variables.   I am interested by the value character variable could take.   Usually I made a proc sort nodupkey by variable x and I obtains the list of possible values for a variable could take.   My question is: is there a better way to do that with many variables and only the character variables   Regards,   Alain LePage   ... View more