Splitting observations in one dataset to then be able to do a 1:1 merge with a second dataset

[Tabitha Green]

Hello again. I have a dataset with charted medicines that are intended for administration, and a second dataset with administered medicines. I need to merge the two datasets using patientid medicineid and dosetime.

If I merge using a 1:m on just patientid and medicineid, it works to merge the Once Daily medicines. The Two, Three or Four Times merge, but the values for dosetime are retained from one dataset as they don't align.

If I only keep medicines with frequency "Once Daily (OD)", they merge nicely with a 1:1 patientid medicineid and dosetime.

My quetion is, how do I create new observations using splitting using the , in the frequency variable to make 2 3 or 4 separate observations if frequency == "Twice Daily", "Three Times Daily" or "Four Times Daily" so that I can merge the two datasets together cleanly? I have tried the below code and it appears to work, but it also feels clumsy and that it could be messy checking it in the full dataset with over 60,000 observations.

expand 2 if frequency == "Twice Daily (BD)", gen(twotimes)
expand 3 if frequency == "Three Times Daily (TDS)", gen(threetimes)
expand 4 if frequency == "Four Times Daily (QID)", gen(fourtimes)

bysort patientid medicineid: gen instance = _n if frequency == "Three Times Daily (TDS)" | frequency == "Four Times Daily (QID)"

split dosetime, p(",")
forvalues i=1(1)4 {
replace dosetime`i' = trim(trim(trim(dosetime`i')))
}


gen dosetime_new = dosetime if frequency == "Once Daily (OD)"

replace dosetime_new = dosetime1 if (two == 0 & frequency == "Twice Daily (BD)")
replace dosetime_new = dosetime2 if (two == 1 & frequency == "Twice Daily (BD)")

replace dosetime_new = dosetime1 if (frequency == "Three Times Daily (TDS)" & instance == 1)
replace dosetime_new = dosetime1 if (frequency == "Four Times Daily (QID)" & instance == 1)

replace dosetime_new = dosetime2 if (frequency == "Three Times Daily (TDS)" & instance == 2)
replace dosetime_new = dosetime3 if (frequency == "Three Times Daily (TDS)" & instance == 3)

replace dosetime_new = dosetime2 if (frequency == "Four Times Daily (QID)" & instance == 2)
replace dosetime_new = dosetime3 if (frequency == "Four Times Daily (QID)" & instance == 3)
replace dosetime_new = dosetime4 if (frequency == "Four Times Daily (QID)" & instance == 4)

drop twotimes threetimes fourtimes dosetime1 dosetime2 dosetime3 dosetime4 instance

rename dosetime dosetime_original
rename dosetime_new dosetime

There are a lot of other variables that relate to the medicines, but these are the only four relevant ones to the merge.

patientid : identifies the unique individual in both datasets
medicineid : uniquely identifies the specific medicine for the unique individual in both datasets
frequency : identifies the dose schedule (Once Daily, Twice Daily, Three Times Daily or Four Times Daily) in the charted medicines dataset only
dosetime: intended dose time(s) in charted or the intended dose time (singular) in administered

The charted dataset using dataex:
clear
input double(patientid medicineid) str34 frequency str230 dosetime
354944 6318358616 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
354944 6424582976 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
354944 8358732104 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
354944 3501802864 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
354944 3886898152 "Three Times Daily (TDS)" "Breakfast (06:30 - 09:30),Lunch (11:30 - 13:30),Bed (19:30 - 21:30)"
994391 8379497520 "Once Daily (OD)" "Break (07:00 - 09:30)"
994391 5575214440 "Once Daily (OD)" "Break (07:00 - 09:30)"
994391 7912117008 "Once Daily (OD)" "Break (07:00 - 09:30)"
994391 4922824696 "Once Daily (OD)" "Break (07:00 - 09:30)"
994391 2770931360 "Three Times Daily (TDS)" "Break (07:00 - 09:30),Lunch (11:30 - 13:30),1700 (16:30 - 17:30)"
994391 1183780600 "Twice Daily (BD)" "Break (07:00 - 09:30),1700 (16:30 - 17:30)"
10282984 6802274792 "Once Daily (OD)" "Dinner (16:15 - 18:30)"
10282984 6725015184 "Once Daily (OD)" "Break (06:00 - 10:00)"
10282984 8571697072 "Once Daily (OD)" "Bedtime (19:00 - 22:00)"
10282984 2614886880 "Once Daily (OD)" "Break (06:00 - 10:00)"
10282984 5689661640 "Once Daily (OD)" "1400 (13:30 - 14:30)"
10282984 8026564208 "Once Daily (OD)" "1000 (09:30 - 10:30)"
10282984 8523212576 "Once Daily (OD)" "Break (06:00 - 10:00)"
10282984 7149879376 "Once Daily (OD)" "Bedtime (19:00 - 22:00)"
10282984 9211690944 "Three Times Daily (TDS)" "Break (06:00 - 10:00),Lunch (11:30 - 13:30),Dinner (16:15 - 18:30)"
10282984 5603293368 "Twice Daily (BD)" "Break (06:00 - 10:00),Dinner (16:15 - 18:30)"
10496133 597476752 "Once Daily (OD)" "Break (06:30 - 09:30)"
18292683 9474945268 "Once Daily (OD)" "Break (06:00 - 10:00)"
18292683 2478182224 "Twice Daily (BD)" "Break (06:00 - 10:00),Dinner (16:15 - 18:30)"
18292683 6830756504 "Twice Daily (BD)" "Break (06:00 - 10:00),Dinner (16:15 - 18:30)"
18292683 1085305760 "Twice Daily (BD)" "Break (06:00 - 10:00),Dinner (16:15 - 18:30)"
19571577 2926156472 "Once Daily (OD)" "0800 (07:30 - 09:30)"
19571577 6565986616 "Once Daily (OD)" "0800 (07:30 - 09:30)"
19571577 5631225592 "Once Daily (OD)" "0800 (07:30 - 09:30)"
19571577 1201997928 "Once Daily (OD)" "0800 (07:30 - 09:30)"
19784726 852958040 "Once Daily (OD)" "0800 (07:15 - 10:00)"
19784726 6662372896 "Once Daily (OD)" "0800 (07:15 - 10:00)"
19784726 9605828456 "Once Daily (OD)" "0800 (07:15 - 10:00)"
19784726 8519892160 "Three Times Daily (TDS)" "1000 (09:30 - 10:30),1400 (13:30 - 14:30),1700 (16:00 - 18:30)"
19784726 6307364832 "Three Times Daily (TDS)" "0800 (07:15 - 10:00),1200 (11:30 - 13:30),1700 (16:00 - 18:30)"
19784726 7033940136 "Twice Daily (BD)" "1200 (11:30 - 13:30),1700 (16:00 - 18:30)"
19784726 2655967896 "Twice Daily (BD)" "0800 (07:15 - 10:00),1700 (16:00 - 18:30)"
19784726 8671067432 "Twice Daily (BD)" "0800 (07:15 - 10:00),1700 (16:00 - 18:30)"
19997875 4997166664 "Once Daily (OD)" "Bedtime (19:30 - 21:30)"
19997875 7801449744 "Once Daily (OD)" "Bedtime (19:30 - 21:30)"
19997875 8268830256 "Once Daily (OD)" "Break (07:00 - 10:30)"
19997875 874642496 "Twice Daily (BD)" "Break (07:00 - 10:30),Bedtime (19:30 - 21:30)"
20211024 3857843448 "Once Daily (OD)" "Breakfast (07:00 - 10:00)"
20211024 4325223960 "Once Daily (OD)" "Breakfast (07:00 - 10:00)"
20211024 3269924376 "Once Daily (OD)" "Breakfast (07:00 - 10:00)"
20211024 8064268064 "Once Daily (OD)" "Breakfast (07:00 - 10:00)"
20211024 8531648576 "Once Daily (OD)" "Breakfast (07:00 - 10:00)"
20211024 2809274664 "Three Times Daily (TDS)" "Breakfast (07:00 - 10:00),Lunch (11:30 - 13:30),Night (19:00 - 22:00)"
20211024 1317567616 "Twice Daily (BD)" "1000 (09:30 - 10:30),1500 (14:30 - 15:30)"
20424173 8776529152 "Once Daily (OD)" "Breakfast (07:00 - 10:00)"
20424173 5972246080 "Once Daily (OD)" "Breakfast (07:00 - 10:00)"
20424173 6907007104 "Once Daily (OD)" "Night (19:00 - 22:00)"
20424173 5504865560 "Twice Daily (BD)" "Breakfast (07:00 - 10:00),Night (19:00 - 22:00)"
27154978 8258016400 "Once Daily (OD)" "Break (06:45 - 10:30)"
27154978 6333971132 "Once Daily (OD)" "Bedtime (19:00 - 22:00)"
27154978 3384817284 "Once Daily (OD)" "Break (06:45 - 10:30)"
27154978 4553161992 "Once Daily (OD)" "Break (06:45 - 10:30)"
27154978 9304259952 "Once Daily (OD)" "Break (06:45 - 10:30)"
27154978 7980690138 "Once Daily (OD)" "Break (06:45 - 10:30)"
27154978 578359784 "Twice Daily (BD)" "Break (06:45 - 10:30),Bedtime (19:00 - 22:00)"
27154978 2391553764 "Twice Daily (BD)" "Break (06:45 - 10:30),Dinner (16:00 - 18:15)"
27154978 2038174744 "Twice Daily (BD)" "Break (06:45 - 10:30),Bedtime (19:00 - 22:00)"
28433872 3718064736 "Once Daily (OD)" "Break (07:00 - 09:00)"
28433872 7695111912 "Once Daily (OD)" "Break (07:00 - 09:00)"
28433872 4890828832 "Once Daily (OD)" "Bedtime (19:30 - 23:30)"
28433872 368682016 "Once Daily (OD)" "Bedtime (19:30 - 23:30)"
28433872 1381162168 "Three Times Daily (TDS)" "Break (07:00 - 09:00),Lunch (11:00 - 13:00),Bedtime (19:30 - 23:30)"
28433872 9034028456 "Twice Daily (BD)" "Break (07:00 - 09:00),Bedtime (19:30 - 23:30)"
28433872 2778524468 "Twice Daily (BD)" "Break (07:00 - 09:00),Bedtime (19:30 - 23:30)"
29499617 2145341656 "Once Daily (OD)" "Dinner (16:15 - 18:30)"
29499617 9223324176 "Once Daily (OD)" "B/fast (07:00 - 10:00)"
29499617 8531042352 "Once Daily (OD)" "B/fast (07:00 - 10:00)"
29499617 2268651152 "Once Daily (OD)" "1000 (09:30 - 10:30)"
29499617 3389856712 "Once Daily (OD)" "Dinner (16:15 - 18:30)"
29499617 4791998248 "Once Daily (OD)" "B/fast (07:00 - 10:00)"
29499617 8716051576 "Three Times Daily (TDS)" "B/fast (07:00 - 10:00),Lunch (11:30 - 13:30),Dinner (16:15 - 18:30)"
29499617 5259378760 "Twice Daily (BD)" "B/fast (07:00 - 10:00),Dinner (16:15 - 18:30)"
29499617 8998422864 "Twice Daily (BD)" "B/fast (07:00 - 10:00),Dinner (16:15 - 18:30)"
29712766 4019799360 "Four Times Daily (QID)" "Breakfast (07:00 - 09:30),Lunch (11:45 - 13:45),Tea (16:15 - 18:30),Bed (19:00 - 22:00)"
29712766 512297408 "Once Daily (OD)" "Breakfast (07:00 - 09:30)"
29712766 849211320 "Once Daily (OD)" "1430 (14:00 - 15:00)"
29712766 5739638176 "Once Daily (OD)" "Breakfast (07:00 - 09:30)"
29712766 284468752 "Once Daily (OD)" "1400 (13:30 - 14:30)"
29712766 604667080 "Once Daily (OD)" "Breakfast (07:00 - 09:30)"
29712766 3876330672 "Once Daily (OD)" "Breakfast (07:00 - 09:30)"
29712766 9952277336 "Once Daily (OD)" "AC Breakfast (05:45 - 07:30)"
29712766 7147994264 "Once Daily (OD)" "Bed (19:00 - 22:00)"
29712766 4535114880 "Twice Daily (BD)" "Breakfast (07:00 - 09:30),Bed (19:00 - 22:00)"
29712766 2756560416 "Twice Daily (BD)" "Breakfast (07:00 - 09:30),Bed (19:00 - 22:00)"
29925915 7887907984 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
29925915 7420527472 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
29925915 8822669008 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
29925915 8355288496 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
29925915 663775800 "Once Daily (OD)" "Breakfast (06:30 - 09:30)"
29925915 5055209640 "Three Times Daily (TDS)" "Breakfast (06:30 - 09:30),Lunch (11:30 - 13:30),Dinner (16:00 - 18:30)"
29925915 861057704 "Three Times Daily (TDS)" "Breakfast (06:30 - 09:30),Lunch (11:30 - 13:30),Dinner (16:00 - 18:30)"
29925915 4207366376 "Twice Daily (BD)" "Breakfast (06:30 - 09:30),Dinner (16:00 - 18:30)"
29925915 3186113888 "Twice Daily (BD)" "Breakfast (06:30 - 09:30),Dinner (16:00 - 18:30)"
29925915 8056774624 "Twice Daily (BD)" "Lunch (11:30 - 13:30),Dinner (16:00 - 18:30)"
29925915 5990396968 "Twice Daily (BD)" "Breakfast (06:30 - 09:30),Dinner (16:00 - 18:30)"
end
[/CODE]


