If the number of levels is fixed, you can use this method:
data have;
input ID Sequence Level Name :$12. ParentID;
datalines;
1 1 1 Elizabeth .
11 2 2 Charles 1
111 3 3 William 11
1111 4 4 George 111
1112 5 4 Charlotte 111
1113 6 4 Louis 111
112 7 3 Henry 11
12 8 2 Anne 1
13 9 2 Andrew 1
131 10 3 Beatrice 13
132 11 3 Eugenia 13
14 12 2 Edward 1
141 13 3 Louise 14
142 14 3 Severn 14
;
proc sql;
create table want0 as
select
l1.id as Id1,
l1.level as lv1,
l1.sequence as Seq1,
l1.name as Name1,
l2.id as Id2,
l2.level as lv2,
l2.sequence as Seq2,
l2.name as Name2,
l3.id as Id3,
l3.level as lv3,
l3.sequence as Seq3,
l3.name as Name3,
l4.id as Id4,
l4.level as lv4,
l4.sequence as Seq4,
l4.name as Name4
from
have as l1
left join have as l2 on l1.parentId=l2.id
left join have as l3 on l2.parentId=l3.id
left join have as l4 on l3.parentId=l4.id;
quit;
/* Reorder the levels */
data want;
set want0;
array i id: ;
array s Seq: ;
array n Name: ;
array l lv: ;
array SequenceLevel{4};
array NameLevel{4} $12;
array IdLevel{4};
do k = 1 to dim(l);
if not missing(l{k}) then do;
SequenceLevel{l{k}} = s{k};
NameLevel{l{k}} = n{k};
IdLevel{l{k}} = i{k};
end;
end;
keep SequenceLevel: NameLevel: IdLevel: ;
run;
proc print data=want noobs; run;
... View more
