topic Filling down N # of observations in SAS Programming

Filling down N # of observations

ttatum — Mon, 03 Apr 2017 15:52:07 GMT

Hello, I am trying to fill down a variable within a by-group to replace missing values. However, when filling down I only want to fill at most 3 observations if a non-blank value is not seen.

For instance, I start with a data set "have" below, and need it to turn out like "want." I can easily fill missing values by-group in all cases (see "test1" below), but can't quite figure out how to set a limit on the number of observations to fill down.

Any suggestions on how to best accomplish this?

Data have;
 input id var1;
 datalines;
 1 54
 1 60
 1 .
 1 .
 1 .
 1 .
 1 .
 2 100
 2 .
 2 .
 2 200
 2 .
 2 .
 ;
Run;

Data want;
 input id var1;
 datalines;
 1 54
 1 60
 1 60
 1 60
 1 60
 1 .
 1 .
 2 100
 2 100
 2 100
 2 200
 2 200
 2 200
 ;
Run;

Data test1;
 set have;
 by id;
 retain _var1;
 if not missing(var1) then _var1=var1;
 else var1=_var1;
 drop _var1;
Run;

Re: Filling down N # of observations

Astounding — Mon, 03 Apr 2017 16:53:19 GMT

Try it this way:

data want;

set have;

by id;

if first.id or var1 > . then do;

_var1 = var1;

retain _var1;

replaced_count=0;

end;

if var1=. then do;

var1=_var1;

replaced_count + 1;

if replaced_count=3 then _var1=.;

end;

run;

Re: Filling down N # of observations

ttatum — Mon, 03 Apr 2017 17:31:40 GMT

Excellent, thank you!

Re: Filling down N # of observations

Ksharp — Tue, 04 Apr 2017 03:26:38 GMT

Data have;
 input id var1;
 datalines;
 1 54
 1 60
 1 .
 1 .
 1 .
 1 .
 1 .
 2 100
 2 .
 2 .
 2 200
 2 .
 2 .
 ;
Run;
data want;
 set have;
 by id;
 retain v;
 if first.id then do;n=0;v=.;end;
 if not missing(var1) then do;n=0; v=var1;end;
 n+1;
 if n gt 4 then v=.;
run;
proc print;run;