Rolling 12 months data

[Belle]

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;

[data_null__]

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;

[DBailey]

I added a true date column in your test 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.3333	160	960

[Belle]

Thank you so much. Works well too.