specifc average

[redrover99]

Hi all

I need help, I’m looking to calculate the 5 year average  for per_change. This five year average must be specific for each company identified by the permn number, and have consecutive years

EG:

Permno	year	Per_change	5yravg
10001	2001	0.2
10001	2002	0.6
10001	2003	0.4
10001	2004	0.22
10001	2005	0.3	0.344
10001	2006	0.5	0.404

I have attached my data any help in this command would be greatly appreciated.

[Haikuo]

Hi,

If you have SAS/ETS, check out the documentation on Proc Expand, which has been built in with such a functionality. Here is to show you one approach with data step:

filename test 'c:\temp\test.csv';

data have;

  infile test dsd truncover firstobs=2;

  input gvkey$    lpermno$    fyear    PER_CHANGE;

  run;

  data want;

    array t(0:4) _temporary_;

    do _n_=1 by 1 until (last.lpermno);

     set have;

      by lpermno notsorted;

      t(mod(_n_,5))=per_change;

      if _n_>=5  then mov5avg=mean(of t(*));

      output;

     end;

     call missing(of t(*));

  run;

Haikuo