Help using Base SAS procedures

Merging Data

Reply
Contributor
Posts: 57

Merging Data

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.

Contributor
Posts: 33

Re: Merging Data

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

Contributor
Posts: 57

Re: Merging Data

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,

Contributor
Posts: 33

Re: Merging Data

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

Super User
Posts: 5,260

Re: Merging Data

Pls also share some sample input and desired output data.

Data never sleeps
Contributor
Posts: 57

Re: Merging Data

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;

Ask a Question
Discussion stats
  • 5 replies
  • 252 views
  • 0 likes
  • 3 in conversation