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..
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;
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;
Thank you for the reply...I changed the date format in SAS and it worked.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.