BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
finans_sas
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

3 REPLIES 3
PGStats
Opal | Level 21

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
PGStats
Opal | Level 21

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
finans_sas
Quartz | Level 8

Thank you, PGStats for your kind help.

Best,

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2222 views
  • 4 likes
  • 2 in conversation