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.
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).
I think this is a major part of the implementation that Sbb suggests:
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;
proc transpose data=i out=r(drop=_name_) prefix=sales;
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.
/* Example target code to be generated by the macro SALESMO*/
/* set jan;*/
/* merge totjan(rename=(sales1=sales2))*/
/* by outlet;*/
/* merge totfeb(rename=(sales2=sales3 sales1=sales2))*/
/* by outlet;*/
/*proc print data=totmar;*/
%local monum molast i m;
%let monum = %sysfunc(month(%sysfunc(inputn(&mo.2010,monyy7.))));
%*put monum &monum;
%do I = 1 %to &monum;
%if i=1 %then %do;
%* The month is january;
%* Determine the name of the previous month;
%let molast = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(mdy(&i,1,2010)),-1)),monname3.));
%do m=&i %to 2 %by -1;
%* build the rename pairs in descending order;
%* Read the current months data;
proc print data=totapr;
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