Yes I managed to modify it. Thanks for the hint - the solution with custom format is very elegant. %macro track_id_sequences(id1,id2,input_data,output_data,max_iterations);
/* 1. Store id data as a custom format = dictionary*/
data temp; set &input_data.;
retain fmtname '$id';
rename &id2. = label &id1.=start;
run;
proc format cntlin=temp; run;
/* 2. Translate the next id of each row multiple times until reaching the sequence end
+ store number of translation = order of the id within the sequence */
data &output_data.; set &input_data.;
sequence_end = &id1.;
test_next = &id2.;
do seq_order=1 to &max_iterations. until (sequence_end = test_next);
sequence_end = test_next;
test_next = put(sequence_end, $id.);
end;
run;
/* 3. Group sequences by ending and order rows by order within the sequence
+ add sequence ending as additional row */
proc sort data=&output_data. out=&output_data.;
by sequence_end descending seq_order;
run;
data &output_data.(keep= id_sequences sequence_end seq_order); set &output_data.;
by sequence_end;
if last.sequence_end then do;
output; &id1. = sequence_end; seq_order=0; output;
end; else output;
rename &id1. = id_sequences ;
run;
%mend track_id_sequences;
data id_lists;
input id_prev $ id_next $;
datalines;
a b
w x
b c
x y
c d
;
%track_id_sequences(id1=id_prev , id2=id_next , input_data=id_lists, output_data=Result, max_iterations=100);
... View more