02-25-2014 04:24 AM
Hello, I am trying to merge two datasets. The first contains all occurrences of pregnancy. The second contains therapies. I want to merge the two together by PATID. The problem is there are more therapy dates then there are pregnancies. I want to be able to put all therapies for each occurrence of pregnancy so that i can then see which therapy was taken after a given pregnancy. At the moment however the merge does this - e.g. two pregnancies same PATID 7 therapies same PATID merge together final dataset has 7 rows. i want the final dataset to have 14 rows with the 7 therapies assigned to both rows of pregnancy dataset. Is there a way to do this - maybe using SQL if not SAS. Many thanks.
02-25-2014 04:46 AM
Tell me , if PAT id of two or more pregnancies (as in your example) are same , then both of them have same therapies , or there can be a case where PAT id is same for two pregnancies but therapies might be different. If therapies are same it can be done on SAS as well SQL.
02-25-2014 04:55 AM
PATID would be the same so for example I would have two pregnancies in the PREG dataset both 101. I would then have 7 therapies in the THERAPY dataset all with PATID 101. So in the merged final dataset I would basically have the therapies duplicated for each pregnancy event. Many thanks,
02-25-2014 05:19 AM
I think using a left join would solve the purpose
select a.PATID , b.Therapy
from PREG a left join THERAPY b on a.PATID = b.PATID
I assume that Therapy data set has unique PATIDs. Please share the code you have written.
02-25-2014 10:22 AM
Hi here is the code I used and it worked perfectly thank you so much for your help.
create table Preg_GLP
as select a.*, b.index_date, b.PatBegDate, b.PatEndDate
from Patient_Medication_GLP a left join Patient_Summary_Preg b on a.PATID = b.PATID;