DATA Step, Macro, Functions and more

Macro Loop error

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Macro Loop error

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..


Accepted Solutions
Solution
‎04-04-2017 01:13 PM
Super User
Super User
Posts: 7,077

Re: Macro Loop error

Posted in reply to Kalai2008

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;

 

View solution in original post


All Replies
Solution
‎04-04-2017 01:13 PM
Super User
Super User
Posts: 7,077

Re: Macro Loop error

Posted in reply to Kalai2008

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;

 

Contributor
Posts: 70

Re: Macro Loop error

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 140 views
  • 0 likes
  • 2 in conversation