Challenging dataset merge issue

[rachelcakes]

Hi all,

I've spent a ton of time researching and I can't find a solution to my problem. I have two databases: 1) Survey collected at baseline and 2) Medical data collected at baseline and f/u with data  in multiple rows per ID for different years the data was collected. I tried merging the two datasets by ID, but then the baseline survey doubled because it added the f/u data to the same lines. Any tips?

- Rachel

What I have

Database#1 Survey data (only collected at baseline)

ID     VAR1     VAR2

1          1          2

2          3          2

3          3          1

Database#2 Medical record data (some ID's have both baseline and follow-up data for BMI, but on different rows)

ID      Baseline      Followup      BMI

1        1                 0                 23

1        0                 1                 24

2        0                 1                 20

2        1                 0                 19

3        1                 0                 30

I want my merged database to look like this:

ID     VAR1     VAR2     BMI_baseline  BMI_fu     BMI_change

1          1          2          23                   24            +1

2          3          2          19                   20             -1

3          3          1          30                   .                .

[art297 · Accepted solution]

If your data are already sorted by ID:

data want (drop=baseline followup);

  merge database1

        database2 (keep=id baseline bmi rename=(BMI=BMI_Baseline)

                   where=(baseline eq 1))

        database2 (keep=id followup bmi rename=(BMI=BMI_FU)

                   where=(followup eq 1))

        ;

  by id;

  BMI_Change=BMI_FU-BMI_Baseline;

run;

[rachelcakes]

Arthur, you just made my day. Thank you so very much!! :smileylaugh: