Help using Base SAS procedures

Challenging dataset merge issue

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Challenging dataset merge issue

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                   .                .


Accepted Solutions
Solution
‎01-10-2015 03:47 PM
PROC Star
Posts: 7,363

Re: Challenging dataset merge issue

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;

View solution in original post


All Replies
Solution
‎01-10-2015 03:47 PM
PROC Star
Posts: 7,363

Re: Challenging dataset merge issue

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;

New Contributor
Posts: 2

Re: Challenging dataset merge issue

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

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 205 views
  • 0 likes
  • 2 in conversation