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
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: