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

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

xyxu — Thu, 18 Aug 2022 03:57:38 GMT

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;

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

PeterClemmensen — Thu, 18 Aug 2022 06:51:31 GMT

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?

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

Ksharp — Thu, 18 Aug 2022 12:18:16 GMT

/*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;

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

xyxu — Thu, 18 Aug 2022 18:51:11 GMT

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

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

PeterClemmensen — Thu, 18 Aug 2022 19:07:23 GMT

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