I am trying to merge two datasets based on two variables: MotherID and PregnancyID. PregnancyID is nested within MotherID as some mothers have multiple pregnancies.

My first dataset (I shall call master) contains multiple observations for each MotherID (details of different events) and there are no Pregnancy IDs in the dataset. My second dataset (I shall call it using) contains all of the PregnancyIDs and their corresponding MotherIDs.

I tried a simple m:m merge on MotherID but this then just assigns the PregnancyIDs to different random observations within the corresponding MotherID. What I would like instead is to have all of the observations within each MotherID to all be assigned to each of the PregnancyIDs, which would involve expanding the total number of observations in the dataset. I wondered if the data requires a reshape but can't think how to make this work.

Thank you for any advice.