Help using Base SAS procedures

Moving Average with Missing Observations

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Moving Average with Missing Observations

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;

Attachment

Accepted Solutions
Solution
‎11-10-2012 10:51 PM
Respected Advisor
Posts: 4,651

Re: Moving Average with Missing Observations

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

PG

View solution in original post


All Replies
Solution
‎11-10-2012 10:51 PM
Respected Advisor
Posts: 4,651

Re: Moving Average with Missing Observations

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

PG
Respected Advisor
Posts: 4,651

Re: Moving Average with Missing Observations

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

PG
Contributor
Posts: 55

Re: Moving Average with Missing Observations

Thank you, PGStats for your kind help.

Best,

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 746 views
  • 3 likes
  • 2 in conversation