The corresponding administered dataset

clear
input double(patientid medicineid) str28 dosetime
354944 6424582976 "Breakfast (06:30 - 09:30)"
354944 6318358616 "Breakfast (06:30 - 09:30)"
354944 3886898152 "Bed (19:30 - 21:30)"
354944 8358732104 "Breakfast (06:30 - 09:30)"
354944 3501802864 "Breakfast (06:30 - 09:30)"
354944 3886898152 "Breakfast (06:30 - 09:30)"
354944 3886898152 "Lunch (11:30 - 13:30)"
994391 5575214440 "Break (07:00 - 09:30)"
994391 1183780600 "Break (07:00 - 09:30)"
994391 4922824696 "Break (07:00 - 09:30)"
994391 2770931360 "Break (07:00 - 09:30)"
994391 7912117008 "Break (07:00 - 09:30)"
994391 8379497520 "Break (07:00 - 09:30)"
994391 2770931360 "Lunch (11:30 - 13:30)"
994391 1183780600 "1700 (16:30 - 17:30)"
994391 2770931360 "1700 (16:30 - 17:30)"
10282984 8523212576 "Break (06:00 - 10:00)"
10282984 6725015184 "Break (06:00 - 10:00)"
10282984 8571697072 "Bedtime (19:00 - 22:00)"
10282984 6802274792 "Dinner (16:15 - 18:30)"
10282984 9211690944 "Break (06:00 - 10:00)"
10282984 5603293368 "Break (06:00 - 10:00)"
10282984 5603293368 "Dinner (16:15 - 18:30)"
10282984 9211690944 "Dinner (16:15 - 18:30)"
10282984 9211690944 "Lunch (11:30 - 13:30)"
10282984 7149879376 "Bedtime (19:00 - 22:00)"
10282984 8026564208 "1000 (09:30 - 10:30)"
10282984 2614886880 "Break (06:00 - 10:00)"
10282984 5689661640 "1400 (13:30 - 14:30)"
10496133 597476752 "Break (06:30 - 09:30)"
18292683 6830756504 "Break (06:00 - 10:00)"
18292683 9474945268 "Break (06:00 - 10:00)"
18292683 1085305760 "Break (06:00 - 10:00)"
18292683 6830756504 "Dinner (16:15 - 18:30)"
18292683 2478182224 "Dinner (16:15 - 18:30)"
18292683 1085305760 "Dinner (16:15 - 18:30)"
18292683 2478182224 "Break (06:00 - 10:00)"
19571577 2926156472 "0800 (07:30 - 09:30)"
19571577 6565986616 "0800 (07:30 - 09:30)"
19571577 1201997928 "0800 (07:30 - 09:30)"
19571577 5631225592 "0800 (07:30 - 09:30)"
19784726 2655967896 "0800 (07:15 - 10:00)"
19784726 9605828456 "0800 (07:15 - 10:00)"
19784726 7033940136 "1700 (16:00 - 18:30)"
19784726 2655967896 "1700 (16:00 - 18:30)"
19784726 7033940136 "1200 (11:30 - 13:30)"
19784726 6307364832 "1200 (11:30 - 13:30)"
19784726 6307364832 "1700 (16:00 - 18:30)"
19784726 8671067432 "1700 (16:00 - 18:30)"
19784726 6307364832 "0800 (07:15 - 10:00)"
19784726 852958040 "0800 (07:15 - 10:00)"
19784726 8671067432 "0800 (07:15 - 10:00)"
19784726 8519892160 "1400 (13:30 - 14:30)"
19784726 8519892160 "1000 (09:30 - 10:30)"
19784726 8519892160 "1700 (16:00 - 18:30)"
19784726 6662372896 "0800 (07:15 - 10:00)"
19997875 8268830256 "Break (07:00 - 10:30)"
19997875 7801449744 "Bedtime (19:30 - 21:30)"
19997875 874642496 "Bedtime (19:30 - 21:30)"
19997875 4997166664 "Bedtime (19:30 - 21:30)"
19997875 874642496 "Break (07:00 - 10:30)"
20211024 4325223960 "Breakfast (07:00 - 10:00)"
20211024 8064268064 "Breakfast (07:00 - 10:00)"
20211024 3269924376 "Breakfast (07:00 - 10:00)"
20211024 8531648576 "Breakfast (07:00 - 10:00)"
20211024 2809274664 "Night (19:00 - 22:00)"
20211024 2809274664 "Breakfast (07:00 - 10:00)"
20211024 3857843448 "Breakfast (07:00 - 10:00)"
20211024 2809274664 "Lunch (11:30 - 13:30)"
20211024 1317567616 "1000 (09:30 - 10:30)"
20211024 1317567616 "1500 (14:30 - 15:30)"
20424173 5504865560 "Breakfast (07:00 - 10:00)"
20424173 8776529152 "Breakfast (07:00 - 10:00)"
20424173 5972246080 "Breakfast (07:00 - 10:00)"
20424173 5504865560 "Night (19:00 - 22:00)"
20424173 6907007104 "Night (19:00 - 22:00)"
27154978 2038174744 "Bedtime (19:00 - 22:00)"
27154978 3384817284 "Break (06:45 - 10:30)"
27154978 2038174744 "Break (06:45 - 10:30)"
27154978 7980690138 "Break (06:45 - 10:30)"
27154978 2391553764 "Break (06:45 - 10:30)"
27154978 8258016400 "Break (06:45 - 10:30)"
27154978 4553161992 "Break (06:45 - 10:30)"
27154978 2391553764 "Dinner (16:00 - 18:15)"
27154978 9304259952 "Break (06:45 - 10:30)"
27154978 578359784 "Break (06:45 - 10:30)"
27154978 578359784 "Bedtime (19:00 - 22:00)"
27154978 6333971132 "Bedtime (19:00 - 22:00)"
28433872 3718064736 "Break (07:00 - 09:00)"
28433872 2778524468 "Break (07:00 - 09:00)"
28433872 7695111912 "Break (07:00 - 09:00)"
28433872 4890828832 "Bedtime (19:30 - 23:30)"
28433872 368682016 "Bedtime (19:30 - 23:30)"
28433872 2778524468 "Bedtime (19:30 - 23:30)"
28433872 9034028456 "Break (07:00 - 09:00)"
28433872 9034028456 "Bedtime (19:30 - 23:30)"
28433872 1381162168 "Break (07:00 - 09:00)"
28433872 1381162168 "Bedtime (19:30 - 23:30)"
28433872 1381162168 "Lunch (11:00 - 13:00)"
29499617 9223324176 "B/fast (07:00 - 10:00)"
29499617 8531042352 "B/fast (07:00 - 10:00)"
29499617 5259378760 "B/fast (07:00 - 10:00)"
29499617 5259378760 "Dinner (16:15 - 18:30)"
29499617 3389856712 "Dinner (16:15 - 18:30)"
29499617 2145341656 "Dinner (16:15 - 18:30)"
29499617 8998422864 "B/fast (07:00 - 10:00)"
29499617 8716051576 "B/fast (07:00 - 10:00)"
29499617 8716051576 "Dinner (16:15 - 18:30)"
29499617 8998422864 "Dinner (16:15 - 18:30)"
29499617 8716051576 "Lunch (11:30 - 13:30)"
29499617 4791998248 "B/fast (07:00 - 10:00)"
29499617 2268651152 "1000 (09:30 - 10:30)"
29712766 4535114880 "Breakfast (07:00 - 09:30)"
29712766 5739638176 "Breakfast (07:00 - 09:30)"
29712766 2756560416 "Breakfast (07:00 - 09:30)"
29712766 3876330672 "Breakfast (07:00 - 09:30)"
29712766 2756560416 "Bed (19:00 - 22:00)"
29712766 4535114880 "Bed (19:00 - 22:00)"
29712766 9952277336 "AC Breakfast (05:45 - 07:30)"
29712766 604667080 "Breakfast (07:00 - 09:30)"
29712766 512297408 "Breakfast (07:00 - 09:30)"
29712766 7147994264 "Bed (19:00 - 22:00)"
29712766 284468752 "1400 (13:30 - 14:30)"
29712766 4019799360 "Tea (16:15 - 18:30)"
29712766 4019799360 "Lunch (11:45 - 13:45)"
29712766 4019799360 "Breakfast (07:00 - 09:30)"
29712766 4019799360 "Bed (19:00 - 22:00)"
29712766 849211320 "1430 (14:00 - 15:00)"
29925915 3186113888 "Dinner (16:00 - 18:30)"
29925915 7887907984 "Breakfast (06:30 - 09:30)"
29925915 7420527472 "Breakfast (06:30 - 09:30)"
29925915 8355288496 "Breakfast (06:30 - 09:30)"
29925915 8822669008 "Breakfast (06:30 - 09:30)"
29925915 3186113888 "Breakfast (06:30 - 09:30)"
29925915 663775800 "Breakfast (06:30 - 09:30)"
29925915 861057704 "Breakfast (06:30 - 09:30)"
29925915 4207366376 "Breakfast (06:30 - 09:30)"
29925915 8056774624 "Dinner (16:00 - 18:30)"
29925915 861057704 "Dinner (16:00 - 18:30)"
29925915 4207366376 "Dinner (16:00 - 18:30)"
29925915 861057704 "Lunch (11:30 - 13:30)"
29925915 8056774624 "Lunch (11:30 - 13:30)"
29925915 5055209640 "Dinner (16:00 - 18:30)"
29925915 5055209640 "Breakfast (06:30 - 09:30)"
29925915 5055209640 "Lunch (11:30 - 13:30)"
29925915 5990396968 "Dinner (16:00 - 18:30)"
29925915 5990396968 "Breakfast (06:30 - 09:30)"
end
[/CODE]

[Clyde Schechter]

use `ordered', clear
split dosetime, parse(",") gen(dosetime)
drop dosetime
reshape long dosetime, i(patientid medicineid)
drop if missing(dosetime)
merge 1:1 patientid medicineid dosetime using `administered'

Note: I saved the first data set in a tempfile called `ordered', and the second in a tempfile called `administered'. So just replace the references to those tempfile names by the actual names of the data sets.

In the example data, this produces a perfect pairing up of ordered and administered, with everything having an exact match..

[Tabitha Green]

Thank you. That code worked well in the whole dataset. I really appreciate your time and help.