Calcite | Level 5

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

## 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;

3 REPLIES 3
Fluorite | Level 6

## Re: Across observation comparison, a data merge question

Hi,

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

Rhodochrosite | Level 12

## 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;

Calcite | Level 5

## Re: Across observation comparison, a data merge question

Dear datasp - this works. Thanks a lot!

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