Dynamic Programming: How to Use a %Do Loop in a Macro to Create Datasets for Each Day of a Month

[davidvalentine]

Hello Beautiful People!

I hope all is well.  I'm trying to automate a very manual process as follows:

1. There is a %LET macro variable that references a date ---> %LET date = '2020-01-10';
2. There is then a data step that follows that generates basic data for that date.  
3. Once the data step runs, change the date variable for the next day and rerun the process.  Repeat this for every day of the month.

An example of the above process is below, where I would change the macro variable after this process runs for the next day and repeat until I capture all of the days of the month.

%let date =20200110;
Data example; set balance_&date.;run;
/*Rerun the above step for every day of the month because busy work.*/

How would I translate this into a macro that encompasses a %DO loop to capture every day of the month?  For example: If it's February 2020, it would capture all 29 days.  If it's December, it would capture all 31 days.  Etc.  Any guidance on this is greatly appreciated!

[Reeza]

That code would not work for starters - the quotes and dashes would cause issues so showing actual code would help.

Second, you can shortcut reference data sets if they actually have a naming structure:

data want;
set balance_2020_02: ;
*the colon will use all data sets that start with balance_2020_02;
if prod_bal = . then delete;
run;

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage - this has some examples that may be useful to you.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[davidvalentine]

Hi Reeza,

I fixed the SAS example code to present a near-identical copy.  In the meantime, I'm not sure I follow your suggestion...

[PaigeMiller · Accepted solution]

A couple of comments. You almost never want quotes around the macro variable value.

Next, you want to use SAS date functions and formats to accomplish this, such as INTNX.

Now, I have to admit, I'm not entirely clear on why you have date = '2020-01-10' and then you talk about February. I'm also not clear, as your have a SET statement that include &date, do you really have separate SAS data sets for every single day and you have to create new separate data set for every single day? If so, this isn't a particularly good way to organize the data, in fact I would call it bluntly a terrible blunder to organize this way, but maybe you're stuck with it — I hope not. You'd be better off creating one large data set for the month or for the year, rather than data sets for each and every day.

But, here goes

/* Somehow the user provides a date, and we loop over all dates in the month */
%let date=2020-01-10;
%let date1=%sysfunc(inputn(&date,yymmdd10.));

%macro dothis;
    %do day=%sysfunc(intnx(month,&date1,0,b)) %to
         %sysfunc(intnx(month,&date1,0,e)) %by 1;
		%let day1=%sysfunc(putn(&day,date7.));
		data example_&day1;
		    set balance_&day1;
			if prod_bal=. then delete;
		run;
	%end;
%mend;
%dothis

But, really, don't do this, take my advice above and put everything into one large data set, your programming thereafter will be much easier.

[davidvalentine]

Hi PaigeMiller,

Thanks so much for your guidance on this.  This is one of those situations where I'm stuck with what was given to me from above.  And they want it this way.  But you gave me plenty of wiggle room with your suggestions and I very much appreciate your help.  Thanks again!

-Valentine

[Reeza]

Did you try it by running the code? It'll pull all data at once into a single data set which is the best approach.

data want;
set balance_2020_02: ;
*the colon will use all data sets that start with balance_202002: ;
if prod_bal = . then delete;
run;

[PaigeMiller]

---

@davidvalentine wrote:

Thanks so much for your guidance on this.  This is one of those situations where I'm stuck with what was given to me from above.  And they want it this way.  But you gave me plenty of wiggle room with your suggestions and I very much appreciate your help.  Thanks again!

---

Tell the people you work with that you will be able to write code much more efficiently if everything is in a single data set, thereby saving time and money. @Reeza shows how you can do that.

[Tom]

Do you need to loop or just read all of the datasets in one step?

So lets get a string that looks like a date in YYYYMMDD style.

%let date=20200210 ;

Then to get year and month we just need the first 6 characters. No need to for any knowledge of actual dates.  So you can use the colon wildcard to tell SAS to read all datasets that start with BALANCE_202002.  You might need include a %UNQUOTE() function call to make sure the parse doesn't treat %substr() function call as breaking the dataset name into three tokens instead of one.

data example; 
  set %unquote(balance_%scan(&date,1,6):) ;
run;