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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.