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;
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
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
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
Thank you, PGStats for your kind help.
Best,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: