topic Re: adding a rolling month in SAS Programming

adding a rolling month

SASPhile — Fri, 17 Dec 2010 18:22:49 GMT

how to get rolling month?
24 months of data have to be shown in the dataset.every month we load data,summarize it and create a column "sales". for instance in january it is called sales.
in febuary sales is calculated,but we need to bring the sales from january dataset call it sales2 and febuary sales is called sales1.
so current month is sales1 and previous month is called sales2 etc etc.
The 3rd month, there'll be Sales1,Sales2 and Sales3, and so on...
the data are merged by outlet number.

Re: adding a rolling month

sbb — Fri, 17 Dec 2010 20:59:32 GMT

If this represents a "reporting requirement", an approach I use is to keep your data "vertical" as observations for each "outlet" and month-period, and use PROC TRANSPOSE at reporting time to generate your "columns" using ID and IDLABEL with PROC TRANSPOSE to create a suitable column heading (or if you prefer, use VAR and generate SAS variables as you have explained).

Scott Barry
SBBWorks, Inc.

Re: adding a rolling month

SPR — Fri, 17 Dec 2010 21:31:55 GMT

Hello SASPhile,

If you need a code please give and an example of input and desired output.

Sincerely,
SPR

Re: adding a rolling month

ArtC — Fri, 17 Dec 2010 21:50:43 GMT

If I had a choice, like Scott suggested, I would rather see you change the data structure, but not given the option the macro was fun to write. See if this is close to the direction that you are going. The outer %DO loop in the macro would not be needed in production and only appears here because I am stepping through all the months at one time.
[pre]data jan;
outlet=345;
sales1=1;
run;
data feb;
outlet=345;
sales1=2;
run;
data mar;
outlet=345;
sales1=3;
run;
data apr;
outlet=345;
sales1=4.4;
run;

/* Example target code to be generated by the macro SALESMO*/
/*data totjan;*/
/* set jan;*/
/* run;*/
/*data totfeb;*/
/* merge totjan(rename=(sales1=sales2))*/
/* feb;*/
/* by outlet;*/
/* run;*/
/*data totmar;*/
/* merge totfeb(rename=(sales2=sales3 sales1=sales2))*/
/* mar;*/
/* by outlet;*/
/* run;*/
/*proc print data=totmar;*/
run;

%macro salesmo(mo=jan);
%local monum molast i m;
%let monum = %sysfunc(month(%sysfunc(inputn(&mo.2010,monyy7.))));
%*put monum &monum;
%do I = 1 %to &monum;
data tot&mo;

%if i=1 %then %do;
%* The month is january;
set &mo;
%end;
%else %do;
%* Determine the name of the previous month;
%let molast = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(mdy(&i,1,2010)),-1)),monname3.));
merge tot&molast(rename=(
%do m=&i %to 2 %by -1;
%* build the rename pairs in descending order;
sales%eval(&m-1)=sales&m
%end;
))
%* Read the current months data;
&mo;
by outlet;
%end;
run;
%end;
%mend salesmo;
%salesmo(mo=apr)
proc print data=totapr;
run;
[/pre]
For renaming the variables, which is really the heart of your question, this solution takes advantage of the RENAME= options execution properties.
[pre] merge totfeb(rename=(sales2=sales3 sales1=sales2))[/pre]
here sales2 is renamed to sales3 before sales1 is renamed to sales2

Re: adding a rolling month

SASPhile — Mon, 20 Dec 2010 18:33:59 GMT

data jan;
outlet=345;
sales1=1;
run;
data feb;
outlet=345;
sales1=2;
run;
data mar;
outlet=345;
sales1=3;
run;
data apr;
outlet=345;
sales1=4.4;
run;

the final output:

outlet sales1 sales2 sales3 sales4
345 4.4 3 2 1

Re: adding a rolling month

sbb — Mon, 20 Dec 2010 18:36:36 GMT

Did you look at PROC TRANSPOSE?

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

proc transpose site:sas.com

Re: adding a rolling month

SPR — Mon, 20 Dec 2010 21:10:58 GMT

Hello SASPhile,

I think this is a major part of the implementation that Sbb suggests:
[pre]
data i;
set jan(in=j) feb(in=f) mar(in=m) apr(in=a);
if j then mt=1;
if f then mt=2;
if m then mt=3;
if a then mt=4;
run;
proc transpose data=i out=r(drop=_name_) prefix=sales;
id mt;
var sales1;
by outlet;
run;
[/pre]
Sincerely,
SPR