DATA Step, Macro, Functions and more

Filling down N # of observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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: 5,518

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;

View solution in original post


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

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

Posted in reply to Astounding

Excellent, thank you!

Super User
Posts: 10,046

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 218 views
  • 1 like
  • 3 in conversation