Challenging dataset merge issue

rachelcakes — Sat, 10 Jan 2015 20:09:08 GMT

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

Re: Challenging dataset merge issue

art297 — Sat, 10 Jan 2015 20:47:45 GMT

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;

Re: Challenging dataset merge issue

rachelcakes — Sat, 10 Jan 2015 21:41:41 GMT

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

topic Re: Challenging dataset merge issue in SAS Procedures

Challenging dataset merge issue

Re: Challenging dataset merge issue

Re: Challenging dataset merge issue