Hello fellow SAS EG users.
I need to run a parent child where each parent and each child have a separate row, with the complete pedigree for each row.
The sample attached is a more visual aspect using the British royal family as samples.
I've used left outer joins from each sub-table, but that leaves me with the rows highlighted in salmon missing.
Any idea how to get those extra rows, so that I get 1 row per family member whether it has descendants or not.
(the sequence number isn't used here, but it helps sort the table adequately in the end by level).
Thank you very much.
Chip1963 from Ottawa Canada
(Same as excel file attached)
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;
No SAS/OR
We have SAS/Enterprise Guide, Base, Visual Analytics
This is an interesting question. It is a one-to-many merge. You may want to adapt to your data this working example from UCLA IDRE:
data dads; input fid name $ inc ; cards; 2 Art 22000 1 Bill 30000 3 Paul 25000 ; run; * Next we make the "kids" data file ; data kids; input famid kidname $ birth age wt sex $ ; cards; 1 Beth 1 9 60 f 1 Bob 2 6 40 m 1 Barb 3 3 20 f 2 Andy 1 8 80 m 2 Al 2 6 50 m 2 Ann 3 2 20 f 3 Pete 1 6 60 m 3 Pam 2 4 40 f 3 Phil 3 2 20 m ; run; proc sql; create table dadkid2 as select * from dads, kids where dads.fid=kids.famid order by dads.fid, kids.kidname; quit;proc print data=dadkid2; run;
If the number of levels is given, you can get by with SQL left joins.
Post some downloadable (text) data if you want us to demonstrate.
(Text file attached, same as below)
There are 4 levels in this scenario.
14 records.
4 fields for each, including a parent-child relationship key.
ID Sequence Level Name Parent ID
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
The following code could give you a start.
data have;
input _end _start ;
cards;
2 1
3 2
4 2
5 2
6 3
7 6
8 7
;
run;
%let start=2;
data want(keep=path);
if _n_ eq 1 then do;
length path _path $ 700 ;
if 0 then set have;
declare hash ha(hashexp:20,dataset:'have(where=(_start is not missing and _end is not missing))',multidata:'y');
ha.definekey('_start');
ha.definedata('_end');
ha.definedone();
declare hash pa(ordered:'y');
declare hiter hi_path('pa');
pa.definekey('n');
pa.definedata('n','path');
pa.definedone();
end;
set have(where=(_start=&start));
count=1;n=1;_n=1;
path=catx('|',_start,_end);
putlog 'WARNING:Found ' _end;
pa.add();output;
do while(hi_path.next()=0);
if n ne 1 then pa.remove(key:_n);_n=n;
_path=path;
_start=scan(path,-1,'|');
rc=ha.find(); /* if rc ne 0 then output; */
do while(rc=0);
if not findw(path,strip(_end),'|') then do;
if length(path)+length(_end)+1 gt lengthc(path) then do;
putlog 'ERROR: The length of path and _path are set too short';
stop;
end;
putlog 'WARNING:Found ' _end;
count+1;n=count;
path=catx('|',path,_end);
pa.add(); output;
path=_path;
end; /* else output; It is a circle.*/
rc=ha.find_next();
end;
end;
pa.clear();
run;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.