merge two datasets having multiple observations

[Qian Cai]

To Stata experts:

I have a master dataset, which has duplicated patient_id as each corresponds to one drug_issue_date. This dataset also contains an index date, which is unique for each patient_id.

he second using dataset also contains duplicated patient_id as each of them corresponds to a disease_diagnosis_date. I want to merge the using dataset with the master one not using the m:m mechanism, and keep those disease_diagnosis_date prior to index date. Appreciate if you have a solution.

I attached the datasets below for your reference:

master dataset：

* Example generated by -dataex-. For more info, type help dataex
clear
input double patient_id float(drug_issue_date index_date)
168620018 16257 14914
168620018 16456 14914
168620018 16737 14914
168620018 18926 14914
168620018 20459 14914
246320018 21801 19571
246320018 22113 19571
246320018 22229 19571
246320018 22284 19571
246320018 20576 19571
246320018 20786 19571
270020018 18716 17666
270020018 20069 17666
270020018 20124 17666
270020018 21077 17666
270020018 19816 17666
275220018 21698 20537
275220018 21983 20537
275220018 22134 20537
275220018 22378 20537
275220018 21791 20537
393920018 17811 17000
393920018 19372 17000
393920018 20396 17000
393920018 19509 17000
393920018 15273 17000
393920018 21557 17000
393920018 22291 17000
448820018 21853 18501
448820018 21976 18501
448820018 21312 18501
448820018 19022 18501
448820018 20430 18501
end
format %d drug_issue_date
format %td index_date

using dataset：

* Example generated by -dataex-. For more info, type help dataex
clear
input double patient_id float disease_diagnosis_date
  9520018 13515
  9520018 15614
 10620018  9831
 10620018 14243
 10620018 14251
168620018  9800
168620018 16824
168620018 16860
168620018 16925
168620018 16993
246320018 14243
246320018 14693
246320018 14955
270020018 -3652
270020018 16251
270020018 16265
270020018 16273
270020018 16315
270020018 16328
270020018 16344
270020018 16362
270020018 16365
270020018 16386
270020018 16594
270020018 16601
270020018 16681
270020018 16762
270020018 16779
270020018 16819
448820018  4333
448820018 16342
448820018 16744
448820018 16870
448820018 17118
448820018 17189
448820018 17204
448820018 17357
448820018 17381
448820018 17743
448820018 17813
448820018 18142
448820018 18157
448820018 18465
448820018 18715
448820018 18718
448820018 18794
448820018 18851
448820018 18976
end
format %d disease_diagnosis_date

For example, patient_id = 168620018, in master dataset, he has 5 observations with the index date on 31Oct2000. In the using dataset, he also has five observations, with the first disease_diagnosis _date on 31Oct1986 but the other four (23Jan2006, 28Feb2006, 04May2006, 11Jul2006) later than the index (31Oct2000). I understand I should avoid using m:m merge mechanism, but how can I merge using m:1 or 1:m, keeping the disease_diagnosis_date occurred before index date?

Thanks a lot in advance and look forward to your guidance.

[Devra Golbe]

I think this will get you part way there. Code is not tested. Call your first dataset dat1 and your second dat2.

*Create a dataset with just patient_id & index_date
use dat1
drop drug_issuedate
duplicates drop
save indx

*Merge dat2 with indx, and drop observations where disease_diagnosis_date> index_date
clear
use dat2
merge m:1 patient using indx
drop if disease diagnosis_date> index_date
save dat3

dat3 contains only the observations from dat2 with disease_date < index_date. It's not clear to me what kind of merge you want to do between these observations and dat1.

[George Ford]

This may get you started. joinby is the command you want.

use master, clear
joinby patient_id using using, unmatched(master)
keep if disease_diagnosis_date < index_date