BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shixin
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
sandyming
Calcite | Level 5

%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

10 REPLIES 10
Reeza
Super User

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

sandyming
Calcite | Level 5

Try,

%do i...

          %do j...

               %report(&i, &j);

%end;

%end;

yaswanthj
Calcite | Level 5

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

Tom
Super User Tom
Super User

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.


yaswanthj
Calcite | Level 5

Thanks TOM for giving clear idea...

shixin
Calcite | Level 5

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?

dhana
Fluorite | Level 6

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

sandyming
Calcite | Level 5

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

shixin
Calcite | Level 5


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?

Tom
Super User Tom
Super User

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.

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
  • 10 replies
  • 1757 views
  • 8 likes
  • 6 in conversation