Across observation comparison, a data merge question

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Across observation comparison, a data merge question

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!


Accepted Solutions
Solution
‎10-30-2014 04:43 AM
Super Contributor
Posts: 290

Re: Across observation comparison, a data merge question

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;

View solution in original post


All Replies
Contributor
Posts: 45

Re: Across observation comparison, a data merge question

Hi,

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

Solution
‎10-30-2014 04:43 AM
Super Contributor
Posts: 290

Re: Across observation comparison, a data merge question

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;

New Contributor
Posts: 2

Re: Across observation comparison, a data merge question

Dear datasp - this works. Thanks a lot!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 250 views
  • 0 likes
  • 3 in conversation