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

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

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;

 

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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.

ballardw
Super User

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

Ody
Quartz | Level 8 Ody
Quartz | Level 8
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.
Reeza
Super User

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)
Quentin
Super User

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 Next up: Don Henderson presenting on using hash functions (not hash tables!) to segment data on June 12. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Ody
Quartz | Level 8 Ody
Quartz | Level 8
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.
Reeza
Super User

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

Tom
Super User Tom
Super User

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;

 

Ody
Quartz | Level 8 Ody
Quartz | Level 8
Thanks for the example!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1404 views
  • 0 likes
  • 6 in conversation