Create a family tree by join data sets

[sarahzhou]

Hi,

I have a table where the relation between two is known.

I want to create a table for a couple related to their child records, for example:

I've tried to duplicate the table then left join the id1's and id2's, however I couldn't get the correct result. 

code show as below:

data rf;
input p1$ p2$ rlf$;
cards;
f123 s234 son
s234 f123 father
m345 s234 son
s234 m345 mother
f123 m345 wife
m345 f123 husband
;

data rb;
input p1$ p2$ rlb$;
cards;
f123 s234 son
s234 f123 father
m345 s234 son
s234 m345 mother
f123 m345 wife
m345 f123 husband
;

proc sql;
select rf.p1 as id1, rf.rlf as name1, 
       rb.p2 as id2, rb.rlb as name2, 
       rb.p1 as id3, rb.rlb as name3 
from rf left join rb
on rf.p2=rb.p1
where (rlf in ("father", "mother") and rlb in ("son","daughter"));
quit;

I get the wrong result:

How can I get the correct result as this?

Kindly advise

Thank you!

[mkeintz]

It's not exactly a family tree, is it?  It appears you only need to prepare for two generations.  Is that correct?

[sarahzhou]

yes, only two generations. thank you!

[PGStats · Accepted solution]

Why do you need duplicate results? Wouldn't this be more useful :

data rf;
input p1$ p2$ rlf$;
cards;
f123 s234 son
s234 f123 father
m345 s234 son
s234 m345 mother
f123 m345 wife
m345 f123 husband
;

proc sql;
/* create table cmf as */
select unique
    a.p2 as child,
    b.p2 as mother,
    c.p2 as father
from 
    rf as a left join
    rf as b on a.p2 = b.p1 left join
    rf as c on a.p2 = c.p1
where a.rlf in ("son","daughter") and b.rlf = "mother" and c.rlf = "father";
quit;

Or maybe :

proc sql;
/* create table cmf as */
select unique
    a.p2 as child,
    a.rlf as rlf,
    b.p2 as mother,
    c.p2 as father
from 
    rf as a left join
    rf as b on a.p2 = b.p1 left join
    rf as c on a.p2 = c.p1
where a.rlf in ("son","daughter") and b.rlf = "mother" and c.rlf = "father";
quit;

[PGStats]

But you could also simply transpose your actual data :

proc sort data=rf; by p1 rlf; run;

proc transpose data=rf out=cmf(drop=_name_);
where rlf in ("mother", "father", "uncle", "aunt");
by p1;
id rlf;
var p2;
run;