topic Re: Merging Data in SAS Procedures

Merging Data

lisahoward — Tue, 25 Feb 2014 09:24:09 GMT

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.

Re: Merging Data

vishal_prof_gmail_com — Tue, 25 Feb 2014 09:46:49 GMT

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

Re: Merging Data

lisahoward — Tue, 25 Feb 2014 09:55:30 GMT

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,

Re: Merging Data

vishal_prof_gmail_com — Tue, 25 Feb 2014 10:19:55 GMT

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

Re: Merging Data

LinusH — Tue, 25 Feb 2014 14:56:47 GMT

Pls also share some sample input and desired output data.

Re: Merging Data

lisahoward — Tue, 25 Feb 2014 15:22:24 GMT

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;