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 . .
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;
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;
Arthur, you just made my day. Thank you so very much!! :smileylaugh:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.