Quartz | Level 8

Calculate moving sum over N observations and require at least M of them to be nonmissing?

Let's say N=8 and M= 6. If the number of non-missing values within a rolling window is fewer than 6, the program should set the output value to be missing. Not sure if the following does this:

``````proc expand data = have out = want method = none;
by firm;
id date;
convert x = y/ transformout = (movsum 8 trimleft 5);
run;``````
4 REPLIES 4
Tourmaline | Level 20

Re: Calculate moving sum over N observations and require at least M of them to be nonmissing?

I'm not entirely sure here, but I don't think Proc Expand has such an option.

Do you want an alternative to Proc Expand?

Quartz | Level 8

Re: Calculate moving sum over N observations and require at least M of them to be nonmissing?

Thanks for confirming that proc expand cannot do this. There are definitely (less elegant) solutions.
Tourmaline | Level 20

Re: Calculate moving sum over N observations and require at least M of them to be nonmissing?

@xyxu agree. Proc Expand is so short and smooth 🙂

Super User

Re: Calculate moving sum over N observations and require at least M of them to be nonmissing?

```/*Assuming there is no gap between dates*/
data have;
call streaminit(123);
do firm='A' ,'B' ,'C';
do date='01jan2010'd to '20dec2021'd;
x=rand('uniform');output;
end;
end;
format date date9.;
run;

%let n=8;
%let m=6;
data want;
set have;
by firm;
array t{0:%eval(&n.-1)} _temporary_;
if first.firm then do; n=0; call missing(of t{*});end;
n+1;
t{mod(n,&n.)}=x;
if n ge &n. and n(of t{*}) ge &m. then moving_mean=mean(of t{*});
drop n;
run;```
Discussion stats
• 4 replies
• 449 views
• 2 likes
• 3 in conversation