Across observation comparison, a data merge question

[Bill_S]

Hi All,

I have 2 data sets for parents and child, like this:

Set A

ID        Mother_ID   Father_ID

1              .                1          

2             2                .

3             .                 .

Set B

ID    X  

1     a        

2     b      

3     c    

Question:

How can I merge two data sets to get a data set like this ( 3 is a child):

ID      X    X_Mother    X_Father

3       c       b                   a

Thanks!

[RaviKommuri]

Hi,

Where is the actual relation between Child and Parents -  in which dataset?

[KachiM · Accepted solution]

data a;

input id mid fid;

datalines;

1 .  1

2 2  .

3 .  .

;

run;

data b;

input id X $1.;

datalines;

1 a

2 b

3 c

;

run;

Get the ID for Mother(m) and Father(f)

data have;

retain m f;

   set a end = eof;

   if mid then m = _N_;

   else if fid then f = _N_;

   if eof then output;

keep m f;

run;

Use m and f to name the Variables, X_Mother and X_Father respectively.

data want;

   if _n_ = 1 then set have;

   set b end = last;

      retain X_Mother X_Father;

      if id = m then X_Mother = X;

      else if id = f then X_Father = X;

   if last and X = 'c';

keep X:;

run;

[Bill_S]

Dear datasp - this works. Thanks a lot!