topic Re: Proc expand missing values in moving average in SAS Programming

Proc expand missing values in moving average

kashlik123 — Wed, 27 Oct 2021 07:45:29 GMT

I am trying to calculate a three month moving average of a variable that has missing value. If the selected time window contains missing values then they should be disregarded in the calculation. I tried doing this with the PROC EXPAND procedure, but it does not capture the missing values as expected. The documentation says that I should use the nomiss option but it has no effect on the results. The following code demonstrates the problem:

data have(drop=i);
call streaminit(1);
do i = 1 to 15;
  period = i;
  x = round(rand('uniform')*10,1.);
  if 5 < period < 12 then x = .;
  output;
end;
run;

proc expand data=have out=wrong_want(drop=time);
convert x=x_avg / transformout=(nomiss movave 3 );
run;

The below PROC SQL block show what the results should be, however it is not feasible to you use it due to computational constrains from my side.

Proc sql;
create table want as
select a.period, a.x
      ,mean(b.x) as x_avg
from have as a
left join have as b
  on a.period -3 < b.period <= a.period
group by 1,2;
Quit;

Re: Proc expand missing values in moving average

sbxkoenk — Wed, 27 Oct 2021 11:09:40 GMT

Hello,

I think you need the

METHOD=NONE option

on the PROC EXPAND statement to avoid any missing value interpolation to be done.

The default is METHOD=SPLINE.

Good luck,

Koen

Re: Proc expand missing values in moving average

kashlik123 — Wed, 27 Oct 2021 11:13:31 GMT

If you use the method=none option then proc expand will set any moving average that has missing values to missing as well. So for example if you have an MA window of 12 months and one of them has a missing value, then proc expand will set the MA value to null instead of calculating the average on the remaining 11 non-missing values.

Re: Proc expand missing values in moving average

sbxkoenk — Wed, 27 Oct 2021 11:26:35 GMT

Hello,

There may be a problem with your original example then (on page 1) as method=none results in PROC EXPAND giving exactly the same output as PROC SQL.

Koen

Re: Proc expand missing values in moving average

kashlik123 — Wed, 27 Oct 2021 11:31:03 GMT

Do you mean the following:

proc expand data=have out=wrong_want(drop=time);
convert x=x_avg / method=none transformout=(nomiss movave 3 );
run;

Because when I run this code it gives me a missing value for periods 6, 7, 12 and 13, whereas the proc sql doesn't.

Re: Proc expand missing values in moving average

sbxkoenk — Wed, 27 Oct 2021 15:20:49 GMT

This code outputs the same data set as PROC SQL.

Get rid of the nomiss option before movave.

proc expand data=have out=wrong_want(drop=time) method=none;
convert x=x_avg / transformout = ( movave 3 );
run;

Koen