I have data where i have a broken sequence of numbers (seq_have) where i want to continue the sequence from largest number in the existing sequence for each ID, for the observations with missing sequence numbers. The order of the observations without sequence numbers is arbitrary, as long as it doesn't overlap and is larger then the largest number of the existing sequence inside the group. The solution i have come up with doesn't seem very elegant, and I'm worried it will be prone to errors once implemented at a larger scale. Any thoughts on the likelihood of errors that could occur, or how to improve the solution? The example: data example;
input ID $ seq_have data;
cards;
1 1 111
1 2 222
1 . 333
2 . 444
2 2 222
2 3 333
3 . 123
3 . 123
3 . 123
4 4 444
4 . 555
;
run; Attempted solution: proc sql;
create table int1 as select *, max(seq_have) as seq_max from example group by id;
quit;
proc sort data=int1;
by id seq_have;
run;
data int2;
set int1;
by id ;
seq_new +1;
if first.id then seq_new = 1;
run;
data result;
set int2;
if seq_have eq . then
seq_res = sum(seq_new,seq_max);
if seq_have not eq . then seq_res = seq_have;
run;
proc sort data=result;
by id seq_res;
run;
... View more