BookmarkSubscribeRSS Feed
Kalai2008
Pyrite | Level 9

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.

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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"
Kalai2008
Pyrite | Level 9

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

ballardw
Super User

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.

Kalai2008
Pyrite | Level 9

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.

 

Patrick
Opal | Level 21

@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;

art297
Opal | Level 21

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

 

ballardw
Super User

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.

cameronmuir
Calcite | Level 5
This is exactly what happened in my sub-macro: resolved higher in sub and then finished in outer without further execution.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2120 views
  • 4 likes
  • 7 in conversation