turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Moving Average with Missing Observations

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-10-2012 09:53 PM

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;

Accepted Solutions

Solution

11-10-2012
10:51 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to finans_sas

11-10-2012 10:51 PM

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

All Replies

Solution

11-10-2012
10:51 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to finans_sas

11-10-2012 10:51 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

11-11-2012 09:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

11-13-2012 06:39 AM

Thank you, PGStats for your kind help.

Best,