DATA Step, Macro, Functions and more

adding a rolling month

Reply
Super Contributor
Posts: 647

adding a rolling month

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: adding a rolling month

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.
Super Contributor
Super Contributor
Posts: 365

Re: adding a rolling month

Hello SASPhile,

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

Sincerely,
SPR
Super Contributor
Posts: 647

Re: adding a rolling month

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
Super Contributor
Super Contributor
Posts: 3,174

Re: adding a rolling month

Did you look at PROC TRANSPOSE?

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

proc transpose site:sas.com
Super Contributor
Super Contributor
Posts: 365

Re: adding a rolling month

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
Valued Guide
Posts: 632

Re: adding a rolling month

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
Ask a Question
Discussion stats
  • 6 replies
  • 271 views
  • 0 likes
  • 4 in conversation