Coding medication data

[Katie Holzer]

Hello!

I am using medication data for thousands of observations. I have calculated the total daily dose for each medication. I would like to use the total_dose variable to report for the most frequent dose for each medication in the list (e.g., most frequently prescribed dose for bupropion). The data are currently in long format and many observations have more than one medication, additionally, participants may be prescribed two different doses of the same medication. For participants prescribed two different doses of the same medication, I am only interested in the combined dose as the total daily dose.. Please see data example below. I appreciate any help with coding to find the modal dose for each med.

[CODE]

* Example generated by -dataex-. To install: ssc install dataex
clear
input int study_id str15 med str5 total_dose
  7 "citalopram"     "40" 
 35 "venlafaxine"    "75" 
 48 "sertraline"     "200"
 54 "bupropion"      "300"
 54 "citalopram"     "10" 
 54 "escitalopram"   "30" 
 61 "duloxetine"     "60" 
 71 "escitalopram"   "20" 
 74 "mirtazapine"    "15" 
 78 "fluoxetine"     "40" 
 87 "venlafaxine"    "150"
 89 "sertraline"     "25" 
 91 "escitalopram"   "10" 
 93 "vortioxetine"   "10" 
 97 "duloxetine"     "120"

[Clyde Schechter]

Here's a start in the right direction:

 // MAKE TOTAL_DOSE VARIABLE NUMERIC
 destring total_dose, replace
 
/*
 For participants prescribed two different doses of the same medication,
 I am only interested in the combined dose as the total daily dose.
*/
collapse (sum) total_dose, by(study_id med)

//  IDENTIFY FREQUENCY OF EACH DOSE FOR EACH MED
contract med total_dose
by med (_freq), sort: keep if _n == _N
drop _freq

The problem is that the question is ill-posed. You have not stated what to do in the event that for some medicine(s) there are two (or more) doses tied for "most frequent." The above code will select one of them, at random, and irreproducibly. If you can spell out a rule for how to break such ties based on something in the data, then the code can be modified accordingly.