Parent Child join

[Chip1963]

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)

[Reeza]

Do you have a license for SAS OR? If so, PROC BOM can help with that type of problem.

[Chip1963]

No SAS/OR

We have SAS/Enterprise Guide, Base, Visual Analytics

[pink_poodle]

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;

[PGStats]

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.

[Chip1963]

(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

[Ksharp]

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;

[PGStats · Accepted solution]

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;