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;
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.
%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;
end;
run;
Try CALL EXECUTE to call the macro, instead of %report or put the loop into a macro loop and call that macro instead
Try,
%do i...
%do j...
%report(&i, &j);
%end;
%end;
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;
%end;
run;
%mend;
%test;
this will definitely works ..
Thanks & Regards,
Yaswanth ..
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.
Thanks TOM for giving clear idea...
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;
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?
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
%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;
end;
run;
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?
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()
%macro report (name);
%put Running report for NAME=&name ;
%mend ;
filename code temp;
data _null_;
set sashelp.class (obs=5);
file code ;
put '%report(' name= ');' ;
run;
%inc code / source2 ;
18 %inc code / source2 ;
NOTE: %INCLUDE (level 1) file CODE is file .../#LN00033.
19 +%report(Name=Alfred );
Running report for NAME=Alfred
20 +%report(Name=Alice );
Running report for NAME=Alice
21 +%report(Name=Barbara );
Running report for NAME=Barbara
22 +%report(Name=Carol );
Running report for NAME=Carol
23 +%report(Name=Henry );
Running report for NAME=Henry
NOTE: %INCLUDE (level 1) ending.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.