DATA Step, Macro, Functions and more

Loop inside Macro not working

Reply
Contributor
Posts: 70

Loop inside Macro not working

Hi All,

 

I am new to the SAS Programming. I am trying to use macro loop inside another macro loop. The outer Macro loop not looping properly, but the inner macro loop works. The outer loop supposed to work 3 times. but working only once.Not sure what the problem is.

 

 

Count is 3 dates: Only one date is working.

 

%macro repeat;

%do i=0 %to &count %by 1;

/*%put first=%eval(&i);*/

/*%if &i. LT &count %then %goto continue;*/

/*%else %if &i. GE &count %then %goto leave;*/

/*%continue:*/

%cc(%eval(&i+1), %eval(&i+1))

%end;

/* %leave:*/

%mend;

%Repeat

 

I tried this way, and it works

%cc(1,1)

%cc(2,2)

%cc(3,3)

 

 

Thanks for the help.

Super User
Super User
Posts: 7,955

Re: Loop inside Macro not working

Posted in reply to Kalai2008

If your new to SAS, why are you doing macro?  Macro is an advanced topic for specific things.  Learn Base SAS which is the programming language.  We cannot tell from what you posted what is wrong, logs, full code, test data etc. all missing.  Start by formualting your question, post test data in the form of a datastep as to what you have, if you need help:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Then post what you want to have out.  And forget macro code it is not needed, I can guarentee you that.

Super User
Posts: 7,782

Re: Loop inside Macro not working

Posted in reply to Kalai2008

I don't see the definition of macro cc here. There's also no place where macro variable count is set.

And there is only one macro loop.

This:

%macro cc(i1,i2);
%put "i1=&i1";
%put "i2=&i2";
%mend;

%macro repeat(count);
%do i = 0 %to &count;
  %cc(%eval(&i+1),%eval(&i+1));
%end;
%mend;

%repeat(4)

produces this log:

24         %macro cc(i1,i2);
25         %put "i1=&i1";
26         %put "i2=&i2";
27         %mend;
28         
29         %macro repeat(count);
30         %do i = 0 %to &count;
31           %cc(%eval(&i+1),%eval(&i+1));
32         %end;
33         %mend;
34         
35         %repeat(4)
"i1=1"
"i2=1"
"i1=2"
"i2=2"
"i1=3"
"i2=3"
"i1=4"
"i2=4"
"i1=5"
"i2=5"
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 70

Re: Loop inside Macro not working

Posted in reply to KurtBremser

Thank you for the reply. Its a very big program, I cant paste entire program..I have base certification, just learning Macros.

Here's my Macro CC

-------------------------------------------------------------------------

%global vardate;

%let vardate=%str(work.testdate)

 

%macro cc(first,last);

data list ;

set &vardate (firstobs = &first OBS = &last);

run;

 

proc sql noprint;

select distinct "'"||trim(left(put(datepart(trans_date),date11.)))||"'" into: ddate

from list;

quit;

--------------------------

 

I want to pass three dates to the program(connected to oracle), so that for each date the inner loop (query) should run. I use proc append with force to append the results.

But only one ddate is working(31-DEC-2016) , the loops stops but no errors in the log.

 

I tried your logic too, but still didnt work

Super User
Posts: 11,343

Re: Loop inside Macro not working

Posted in reply to Kalai2008

And when you %put &ddate what do you see for a result?

 

Note if you don't use a group in the proc sql you only get the first value of the variable

 

See if this works any better

proc sql noprint;

select distinct "'"||trim(left(put(datepart(trans_date),date11.)))||"'" into: ddate separated by ' '

from list

group by trans_date;

quit;

 

You don't show us how you are using &ddate so that is guess as to if you want a space separated list or not.

Contributor
Posts: 70

Re: Loop inside Macro not working

Thank you for the reply and the solution.

 

How am I using:

proc sql..

connect to oracle..

create table..from..

where..

AND trunc(x.trans_date) = &ddate

 

My %put &ddate results is  '28-FEB-2017'. After using group by I see only one date in the dataset list instead of 3 dates. (Initially there were 3 dates before group by)

 

Error:

MPRINT(CC): proc sql noprint;

MPRINT(CC): select distinct "'"||trim(left(put(datepart(trans_date),date11.)))||"'" into: ddate from list;

180: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has

occurred.

ERROR 180-322: Statement is not valid or it is used out of proper order.

MPRINT(CC): group by trans_date;

 

 

Thank you for looking.

 

Respected Advisor
Posts: 4,173

Re: Loop inside Macro not working

[ Edited ]
Posted in reply to Kalai2008

@Kalai2008

Before you even get into "macrotizing" SAS code make sure that your SAS code as such is working in a version without any macro code around it.

 

In the code you've posted there is an Oracle TRUNC() function used in a SAS SQL (=not as SQL pass-through). That's not going to work!

 

proc sql..

connect to oracle..

create table..from..

where..

AND trunc(x.trans_date) = &ddate

 

I suggest you first implement one of the code versions you want the macro to generate in plain SAS code and make this work. Only then wrap a macro around it and start to replace bits and pieces with macro vars and macro code.

Frequently unit test your code while converting into a macro version as this will make it much easier for you to identify issues as you go.

 

For the code snippet you've posted to work you need either to use pass-through SQL and formulate it in Oracle syntax:

....and trunc(x.trans_date) = to_date('28-FEB-2017', 'DD-MON-YYYY')

 

Or you formulate it in SAS syntax using functions which SAS can push down to the database:

....and datepart(x.trans_date) = '28FEB2017'd

 

If using the SAS Datepart() function make sure it can get pushed to the database as else all the data will be transferred to the SAS server and only then the where clause will get applied (=potentially huge impact on performance)

https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0f64yzzxbsg8un1u...

 

And last but not least: If using SAS SQL when interacting with a database then set options which show you in the log what SAS actually was able to send to the database for processing.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

PROC Star
Posts: 7,474

Re: Loop inside Macro not working

Posted in reply to Kalai2008

I haven't looked closely at your code but, if you are selecting more than one date, you have to specify that in your into statement. e.g., rather than:

into: ddate

use

into: ddate separated by ' '

I used separated by a space, above, but that could also be a semi-colon or any other character you need.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 11,343

Re: Loop inside Macro not working

Posted in reply to Kalai2008

By any chance does your macro CC include it's own loop counter named i? Then you likely have a scope issue where AFTER CC runs the value of i now exceeds the loop counter in repeat.

 

Try adding

%put Before CC: &i;

%cc(%eval(&i+1), %eval(&i+1))

%put After CC: &i;

 

to see if that is what is happening. Check the log for the messages.

Ask a Question
Discussion stats
  • 8 replies
  • 221 views
  • 3 likes
  • 6 in conversation