topic Re: Macro to increment months in SAS Programming

Macro to increment months

Ody — Mon, 04 May 2020 17:49:10 GMT

Hi all,

I've searched though some of the previous forum posts but couldn't find something relevant to my particular need.

I have a query that looks something like this:

proc sql;
create table month as
select * from some_table
where date between start_date and end_date;
quit;

My goal is to create a macro that will increment the start/end date for each month in a given range. For example, I want to create a data set for each month in the range of May 1 2017 to September 1 2019, where start_date and end_date are the beginning and end of each month in that range. I've seen some examples using intx() but I'm having trouble applying that to this scenario.

Appreciate any pointers. Thanks in advance.

Re: Macro to increment months

PaigeMiller — Mon, 04 May 2020 17:53:14 GMT

I want to create a data set for each month ...

Almost always a poor idea that results in more difficult and time-consuming coding than keeping things in one large data set. If you need to do analysis or do reports for each month, you can use the BY statement to accomplish. This is much less coding, and no macro is needed.

Re: Macro to increment months

Ody — Mon, 04 May 2020 18:12:04 GMT

Unfortunately I'm unable to keep things in one data set due to the nature of the data and analysis being performed. I also don't really have much control over the context of the code. I was asked to automate an existing process involving a more complex version of the query in my example.

Re: Macro to increment months

ballardw — Mon, 04 May 2020 18:29:37 GMT

@Ody wrote:

Unfortunately I'm unable to keep things in one data set due to the nature of the data and analysis being performed. I also don't really have much control over the context of the code. I was asked to automate an existing process involving a more complex version of the query in my example.

That brings up a question of are your date values in the data actually SAS date values or not. If not then you'll need to provide very explicit examples of what the start and end dates have to look like.

Re: Macro to increment months

Ody — Mon, 04 May 2020 18:38:59 GMT

Thanks for thinking through that though that possible issue but I'm comfortable transforming the data into the appropriate format, I'm just having trouble writing a macro to loop through the months of my date range. I'm really just looking for an example I can use as a frame work to build on.

Re: Macro to increment months

Quentin — Mon, 04 May 2020 18:44:23 GMT

How big is your source data? If it's not too big, could be a nice use case for dynamic data-splitting via hash tables. If not, there's probably a straight forward call execute approach.

Re: Macro to increment months

Reeza — Mon, 04 May 2020 18:44:47 GMT

Check the macro appendix.

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n01vuhy8h909xgn16p0x6rddpoj9.htm&docsetVersion=9.4&locale=en

 %macro date_loop(start,end);
   %let start=%sysfunc(inputn(&start,anydtdte9.));
   %let end=%sysfunc(inputn(&end,anydtdte9.));
   %let dif=%sysfunc(intck(month,&start,&end));
     %do i=0 %to &dif;
      %let date=%sysfunc(intnx(month,&start,&i,b),date9.);
      %put &date;
     %end;
   %mend date_loop;

   %date_loop(01jul2015,01feb2016)

Re: Macro to increment months

Ody — Mon, 04 May 2020 19:05:56 GMT

The data source is financial related so it's relatively large. The query pulls data on a per period basis where a period is defined as the start of the month to the end of the month. The data, being financial in nature, needs to be pulled in this manner for analysis.

The current process is absurd. Someone is running this query over and over again, changing the start and end date. Then they copying the data over to excel. When I heard of this I new there was an easier way to loop through the query and export the data. I've done similar things in the past with export data sets but the date range is giving me a headache. Maybe I'm over thinking it...

I thought I'd get some quick ideas here but instead i'm getting a lot more questions than answers.

Re: Macro to increment months

Tom — Mon, 04 May 2020 19:31:08 GMT

First step is to get it to work for one specific case. Your example is NOT that. More like this:

create table MAY2019 as
  select * 
  from some_table
  where date between '01MAY2019'd  and '31MAY2019'd
;

Second step is to replace the parts that change with macro variables. To start set the values manually.

%let dsname=MAY2019;
%let start='01MAY2019'd;
%let end='31MAY2019'd;
create table &dsname. as
  select * 
  from some_table
  where date between &start. and &end.
;

Now you can start thinking about how to generate a series of these with different values of the macro variables.

For example if you wanted to do the same thing for each month from MAY2019 to JAN2020 you might to something like this:

%let first_date='01MAY2019'd;
%let last_date='01JAN2020'd;
%do offset=0 %to %sysfunc(intck(month,&first_date,&last_date));
  %let start=%sysfunc(intnx(month,&first_date,&offset,b));
  %let end=%sysfunc(intnx(month,&first_date,&offset,e));
  %let dsname=%sysfunc(intnx(month,&first_date,&offset,b),monyy7.);
create table &dsname. as
  select * 
  from some_table
  where date between &start. and &end.
;
%end;

Re: Macro to increment months

Reeza — Mon, 04 May 2020 19:33:35 GMT

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

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

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

Re: Macro to increment months

Ody — Mon, 04 May 2020 19:39:59 GMT

Thanks for the example!