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: 19,771

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: 7,039

Re: macro in a loop

Posted in reply to yaswanthj

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

Posted in reply to sandyming


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: 7,039

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

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

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