Contributor
Posts: 67

# Rolling 12 months data

Hi,

I have a 1+ year data, every month we need to run a program to procedure a report. In this report, it inculdes:

(1) mean, low range and high range of previous full year (2012) is base on previous full year (2012) data.

(2) Current month do not have full data, so mean, low range and high range will leave blank.

(3) The mean, low, high range for previous month of current year will based on previous 12 months data.

For example:

If I run this report on March 2013, then the mean, low and high range will base on 03/2012 to 02/2013 data.

I created a program to produce this report; however, there are too many steps in the program. I am wondering if there is a way to simplify this program.

The following data I used March 2013 as current month.

data test;
input year  month  a \$ count;
cards;
2012   1   aa  160
2012   1   bb  260
2012   2   aa  760
2012   2   bb  860
2012   3   aa  260
2012   3   bb  960
2012   4   aa  760
2012   4   bb  660
2012   5   aa  560
2012   5   bb  660
2012   6   aa  460
2012   6   bb  460
2012   7   aa  360
2012   7   bb  260
2012   8   aa  260
2012   8   bb  960
2012   9   aa  160
2012   9   bb  860
2012   10  aa  575
2012   10  bb  775
2012   11  aa  690
2012   11  bb  390
2012   12  aa  760
2012   12  bb  560
2013   1   aa  490
2013   1   bb  290
2013   2   aa  809
2013   2   bb  709
2013   3   aa  609
2013   3   bb  309
;
run;

/***** Get the average and std for last full year and current month *****/
proc means data = test maxdec=2 noprint;
where year = 2012  or year = 2013 and month = 3;
class year a;
var count;
output out=avgqt0 mean=avg_count std=stdev;
run;

/***** Create a rolling 12 month date *****/
data test2;
set test;
do i = 0 to 3;
if year = 2013 and month = 3 then delete;
if year = 2012 and month = i-1 then delete;
end;
run;

/***** Compute mean and std for rolling 12 month data *****/
proc means data = test2 maxdec=2 noprint;
class a;
var count;
output out=avgqt1 mean=avg_count std=stdev;
run;

/***** Get the range for previous full year *****/
/***** Set the mean for current month as Null since it is not a full month data *****/
data avgqt00;
set avgqt0(drop=_freq_);
if _type_ in (3);
if year = 2012 then
do;
two_stdev=round(2*stdev);
avg_count = round(avg_count);
low=avg_count-two_stdev;
high=avg_count+two_stdev;
flag = 1;
end;
else
do;
avg_count = .;
flag = 3;
end;
drop _type_ stdev two_stdev;
run;

/***** Get the range for rolling 12 month data*****/
data avgqt11;
set avgqt1(drop=_freq_);
if _type_ in (1);
two_stdev=round(2*stdev);
avg_count = round(avg_count);
low=avg_count-two_stdev;
high=avg_count+two_stdev;
flag = 2;
drop _type_ stdev two_stdev;
run;

PROC SORT DATA = test out=sort2012;
where year = 2012  or year = 2013 and month = 3;
by year a;
run;

/***** Merge previous full year and current month data back to original same period of time data *****/
data all2012;
length flag2 \$5;
merge sort2012 (in = B)
avgqt00  (in = C)
;
by year a;
if B;
if year = 2012 then
do;
IF LOW <= a <= HIGH THEN FLAG2='OK';
ELSE FLAG2 ='CHECK';
end;

ELSE flag2 = ' ';
RUN;

PROC SORT DATA = test out=sort2013;
where year = 2013 and month ^= 3;
by year a;
run;

/***** Merge rolling 12 month data back to original current year data except current month *****/
data all2013;
length flag2 \$5;
merge sort2013 (in = B)
avgqt11  (in = C)
;
by a;
if B;
IF LOW <= a <= HIGH THEN FLAG2='OK';
ELSE FLAG2 ='CHECK';
RUN;

/***** Combine these two data set to get the final report *****/
data all;
set all2012
all2013;
run;

