Moving Average with Missing Observations

[finans_sas]

Dear All,

I am trying to calculate a 12 month moving sum for variable "frac" in the test data set I attached (there are 3000+ ids in the original data). I modified the program (below) from SAS website (http://support.sas.com/kb/25/027.html) and it seems to be working alright. However, towards the end of the sample, it is generating extremely small numbers for the moving average while it is supposed to generate zeros due to missing observations. I will really appreciate your help in understanding and fixing this output.

Thanks,

proc sort data=test; by id myear;

data test;

  set test;

  by id;

  retain mnum_sum 0;

  if first.id then do;

    mcount=0;

    mnum_sum=0;

  end;

  mcount+1;

  %let m = 12;

  mlast&m=lag&m(frac);

  if mcount gt &m then mnum_sum=sum(mnum_sum,frac,-mlast&m);

  else mnum_sum=sum(mnum_sum,frac);

  if mcount ge &m then movavg=mnum_sum/&m;

  else movavg=.; *first 11 months are to set to missing;

run;

[PGStats · Accepted solution]

The problem is not caused by missing values but by accumulated floating point numbers imprecision. Given the type of numbers (fractions between -1 and 1) that you average, you coulld solve the problem with the FUZZ function :

proc sort data=test; by id myear; run;

%let m = 12;

data test;
  set test;
  by id;
  retain mnum_sum 0;
  if first.id then do;
    mcount=0;
    mnum_sum=0;
  end;
  mcount+1;
  mnum_sum=sum(mnum_sum,frac);
  mlast&m=lag&m(frac);
  if mcount gt &m and not missing(mlast&m) then mnum_sum=fuzz(sum(mnum_sum,-mlast&m));
  if mcount ge &m then movavg=mnum_sum/&m;
  else movavg=.; *first 11 months are to set to missing;
run;

PG

[PGStats]

A cleaner solution involves an array :

proc sort data=test; by id myear; run;

%let m = 12;

data test;
  array _X{&m} _temporary_;
  set test;
  by id;
  if first.id then count = 0;
  count+1;
  _X{1+mod(count,&m)} = frac;
  if count ge &m then movavg=mean(of _X{*});
  else movavg=.; *first 11 months are to set to missing;
run;

If you would rather consider missing values as zeros in the calculation of the average, replace movavg=mean(of _X{*}) by movavg=sum(of _X{*})  /&m.

PG

[finans_sas]

Thank you, PGStats for your kind help.

Best,