Thank you so much dcruik for helping me think of the data another way! Ballardw - to elaborate on my problem: I have a dateset with MRN numbers, dx codes, dates of diagnoses and dates of treatment. So patient 1 could be diagnosed with condition X on 1/15/2001, receive treatment Y on 1/25/2001. This person could later be diagnosed with condition Z on 5/16/2015, and receive treatment Y on 6/1/2015. What I would like to do is create a dataset where each row represents a patient so that I can merge this information with other demographic information I have for the patients. Using dcruik's thinking I sorted the data by mrn and diagnosis date. I created a macro and transposed the data for each variable. I then merged all of the newly created datasets by MRN number. I know this is clunky but it seems to have worked? I'm still checking for accuracy and would welcome suggestions if my process raises any immediate redflags! Below is simplified/dummy data data have; format MRN 3. DxDt mmddyy10. Dx $2. TX $4. TxDate mmddyy10.; informat MRN 3. DxDt mmddyy10. Dx $2. TX $4. TxDate mmddyy10.; input MRN DxDt Dx $ TX $ TxDate; datalines; 1 1/15/2001 A Rad 2/1/2001 2 11/6/1999 A Sur 12/16/1999 2 7/11/2005 B Che 9/25/2005 1 4/2/2010 C Sur 6/8/2010 1 4/15/1989 D . . 3 9/17/2014 A Rad 9/20/2014 4 10/11/2007 B Che 11/1/2007 ; run; proc sort data=have; by mrn dxdt; run; %macro transpose(x); proc transpose data=have out=want_&x (drop=_Name_) prefix=&x; var &x; by mrn; run; %mend transpose; %transpose(DxDt) %transpose(Dx) %transpose(TX) %transpose(TxDate) data want; merge want_dxdt want_dx want_tx want_txdate; by mrn; run;
... View more