topic Repeat Values For Unique ID With Exceptions in SAS Programming

Repeat Values For Unique ID With Exceptions

chsprogramming — Sun, 17 Sep 2023 21:06:20 GMT

I have a column with IDs that repeat, and I would like to translate the second column to repeat the first value for each unique ID. EXCEPT for when the value is NULL, the value needs to stay NULL. Below is an example of the column I have, and the column I want.

ID  HAVE  WANT
1   12    12
1   19    12
1   23    12
2   40    40
2   NULL  NULL
2   25    40
3   NULL  NULL
3   11    11
3   NULL  NULL
4   24    24
4   NULL  NULL
4   72    24
4   NULL  NULL

Re: Repeat Values For Unique ID With Exceptions

Patrick — Mon, 18 Sep 2023 00:32:33 GMT

Something like below should do.

data have;
  input id have want;
  datalines;
1 12 12
1 19 12
1 23 12
2 40 40
2 . .
2 25 40
3 . .
3 11 11
3 . .
4 24 24
4 . .
4 72 24
4 . .
;

data want(drop=_:);
  set have;
  by id;
  retain _tmp;
  if first.id then call missing(_tmp);
  if not missing(have) then
    do;
      if missing(_tmp) then _tmp=have;
      derived=_tmp;
    end;
run;

proc print data=want;
run;