Filling down N # of observations

Solved
Occasional Contributor
Posts: 5

Filling down N # of observations

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;`

Accepted Solutions
Solution
‎04-03-2017 01:31 PM
Super User
Posts: 6,903

Re: Filling down N # of observations

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;

All Replies
Solution
‎04-03-2017 01:31 PM
Super User
Posts: 6,903

Re: Filling down N # of observations

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;

Occasional Contributor
Posts: 5

Re: Filling down N # of observations

Excellent, thank you!

Super User
Posts: 10,846

Re: Filling down N # of observations

``````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;``````
☑ This topic is solved.