Say you have dataset HAVE with variable X. You want to create X1 as follows:
If X is non-missing then X1=X
if X is part of a series of 7 or more missing values, then X1=.
if X is otherwise missing then X1= a backward moving average of the last 10 records (MOVAVE 10)
I don't see how you can do that in a single proc expand, but you could use PROC EXPAND to create dataset NEED, in which X1 is a trailing moving average for all missing values. Then read in dataset NEED, and convert X1 to missing for all cases of 7 or more consecutive X's:
Regards,
Mark
proc expand data=have out=need method=none;
convert x=x1 / transformout=(missonly movave 10);
run;
data want;
/* count a sequence of records having a single X value*/
do n=1 by 1 until (last.x);
set need;
by x notsorted;
end;
/* If the count>=7 and the X value is missing set a flag */
if n>=7 and x=. then flag=1;
/* Reread the same records, and if flag=1 set X1 to missing*/
do until (last.x);
set need;
by x notsorted;
if flag=1 then x1=.;
output;
end;
drop flag n;
run;
... View more
