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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: