Quartz | Level 8

## Fill down row if missing for all variables

Hello. I have the following data:

``````data have;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 . . .
2 -20 -19 -18 -17 1
2 -16 . . . .
3 -8 -7 -6 -5 .
3 -4 . . . .
;
run;``````

I would like the following data:

``````data want;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 -10 -9 0
2 -20 -19 -18 -17 1
2 -16 -19 -18 -17 1
3 -8 -7 -6 -5 .
3 -4 -7 -6 -5 .
;
run;``````

This is just a sample of the variables in the table. I would like to do this for every variable in the table. Each ID has 2 observations. Essentially, I would like to "fill down" if the value in last.id is missing. Could someone please help me with some code. I figured out how to do this for one variable at a time, but cannot figure out how to do it for all the variables in one easy step. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Fill down row if missing for all variables

``````data have;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 . . .
2 -20 -19 -18 -17 1
2 -16 . . . .
3 -8 -7 -6 -5 .
3 -4 . . . .
;
run;

data want;
update have(obs=0) have;
by id;
output;
run;``````
5 REPLIES 5
Quartz | Level 8

## Re: Fill down row if missing for all variables

Quick edit -- the first row is supposed to be:
1 -11 -10 -9 -8 0
1 -7 -6 -9 -8 0
Tourmaline | Level 20

## Re: Fill down row if missing for all variables

``````data have;
input id day1 day2 day3 day4 otherd7;
datalines;
1 -11 -10 -9 -8 0
1 -7 -6 . . .
2 -20 -19 -18 -17 1
2 -16 . . . .
3 -8 -7 -6 -5 .
3 -4 . . . .
;
run;

data want;
update have(obs=0) have;
by id;
output;
run;``````
Quartz | Level 8

## Re: Fill down row if missing for all variables

Oh that was so easy. I tried it but had the code a little off. Thank you!
Lapis Lazuli | Level 10

## Re: Fill down row if missing for all variables

Glad you made that correction, I was having trouble replicating it.  Here is a fairly elegant solution.

```data mywant;
set have;
array toretain day1 day2 day3 day4 otherd7;
by id;
do over toretain;
junk = lag(toretain);
toretain = coalesce(toretain,junk);
end;
output;
if last.id then do over toretain;
toretain = .;
end;
run;```

Note that the real trick is setting junk = to the lag.  Lag only works if it is called every row.  If the lag was in the coalesce will not call it if the current row has a value.

Quartz | Level 8

## Re: Fill down row if missing for all variables

Thank you @CurtisMackWSIPP, I will give that a try as well.
Discussion stats
• 5 replies
• 495 views
• 0 likes
• 3 in conversation