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.
Thanks in advance for your help.
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;
