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

[stataq]

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.

[Patrick]

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;

[stataq]

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

[Patrick · Accepted solution]

---

@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;