topic Re: macro in a loop in SAS Programming

macro in a loop

shixin — Mon, 09 Sep 2013 19:41:16 GMT

I created %macro report(year,quarter) which gives me a quarterly sales report.

To get results for the past 4 quarters, I did this:

%report(2013,3);

%report(2013,2);

%report(2013,1);

%report(2012,4);

However, I find it toublesome to always keep changing the year and quarter so I looped the macro:

data _NULL_;

count = 0;

do i = year(today()) to 2010 by -1 (until count ge 4);

do j = quarter(today()) to 1 by -1;

%report(i,j);

count = count + 1;

end;

run;

The errors I get are: Expression using = has components that are of different datatypes. The following columns were not found in the contributing tables i,j.

I tried using CALL SYMPUT but still didnt work, can someone pls help me Thank you.

Re: macro in a loop

Reeza — Mon, 09 Sep 2013 19:47:05 GMT

Try CALL EXECUTE to call the macro, instead of %report or put the loop into a macro loop and call that macro instead

Re: macro in a loop

sandyming — Wed, 11 Sep 2013 01:52:10 GMT

Try,

%do i...

%do j...

%report(&i, &j);

%end;

Re: macro in a loop

yaswanthj — Wed, 11 Sep 2013 09:41:04 GMT

Hi ..

You have to give this code with in the macro like below and try...

%macro test;

data _NULL_;

count = 0;

%do i = year(today()) %to 2010 by -1 (until count ge 4);

%do j = quarter(today()) %to 1 by -1;

%report(i,j);

count = count + 1;

%end;

run;

%mend;

%test;

this will definitely works ..

Thanks & Regards,

Yaswanth ..

Re: macro in a loop

Tom — Wed, 11 Sep 2013 11:00:50 GMT

That code cannot work as the it is mixing macro code and data step code.

First you have the calls to the macro nested inside of a DATA _NULL_ step, so even if you got the %DO loops corrected so that the macro generated multiple calls to %REPORT() it would require that the code that %REPORT() generates could work in a single data step. But I suspect from the name that the macro actually generates multiple data/proc steps. So you would end up with DATA; .... PROC ... and then when it sees the line COUNT = COUNT+1 floating after the last step generated by the last %REPORT() call it will complain as you cannot have an assignment statement outside of a DATA step.

Second take a look at what the first %DO loop actually says from the macro processors point of view.

%do i = year(today()) %to 2010 by -1 (until count ge 4);

%do i= This part looks reasonable as the beginning of an iterative %do loop using macro variable I as the loop counter.

year(today()) This part will fail as it cannot be interpreted as an initial value of the macro variable I. To the macro processor this is just a 13 character string. You could convert this to macro logic using %sysfunc(). %sysfunc(year("&date9"d))

%to 2010 This part looks good as it is setting the upper limit for the iteration.

by -1 This cannot work since it is not macro. If it used %BY then it would be ok as part of a %DO loop.

(until ... ) This cannot work in many ways. First the whole thing is just text and not anything the macro processor would process. Second the macro %DO loop cannot combine iterative and conditional termination criteria the way that the data step DO loop can. You must either use %DO .. %TO .. or %DO %UNTIL() or %DO %WHILE() but you cannot combine them in one %DO loop.

count ge 4 Again count is just text to the macro processor so if this did appear in a macro conditional, such as %eval(), it would see that the letter "c" is after the digit "4" in the ASCII coding scheme so this would always be true.

Re: macro in a loop

yaswanthj — Wed, 11 Sep 2013 11:54:54 GMT

Thanks TOM for giving clear idea...

Re: macro in a loop

shixin — Thu, 12 Sep 2013 13:06:29 GMT

So I either use data _NULL_ or make it into a macro?

I ran this loop and it worked:

data _NULL_;

count =0;

do i = year(today()) to 2010 by -1 until (count = 4);

if i = year(today()) then

do j = qtr(today()) to 1 by -1 until (count = 4);

n = i + j;

count + 1;

output;

end;

else

do j = 4 to 1 by -1 until (count = 4);

n = i + j;

count + 1;

output;

end;

run;

But when I replace the underlined statement with my macro %report(i,j), it stopped working because of different datatypes.

How do I change the datatype of a macro?

Re: macro in a loop

dhana — Thu, 12 Sep 2013 13:31:57 GMT

Another way of approaching the same problem. I have used 2 macros here one your actual report another one that calls the report macro 4 times.

%MACRO REPORT(YYYY,QTR);

%PUT #### &&YYYY ####;
%PUT #### &&QTR ####;

%MEND REPORT;

%MACRO CALL_REPORT;

%LET START_DT=%SYSFUNC(INTNX(QTR,%SYSFUNC(TODAY()),-4));

%DO I=0 %TO 3;
%LET DATE_INC=%SYSFUNC(INTNX(QTR,&START_DT,&I));
%LET YEAR1=%SYSFUNC(YEAR(&DATE_INC));
%LET QTR1=%SYSFUNC(QTR(&DATE_INC));
%REPORT(&YEAR1,&QTR1);
%END;

%MEND CALL_REPORT;

%CALL_REPORT;

Thanks

Dhana

Re: macro in a loop

sandyming — Thu, 12 Sep 2013 13:35:19 GMT

%macro report(i,j);

%put &i, &j;

%mend report;

data _NULL_;

count =0;

do i = year(today()) to 2010 by -1 until (count = 4);

if i = year(today()) then

do j = qtr(today()) to 1 by -1 until (count = 4);

call execute('%report('||i||','||j||')');

count + 1;

output;

end;

else

do j = 4 to 1 by -1 until (count = 4);

call execute('%report('||i||','||j||')');

count + 1;

output;

end;

run;

Re: macro in a loop

shixin — Thu, 12 Sep 2013 14:40:37 GMT

That was exactly what I wanted! Great!

So whenever we want to loop a macro in a data step, we have to use CALL EXECUTE?

Re: macro in a loop

Tom — Thu, 12 Sep 2013 14:52:07 GMT

CALL EXECUTE() is one way to generate code to run after the current step has completed.

Another way is to write the code into a text file using normal PUT statements and then %INCLUDE the file, this will avoid a lot of the macro quoting and timing issues of CALL EXECUTE()