Nested Macro in Do Loop?

[SASnoobie101]

Hi,

First time poster here, need help!

I'm trying to append a bunch of output tables from my macro. Do i do this with a nested macro in a do loop? or some other way?

My macro is 

%macro yearmonth(YM=);

blah blah code

%mend;

The parameter for the macro is yearmonth so like 202204. So the final table output is 1 month depending on the YM i put in like 202204. But I want append a whole bunch of months together 12 from each year. 

How do I make this macro run on a loop for each month from like 202101 to 202204 and then append them altogether?

I'm a noob with do loops so please help! thank you very much. 

[ballardw]

Show code that does what you want for 3 values of YM that works without ANY macro coding or variables.

First step in writing a macro is to have non-macro code that works. Without seeing how you are using this is not easy to guess the best, or even a working approach.

Aside: if you have "date" values that are simple numeric values like 202204 and not actual SAS dates you are going to cause yourself a lot of extra work in the long run. There are many types of grouping that can be accomplished simply by changing the format applied to a date value. Also SAS supplies a lot of functions to deal with date (and time or datetime) values. When you short-change yourself by using something like 202204 you end up having to recreate the wheels that SAS already provides.

[PaigeMiller]

Agreeing with @ballardw , in particular this part:

First step in writing a macro is to have non-macro code that works.

You, @SASnoobie101 need to do this work first before trying to create a macro. If you don't have working code without macros, it will never work with macros.

[SASnoobie101]

I do have macro code that works, but that's not what is important here as I said the problem isn't getting it to work. it is appending the output tables. That is why I didn't include the code for the macro.

[Reeza]

---

@SASnoobie101 wrote:
I do have macro code that works, but that's not what is important here as I said the problem isn't getting it to work. it is appending the output tables. That is why I didn't include the code for the macro.

---

In that case, the most efficient thing is often to add the append at the end of the macro code so it happens as part of the process.

SAS - Case Study - How to add data to a regular report and then report on it daily

https://gist.github.com/statgeek/353374a5d8ea4f0c89ce5d80a47f4a4c

[SASnoobie101]

The macro code works fine when i put in values for the parameters such as 202202, 202203, 202204. But my question is how to do I append these tables in a loop?  there's no way to loop the dates so it goes from 202101 to 202203? 

I found something on stack overflow that says to do this:

%macro loop(start_month=, stop_month=);
    %local month;
    %do month=&start_month %to &stop_month;
        %put Month: &month;

        %* SQL CODE HERE....

        %* SPECIAL CASE WHEN WE REACH END OF A YEAR;
        %if %substr(&month, 5, 2) = 12 %then %let month = %eval(&month + 88);       
    %end;
%mend loop;

%loop(start_month=200301, stop_month=201502)

 but when i plug in my macro in the middle %yearmonth(&YM); it doesn't work.  Do you know why?

%macro loop(start_month=, stop_month=);
    %local month;
    %do month=&start_month %to &stop_month;
        %put Month: &month;
        %yearmonth(&YM);
        %if %substr(&month, 5, 2) = 12 %then %let month = %eval(&month + 88);       
    %end;
%mend loop;
%loop(start_month=202101, stop_month=202203)

[PaigeMiller]

"It doesn't work"

We have no idea why you say this. You know what the errors are, but don't tell us. So from now on, when something doesn't work, don't say "it doesn't work" and stop there. Provide details. If there are errors in the log, then show us the entire LOG for this macro (or at least the log for the first 3 iterations). First, run this code which turns on the macro debugging options:

options mprint;

Then run the macro again, and copy the log as text, and paste it into the window that appears when you click on the </> icon. And from now on, do this EVERY time you have errors in the log.

[Quentin]

We can't help you with appending data, when you haven't showed any code or log that appends data.  Please shows the definition of your YearMonth macro.

If you're calling it like:

    %do month=&start_month %to &stop_month;
        %put Month: &month;
        %yearmonth(&YM);
        %if %substr(&month, 5, 2) = 12 %then %let month = %eval(&month + 88);   /*this is the kind of hack that @ballardw warned about. : )  */    
    %end;

I don't see a macro variable YM defined anywhere.  I suspect you might intend:

    %do month=&start_month %to &stop_month;
        %put Month: &month;
        %yearmonth(&month)  /*pass the value of macro variable MONTH*/
        %if %substr(&month, 5, 2) = 12 %then %let month = %eval(&month + 88);       
    %end;

[ballardw]

My suspicion, since you refuse to show what your "yearmonth" macro actually does is that inside that macro you have another macro variable named MONTH and you are creating a macro scope scope issue by changing that value inside the YearMonth macro.

You can check such by setting OPTIONS MPRINT; to get a lot of information about the details of your macro.

BTW:

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

[Tom · Accepted solution]

Let's assume that your macro is functioning correctly (and that it actually makes sense to re-use it for this purpose).

Let's also assume that it creates an output dataset named WANT.

What you then need is a way to generate calls to the macro for each of the month descriptors and also some way to combine the datasets it generates.

Let's tackle the second problem first.  If you wanted to call it three times and build one large dataset you could use these steps:

proc delete data=ALL ; run;
%yearmonth(YM=202201);
proc append data=WANT base=ALL force; run;
%yearmonth(YM=202202);
proc append data=WANT base=ALL force; run;
%yearmonth(YM=202203);
proc append data=WANT base=ALL force; run;

And now you have a dataset named ALL with the combined data from the three macro calls.

So how to generate that code?  You could write another macro.

Or just use a data step to generate the code.  Either by writing a file that you can %INCLUDE. Or by using CALL EXECUTE().

To do the looping over months use an offset and the INTNX() function to generate the next date. Use a format to generate the character string you need to pass to the macro call.

So in a data step you might do:

filename code temp;
data _null_;
  file code;
  start = '01JAN2022'd;
  stop = today();
  do offset=0 to intck('month',start,stop);
     date=intnx('month',start,offset);
     put '%yearmonth(ym=' date yymmn6. ');'
         / 'proc append data=WANT base=ALL force; run;'
    ;
  end;
run;
%include code;

[SASnoobie101]

Thank you very much. You totally understand where I am coming from unlike other posters here. I think I understand your code logic, although still a bit complex for me I will try to find out what all the statements mean. But I think your code works. Thank you very much!

[Reeza]

I wouldn't bother with macros personally unless you need to run this mulitple times and have it automatically update for each year. 

data want;
set DATA2022: Data2021: Data2020: ...;
run;

Use the colon operator to short cut the names, assuming you've used a naming convention. 

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

If you do want to use a macro, as others have stated you should start with basic working code and then translate to macros. Finding semi working code on Stack Overflow when you don't understand macros will just make work for you to first figure out that code, then figure out how to modify it for your needs. 

[SASnoobie101]

thanks but i have to run this for like 36 months, so i can't just append it like that. 

I have to run my macro 36 times for each year month parameter. That is why I am trying to find it to run on a loop and append the results. 

[Tom]

Just use a month offset number to drive the loop and the INTNX() function generate the next month.

So if you know the first and last month then do something like this:

filename code temp;
data _null_;
  file code;
  start = '01JAN2019'd;
  stop = '31DEC2021'd;
  do offset=0 to intck('month',start,stop);
     date=intnx('month',start,offset);
     put '%yearmonth(ym=' date yymmn6. ');'
       / 'proc append data=WANT base=ALL force; run;'
    ;
  end;
run;
%include code;