proc sort data = all;
by year flag;
run;

proc print data = all;

var year month a count avg_count low high flag2;

run;

Posts: 3,852

## Re: Rolling 12 months data

This is most of it I think.  You need to make you date info a SAS date.

The sort is different and I didn't calculate LOW HIGH or FLAG but you should be able to do that ok.

data test;
input year  month  a \$ count;
yymm = mdy(month,1,year);
format yymm yymm.;

drop year month;
cards;
2012   1   aa  160
2012   1   bb  260
2012   2   aa  760
2012   2   bb  860
2012   3   aa  260
2012   3   bb  960
2012   4   aa  760
2012   4   bb  660
2012   5   aa  560
2012   5   bb  660
2012   6   aa  460
2012   6   bb  460
2012   7   aa  360
2012   7   bb  260
2012   8   aa  260
2012   8   bb  960
2012   9   aa  160
2012   9   bb  860
2012   10  aa  575
2012   10  bb  775
2012   11  aa  690
2012   11  bb  390
2012   12  aa  760
2012   12  bb  560
2013   1   aa  490
2013   1   bb  290
2013   2   aa  809
2013   2   bb  709
2013   3   aa  609
2013   3   bb  309
;;;;
run;
proc sql noprint;

select max(yymm) into :ref from test;
quit;

run;
%put NOTE: REF=&ref %sysfunc(putn(&ref,date,9));

proc summary data=test nway;

where year(yymm) eq (year(&ref)-1);
class a;
var count;
output out=lastyear(drop=_ mean=mean std=std;

run;
proc summary data=test nway;

where intnx('MONTH',&ref,-12) le yymm le intnx('MONTH',&ref,-1);
class a;
var count;
output out=last12months(drop=_ mean=mean std=std;

run;
data stats;
set lastyear(in=in1) last12months(in=in2);
by a;
if in1 then yymm = intnx('YEAR',&ref,-1);
else if in2 then yymm = intnx('YEAR',&ref,0);
format _all_;
run;
proc sort data=test;
by a yymm;
run;
data newtest;
merge test stats;
by a yymm groupformat;

format yymm year.;

if yymm eq &ref then call missing(of mean std);

run;
proc print;

format yymm yymm.;

run;
Super Contributor
Posts: 578

## Re: Rolling 12 months data

data test;

input year  month  a \$ count;

format dt date9.;

dt = mdy(month,1,year);

cards;

2012   1   aa  160

2012   1   bb  260

2012   2   aa  760

2012   2   bb  860

2012   3   aa  260

2012   3   bb  960

2012   4   aa  760

2012   4   bb  660

2012   5   aa  560

2012   5   bb  660

2012   6   aa  460

2012   6   bb  460

2012   7   aa  360

2012   7   bb  260

2012   8   aa  260

2012   8   bb  960

2012   9   aa  160

2012   9   bb  860

2012   10  aa  575

2012   10  bb  775

2012   11  aa  690

2012   11  bb  390

2012   12  aa  760

2012   12  bb  560

2013   1   aa  490

2013   1   bb  290

2013   2   aa  809

2013   2   bb  709

2013   3   aa  609

2013   3   bb  309

;

run;

proc sql;

select

case

when t1.dt = t2.curr_dt then 'Current'

when t1.dt > intnx('month',t2.curr_dt,-13) then 'Prior Rolling 12 Months'

end as Period

,count(*) as Obs

,avg(count) as Mean_Count

,min(count) as Min_Count

,max(count) as Max_Count

from

test t1,

(select max(dt) format=date9. as curr_dt from test) t2

where calculated period is not null

group by calculated period

;

quit;

produces this dataset:

 Current 2 459 309 609 Prior Rolling 12 Months 24 570.333 160 960
Contributor
Posts: 67

## Re: Rolling 12 months data

Thank you so much. Works well too.

Discussion stats
• 3 replies
• 3406 views
• 6 likes
• 3 in conversation