Converting drug-level data (medications) to a person-level data in NHANES using PROC SUMMARY

[amoney518]

Hello all, 

I'm working on a project using NHANES and trying to merge datasets. One dataset (MEDS) lists medications patients are taking as individual observations, all with a common variable SEQN that is a unique identifier for individual patients.

In order to merge the datasets I must convert this drug-level data to patient-level data using the SEQN variable in a format that retains  the variables RXDDRGID, RXDDRUG, and  RXDUSE from the MEDS dataset without having redundant observations in my combined dataset merged by SEQN.

I encountered this document which appears to have the right information but frankly it's way over my head.

So far, I have read that the proc summary procedure may be the best way and this is what I have:

proc summary nway data=NHANES.MEDS;
class SEQN RXDDRGID RXDDRUG RXDUSE;
id SEQN RXDDRGID RXDDRUG RXDUSE;
output out=NHANES.FIXEDMEDS;
run;

but this is returning a dataset with the right variables but too many observations.

I am very new to SAS so any help would be greatly appreciated!

[Reeza]

Do you need to summarize data before you change the structure? If so, you likely need PROC SUMMARY. If not, you likely want PROC TRANSPOSE. https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

[Reeza · Accepted solution]

Sorry, you're going to long to wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

[amoney518]

Thanks for your help! This is definitely what I was looking for. 

However, I'm still having trouble getting the right number of observations in my output datasets. I've made new code below that sorts the original dataset by the variables of interest (since each observation doesn't have a unique identifier), transposes the three variables of interest individually, then merges them by SEQN but this doesn't seem to be working as anticipated. 

Any ideas?

proc sort data=NHANES.MEDS out=NHANES.SORTEDMEDS;
by SEQN RXDDRGID;
run;

proc transpose data=NHANES.SORTEDMEDS out=NHANES.RXDDRGIDMEDS prefix=RXDDRGID;
by SEQN RXDDRGID;
var RXDDRGID;
run;

proc transpose data=NHANES.SORTEDMEDS out=NHANES.RXDDRUGMEDS prefix=RXDDRUG;
by SEQN RXDDRGID;
var RXDDRUG;
run;

proc transpose data=NHANES.SORTEDMEDS out=NHANES.RXDUSEMEDS prefix=RXDUSE;
by SEQN RXDDRGID;
var RXDUSE;
run;

data NHANES.FIXEDMEDS;
merge NHANES.RXDDRGIDMEDS (drop=_name_) NHANES.RXDDRUGMEDS (drop =_name_) NHANES.RXDUSEMEDS (drop=_name_);
by SEQN ;
run;

[amoney518]

Actually, I seem to have solved by the problem by limiting the BY statement to only SEQN within the PROC TRANSPOSE procedures. 

Thanks!