Macro Loop error

[Kalai2008]

Hi all,

Can somebody help me..I was trying to pass 20 dates in a loop inside a oracle query...But getting oracle error

ERROR: ORACLE execute error: ORA-01843: not a valid month.

%global vardate;
%let vardate=%str(work.cdate);cdate contains 20 dates (30NOV2016) that I need to run in a loop

%macro rep(first,last);
data listdate ;
    set &vardate (firstobs = &first OBS = &last);
run;

proc sql noprint;
select distinct "'"||trim(left(put(cs_date,8.)))||"'"  into: CLISTdate (I tried 10. ERROR: ORACLE execute error: ORA-01861: literal does not match format string.)
from listdate;
quit;

Proc Sql;
connect to oracle...
create table...
(
select *
from..where..
and c.date= &CLISTdate
)
proc sql;
select count(distinct cs_date) into: count1
from &vardate;
quit;

%put &count1;

%macro act;
%do i=0 %to &count1 %by 100; 
%put first=%eval(&i);
%if &i. LT &count1 %then %goto continue;
%else %if &i. GE &count1 %then %goto leave;
%continue:
%rep(%eval(&i+1), %eval(&i+100))
%end;
%leave:
%put
%mend;
%act

Thanks for the help..

[Tom · Accepted solution]

First get the ORACLE syntax that works. Then figure out how to generate the code using macro logic and/or macro variables.

If you have your dates in DATE9 format

%let datelist=30NOV2016 31DEC2016 31JAN2017;

then to use them in SAS code you will need to convert them in date literals like "30NOV2016"d.

I suspect that to use them in ORACLE at a minimum you will need enclose them in single quotes. You might even need to convert them into a different display format for dates like '2016-11-30' instead of '30NOV2016'.

If you just want to loop over a space delimited list of words that are in a macro variable then you could use logic like this. It needs to be inside of a macro to be able to use %DO loop.

%do i=1 %to %sysfunc(countw(&datelist));
  %let datetext=%scan(&datelist,&i);
  %let sasdate="&datetext"d ;
  %let oracledate = %unquote(%str(%')&datetext%str(%'));
  .....
%end;

[Kalai2008]

Thank you for the reply...I changed the date format in SAS and it worked.