topic Re: New to SAS: Code for Monthly Datasets in SAS Programming

New to SAS: Code for Monthly Datasets

aycee — Sun, 04 Jun 2017 14:18:54 GMT

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

Re: New to SAS: Code for Monthly Datasets

Reeza — Sun, 04 Jun 2017 16:28:16 GMT

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.

Re: New to SAS: Code for Monthly Datasets

Tom — Sun, 04 Jun 2017 16:41:15 GMT

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;

Re: New to SAS: Code for Monthly Datasets

ballardw — Mon, 05 Jun 2017 14:58:14 GMT

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.