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

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
sbxkoenk
SAS Super FREQ

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

View solution in original post

5 REPLIES 5
sbxkoenk
SAS Super FREQ

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

kashlik123
Obsidian | Level 7

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.

sbxkoenk
SAS Super FREQ

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

kashlik123
Obsidian | Level 7

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.

sbxkoenk
SAS Super FREQ

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2116 views
  • 0 likes
  • 2 in conversation