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( ));