Macro for Extracting Monthly data

[Rahul_SAS]

Hi Experts,

Can anyone help me to write a macro that extracts monthly data to multiple datasets. I have a sales data that has a Sales_Date column and using this I need to extract multiple data and load to new month-wise datasets.

Thanks

Rahul

[PaigeMiller]

This has been discussed a zillion times, and the general consensus is to not use separate data sets for monthly data. This is extra programming time to create the data sets, and then extra work to write a program to use the separate data sets.

Instead, leave the data in one large data set. Analyses can by done via a BY statement, and all months will be analyzed with one call to a PROC. Or, if you want to just analyze one month, for example, July 2019, you can do something like this:

proc whatever data=have(where=('01JUL19'd<=sales_date<='31JUL19'd));

[Jagadishkatam]

Here is an example to output monthly data separately ,hope this is what you are expecting, please try executing this data

proc sort data=sashelp.stocks out=stocks;
by date;
run;

%macro month;
%do i = 1 %to 12;
data month&i;
set stocks;
by date;
month=month(date);
if month=&i then output month&i;
run;
%end;
%mend;

%month;

[Rahul_SAS]

Hi Jagadishkatam,

Thanks for helping. 

I have data for multiple years and want to include year value also. Exa: Jan2014, Fab2014....Dec2014....Jan2015, Feb2015....Dec2015, Jan2016, Feb2016,...Dec2016 and so on...

Tried adding year like below but its not working..

%macro month;
%do i = 1 %to 12;
data month&i..sysfunc(year(date));
set stocks;
by date;
month=month(date);
if month=&i then output month&i;
run;
%end;
%mend;

[Tom]

That doesn't look right.  Your DATA statement is naming this dataset:

month&i..sysfunc(year(date))

But the OUTPUT statement is naming a different dataset:

month&i

If you just leave the dataset name off of the output statement then the observation is written to all of the datasets defined in the DATA statement.  Which in this case (and in most) is just one.

The name in the DATA statement also looks wrong.  First you are trying to use the YEAR() function on a character string, the letters D A T E .  Did you have an actual DATE value that you wantted to pass the YEAR() function?

But even if you did it still doesn't look right.  You appear to be trying to generate a name like:

MONTH1.2020

Looks like you are trying to make dataset named 2020 in a libref named MONTH1.  2020 is an invalid dataset name. Do you even have a libref named MONTH1 defined?

[Rahul_SAS]

I modified the code but still getting error. Please help.

%macro month;
proc sql;
select min(distinct(year(date))) into: minyear from sashelp.stocks;
quit;
proc sql;
select max(distinct(year(date))) into: maxyear from sashelp.stocks;
quit;

%do i = 1 %to 12;
%do j = &minyear %to &maxyear;

/*data month&i.&j.;*/
data put(&i,monname3.)&j;
set stocks;
by date;
month=month(date);
where year(date) > 2002;
if month=&i and years=&j then output month&i&j;
run;
%end;
%end;
%mend;
%month;

[Tom · Accepted solution]

So the main problem now is you cannot have parentheses in the name of a dataset. SAS will interpret that as request to make a dataset named PUT.  SAS will interpret the text inside the parentheses as an attempt to specify dataset options.  But the values inside the parentheses do not look like valid dataset options.

It is much easier if you use actual dates instead of just integers.  

So if you want to run a separate data step for each month then your %DO loop would look like this:

%macro month;
%local mindate maxdate i date month ;
proc sql noprint;
select min(date),max(date)
  into :mindate , :maxdate 
  from sashelp.stocks
;
quit;
%do i=0 %to %sysfunc(intck(month,&mindate,&maxdate));
  %let date=%sysfunc(intnx(month,&mindate,&i));
  %let month=%sysfunc(putn(&date,monyy7.));
data &month ;
  set sashelp.stocks;
  if intnx('month',date,0)=&date then output ;
run;
%end;
%mend;
%month;

If you want to run one data step and write out all of the datasets at once you need two %DO loops.

%macro month;
%local mindate maxdate i date month ;
proc sql noprint;
select min(date),max(date)
  into :mindate , :maxdate 
  from sashelp.stocks
;
quit;
data 
%do i=0 %to %sysfunc(intck(month,&mindate,&maxdate));
  %let date=%sysfunc(intnx(month,&mindate,&i));
  %let month=%sysfunc(putn(&date,monyy7.));
&month 
%end;
  ;
  set sashelp.stocks;
%do i=0 %to %sysfunc(intck(month,&mindate,&maxdate));
  %let date=%sysfunc(intnx(month,&mindate,&i));
  %let month=%sysfunc(putn(&date,monyy7.));
  if intnx('month',date,0)=&date then output &month ;
%end;
run;
%mend;
%month;

[Rahul_SAS]

Hi Tom,

Thanks for helping!!

These codes are working absolutely fine. I have few questions and asked below:

%macro month;
%local mindate maxdate i date month ;
proc sql noprint;
select min(date),max(date)
into :mindate , :maxdate
from sashelp.stocks
;
quit;
%do i=0 %to %sysfunc(intck(month,&mindate,&maxdate));
%let date=%sysfunc(intnx(month,&mindate,&i));
%let month=%sysfunc(putn(&date,monyy7.)); *Why did we use here PUTN instead of PUT;
data &month ;
set sashelp.stocks;
if intnx('month',date,0)=&date then output ;
run;
%end;
%mend;
%month;

/*If you want to run one data step and write out all of the datasets at once you need two %DO loops.*/
%macro month;
%local mindate maxdate i date month ;
proc sql noprint;
select min(date),max(date)
into :mindate , :maxdate
from sashelp.stocks
;
quit;
data
%do i=0 %to %sysfunc(intck(month,&mindate,&maxdate));
%let date=%sysfunc(intnx(month,&mindate,&i));
%let month=%sysfunc(putn(&date,monyy7.));
&month
%end;
;
set sashelp.stocks;
%do i=0 %to %sysfunc(intck(month,&mindate,&maxdate));
%let date=%sysfunc(intnx(month,&mindate,&i));
%let month=%sysfunc(putn(&date,monyy7.)); *Why do we need these repeated looping statements and how its working;
if intnx('month',date,0)=&date then output &month ;
%end;
run;
%mend;

[Tom]

%SYSFUNC() does not work with PUT() and INPUT().  You need to use PUTN() or PUTC() or INPUTN() or INPUTC(). I assume it is because unlike in a data step sysfunc cannot determine the type of the format being applied.

You need two loops because you need to generate two lists.  First the list of dataset names in the DATA statement. Second the list of IF/THEN/OUTPUT statements for each dataset.