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;
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;
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;
Excellent, thank you!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.