topic Fill down row if missing for all variables in SAS Programming

Fill down row if missing for all variables

eabc0351 — Thu, 17 Sep 2020 00:25:00 GMT

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.

Re: Fill down row if missing for all variables

eabc0351 — Thu, 17 Sep 2020 00:27:02 GMT

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

Re: Fill down row if missing for all variables

novinosrin — Thu, 17 Sep 2020 00:34:07 GMT

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;

Re: Fill down row if missing for all variables

eabc0351 — Thu, 17 Sep 2020 00:40:17 GMT

Oh that was so easy. I tried it but had the code a little off. Thank you!

Re: Fill down row if missing for all variables

CurtisMackWSIPP — Thu, 17 Sep 2020 00:54:36 GMT

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.

Re: Fill down row if missing for all variables

eabc0351 — Thu, 17 Sep 2020 00:55:43 GMT

Thank you @CurtisMackWSIPP, I will give that a try as well.