Rolling Moving Average

I needed to create a 5 year rolling moving average of transposed weekly data. Jerry from SAS tech support came up with what I thought was a great response. He suggested using a multilabel format along with proc summary and the CLASS var / MLF option.

I used a macro loop to create the dynamic proc format statement. The use of the SAS colon (:) wildcard makes it easy to select like named variables such as week1 - week52.

data sample ;
  do year = 2000 to 2015 ;
    do weekno = 1 to 52 ;
      value = int( ranuni( 1 ) * 1000 ) ;
      output ;
    end ;
  end ;
run ;

proc transpose
  data   = sample
  out    = transposed( drop = _name_ )
  prefix = week ;

  var value ;
  id  weekno ;
  by  year ;
run ;

%macro yearformat(
    startyear = 2000
  , endyear   = 2015
  , periods   = 5
)
;
  %local year ;
  proc format ;
    value yearf ( multilabel )
    %do year = &startyear. %to %eval( &endyear. - &periods. + 1 ) ;
      &year - %eval(&year + &periods. -1) = "%eval(&year. + &periods. -1)"
    %end ;
  ;
  run ;
%mend ;
%yearformat

proc summary
  data = transposed
  nway ;

  class year / mlf ;
  var week: ;

  format
    year yearf.
    week: 8.2 ;

  output
    out  = movingaverage(drop = _type_ _freq_ )
    mean = ;
run ;