🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## Macro to increment months

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Macro to increment months

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

10 REPLIES 10
Diamond | Level 26

## Re: Macro to increment months

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.

--
Paige Miller
Quartz | Level 8

## Re: Macro to increment months

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.

Super User

## Re: Macro to increment months

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

Quartz | Level 8

## Re: Macro to increment months

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

## Re: Macro to increment months

Check the macro appendix.

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n01vuhy8h909xgn16p0x6rddpoj9.htm&docse...

`````` %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)``````
Super User

## Re: Macro to increment months

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.

BASUG is hosting free webinars ! Check out our recordings of past webinars: https://www.basug.org/videos. Be sure to subscribe to our email list for notification of future BASUG events.
Quartz | Level 8

## Re: Macro to increment months

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

## Re: Macro to increment months

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

Super User

## Re: Macro to increment months

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

Quartz | Level 8

## Re: Macro to increment months

Thanks for the example!
Discussion stats
• 10 replies
• 1548 views
• 0 likes
• 6 in conversation