BookmarkSubscribeRSS Feed
Calcite | Level 5

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.

Obsidian | Level 7


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.


Calcite | Level 5

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,

Obsidian | Level 7


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.


Tourmaline | Level 20

Pls also share some sample input and desired output data.

Data never sleeps
Calcite | Level 5

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;



Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3 in conversation