Home > database >  SAS Macro looping through multiple 'yyyy-mm-dd' date format with single quotes
SAS Macro looping through multiple 'yyyy-mm-dd' date format with single quotes

Time:09-24

I need to loop through some date format like 'yyyy-mm-dd' in SAS Macro since my main query body uses Teradata SQL Pass-Thru however my code below is not working where %let wk_first_dt is not picking up 'yyyy-mm-dd' format. The error says

%MACRO DO_APPEND;
%let first_dt_list = '2020-03-11' '2020-03-18';
%local i wk_first_dt;
%do i=1 %to %sysfunc(countw(&first_dt_list));
%let wk_first_dt = %scan(&first_dt_list, &i);

...
proc sql
...

where BILL_DT >= Date &wk_first_dt
AND SL_INVC.BILL_DT <= (Date &wk_first_dt   7)
...
quit;


...

%END;
%MEND; 
%DO_APPEND;
ERROR: Literal contains unmatched quote.
ERROR: The macro DO_APPEND will stop executing.

Did a lot of research and I believe the issue was due to the single quotes in this format 'yyyy-mm-dd' since there is special treatment dealing with single quotes in SAS Macro. however the most popular recommendations like

%let first_dt_list = %str(%')yyyy-mm-dd.%str(%') 

won't work in my case. Please kindly point me to the right direction. Thanks in advance!

Btw, in the code above, if I change %scan(&first_dt_list, &i) to '2020-03-11', the whole Macro works - but i just need to loop through multiple dates. This makes me believe once 'yyyy-mm-dd' is passed to %let wk_first_dt, the issue would be fixed.

CodePudding user response:

Your %SCAN() function call is wrong.

75    %let list = '2020-03-11' '2020-03-18';
76    %put %qscan(&list,1);
'2020

Since you didn't tell %SCAN() what delimiter to use it used ANY of the default set of delimiters, which includes the hyphen.

Try telling it that only space should be used as the delimiter.

%do i=1 %to %sysfunc(countw(&first_dt_list, %str( )));
%let wk_first_dt = %scan(&first_dt_list, &i,%str( ));
  • Related