topic Re: How to fill missing value base on another col and the last row value in SAS Programming

How to fill missing value base on another col and the last row value

stataq — Sat, 14 Oct 2023 22:48:42 GMT

Hello,

I would like to get the table on right hand side, from a table that is on the left hand side:

Basically, I want when `order`=., and then by `ID`, `order` will be last `order`+1. The red number will be the number I want.

What should I do in order to get such result.

Thanks.

Re: How to fill missing value base on another col and the last row value

Patrick — Sat, 14 Oct 2023 23:38:29 GMT

Please provide sample data via a SAS data step that creates the data.

If the Have data you shared is representative for your real data then below should work.

data want;
  set have(drop=order);
  by id notsorted;
  if first.id then order+1;
run;

proc print data=want;
run;

Re: How to fill missing value base on another col and the last row value

stataq — Sun, 15 Oct 2023 01:03:53 GMT

It looks like this method will change current order 2& 3 to 2, and only have 6 instead 7. Any way to solve this?

Re: How to fill missing value base on another col and the last row value

Patrick — Sun, 15 Oct 2023 01:27:56 GMT

@stataq wrote:
It looks like this method will change current order 2& 3 to 2, and only have 6 instead 7. Any way to solve this?

That's why you should provide sample data via a SAS datastep so we can actually test the code we propose.

data have;
  infile datalines dsd dlm=',' truncover;
  input id $ order order_want;
  datalines;
B1,,1
B1,,1
,2,2
,2,2
,3,3
,3,3
,3,3
C1,,4
C1,,4
C1,,4
C3,,5
C3,,5
,6,6
,6,6
D4,,7
D4,,7
;
data want;
  set have(rename=(order=order_have));
  by id notsorted;
  if not missing(order_have) then order_derived=order_have;
  else if first.id then order_derived+1;
run;

proc print data=want;
run;