DATA Step, Macro, Functions and more

macro in a loop

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

macro in a loop

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.


Accepted Solutions
Solution
‎09-12-2013 09:35 AM
Contributor
Posts: 32

Re: macro in a loop

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

View solution in original post


All Replies
Super User
Posts: 17,784

Re: macro in a loop

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

Contributor
Posts: 32

Re: macro in a loop

Try,

%do i...

          %do j...

               %report(&i, &j);

%end;

%end;

Contributor
Posts: 70

Re: macro in a loop

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

Super User
Super User
Posts: 6,499

Re: macro in a loop

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.


Contributor
Posts: 70

Re: macro in a loop

Thanks TOM for giving clear idea...

Contributor
Posts: 22

Re: macro in a loop

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?

Frequent Contributor
Posts: 75

Re: macro in a loop

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

Solution
‎09-12-2013 09:35 AM
Contributor
Posts: 32

Re: macro in a loop

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

Contributor
Posts: 22

Re: macro in a loop


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?

Super User
Super User
Posts: 6,499

Re: macro in a loop

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 572 views
  • 8 likes
  • 6 in conversation