Merging Data

[lisahoward]

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.

[vishal_prof_gmail_com]

Hi,

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.

Vishal

[lisahoward]

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,

[vishal_prof_gmail_com]

Ok,

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.

Vishal

[LinusH]

Pls also share some sample input and desired output data.

[lisahoward]

Hi here is the code I used and it worked perfectly thank you so much for your help. 

proc sql;

  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;

quit;