BookmarkSubscribeRSS Feed
aycee
Calcite | Level 5

Hi 

 

Is there an easy way to run a macro that allows you to quickly append the data you need from monthly files? eg. if you have spend data that is contained in monthly datasets (eg. Jan & Feb datasets spend_1701, spend_1702) and you wanted to trend spend over the last 12 months, instead of setting 12 datasets manually and keeping the spend value and renaming the spend variable whats the quickest way to do this?

 

Thanks 

3 REPLIES 3
Reeza
Super User

Macros always run by calling them the same way so I suspect you're asking how to create a macro. 

As to what you're trying to do I'm not sure, your explanation is unclear. 

 

I suspect you dont need macros at all. Append all datasets into one, using INDSNAME option to identify source data, and a PROC TRANSPOSE If you want it in a wide format. 

Tom
Super User Tom
Super User

What code to generate I leave to you.  The "tricky" part is getting a value to increment over 12 strings that are in the format YYYYMM since it is not just a normal integer sequence.  That is 201612 is followed by 201701 instead of 201613.

To generate 12 month strings you need to know a base date.  You can pick either the last or the first in the series. I suspect it is more likely that you know the last month rather than the first month.

%let lastmonth="01MAY2017"d;

So to generate the previous twelve months in YYYYMM format you could use a loop like this.

%do offset=-11 %to 0;
   %let month=%sysfunc(intnx(month,&lastmonth,&offset),yymmn6);
    ....
%end;

So if you just want to generate the string

  spend_1603 spend_1604 .... spend_1702

Then you might want a macro like this

%macro dslist(prefix,lastmonth);
%local offset month ;
%do offset=-11 %to 0;
   %let month=%sysfunc(intnx(month,&lastmonth,&offset),yymmn6);
    &prefix.&month.
%end;
%mend dslist;

Then you could use it in a program like this:

data want ;
  set %dslist(spend_,"01FEB2017"D) ;
run;
ballardw
Super User

You may not need any macro or fancy code if you only need data within a given year and your data sets actually have the names you mention. There are list name shortcuts involving the colon character.

 

Data want;

   set spend_17: ;

run;

 

The colon says to include all of the data sets that start with spend_17 .

 

However your comment:


@aycee wrote:

Hi 

 

Is there an easy way to run a macro that allows you to quickly append the data you need from monthly files? eg. if you have spend data that is contained in monthly datasets (eg. Jan & Feb datasets spend_1701, spend_1702) and you wanted to trend spend over the last 12 months, instead of setting 12 datasets manually and keeping the spend value and renaming the spend variable whats the quickest way to do this?

 

Thanks 


Abount renaming makes me think you want to do something that will add work. It is much better to have a variable with the date of the record and a single variable to hold the value than to create multiple variables with different names for each month. Let REPORT procedures do the human readable parts of column headings based on the date instead of forcing multiple variables into a difficult to manipulate process.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 691 views
  • 0 likes
  • 4 in conversation