Help making my code more efficient

[Tabitha Green]

I have a dataset of participants who are dispensed medicines on a particular date (identified by variable supply date). I want to be able to calculate the number of active ingredients that they use in a month. I need to also be able to calculate how many medicines they are adherent to the medicine by having it dispensed in two different time periods. I need to be able to account for people who use two or more products containing a single active ingredient so I don't double count them.


The code
The code I've written works and gets me the outcome I want, but it's long winded and in-elegant. I've used amlodipine as the example here, but I need to do this again for other medicines, and I don't want to do this in such a long format for 100 different preparations. I've had to put it in such long form so that I don't end up with the two ATC codes being equal and the code simply telling me that they remain on the same preparation. How do I write this code so that I only need to do it once instead of having such a long winded way of running through the variables removing the ATC code one at a time?

local amlodipinecombos c09bb04 c09db01 c09db02 c09db04 c09dx01 c09dx03 c10bx03
foreach j of local amlodipinecombos {
forvalues i=0(1)5 {
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c08ca01") & ATCcode[[_n-`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c08ca01") & ATCcode[[_n+`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n-`i']] == "c08ca01"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n+`i']] == "c08ca01"
}
}

local amlodipinecombos c09db01 c09db02 c09db04 c09dx01 c09dx03 c10bx03
foreach j of local amlodipinecombos {
forvalues i=0(1)5 {
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09bb04") & ATCcode[[_n-`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09bb04") & ATCcode[[_n+`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n-`i']] == "c09bb04"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n+`i']] == "c09bb04"
}
}


local amlodipinecombos c09db02 c09db04 c09dx01 c09dx03 c10bx03
foreach j of local amlodipinecombos {
forvalues i=0(1)5 {
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09db01") & ATCcode[[_n-`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09db01") & ATCcode[[_n+`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n-`i']] == "c09db01"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n+`i']] == "c09db01"
}
}

local amlodipinecombos c09db04 c09dx01 c09dx03 c10bx03
foreach j of local amlodipinecombos {
forvalues i=0(1)5 {
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09db02") & ATCcode[[_n-`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09db02") & ATCcode[[_n+`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n-`i']] == "c09db02"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n+`i']] == "c09db02"
}
}


local amlodipinecombos c09dx01 c09dx03 c10bx03
foreach j of local amlodipinecombos {
forvalues i=0(1)5 {
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09db04") & ATCcode[[_n-`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09db04") & ATCcode[[_n+`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n-`i']] == "c09db04"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n+`i']] == "c09db04"
}
}


local amlodipinecombos c09dx03 c10bx03
foreach j of local amlodipinecombos {
forvalues i=0(1)5 {
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09dx01") & ATCcode[[_n-`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09dx01") & ATCcode[[_n+`i']] == "`j'"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n-`i']] == "c09dx01"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`j'") & ATCcode[[_n+`i']] == "c09dx01"
}
}


forvalues i=0(1)5 {
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09dx03") & ATCcode[[_n-`i']] == "c10bx03"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c09dx03") & ATCcode[[_n+`i']] == "c10bx03"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c10bx03") & ATCcode[[_n-`i']] == "c09dx03"
bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "c10bx03") & ATCcode[[_n+`i']] == "c09dx03"
}

Describing the dataset

The variable ATC code uniquely identifies each medicine, while the drug_name variable names the variable (so each ATCcode value uniquely matches one drug_name value). I have eight different products that contain amlodipine that are identified by the ATC codes c09bb04 c09db01 c09db02 c09db04 c09dx01 c09dx03 c10bx03 c08ca01.

I have created an active ingredients variable that tells me how many active ingredients there are in each product (i.e. amlodipine c08ca01 has only 1 active ingredient while perindopril + amlodipine c09bb04 and telmisartan + amlodipine c09db04 have two active ingredients).

Here is a sample of my data:

input double participant_id int supply_date str45 drug_name str7 ATCcode byte activeingredients
186331 22528 "amlodipine" "c08ca01" 1
461681 22415 "amlodipine" "c08ca01" 1
555339 22561 "amlodipine" "c08ca01" 1
568787 22538 "amlodipine" "c08ca01" 1
886834 22351 "amlodipine" "c08ca01" 1
895276 22517 "amlodipine" "c08ca01" 1
898571 22521 "amlodipine" "c08ca01" 1
1002382 22300 "amlodipine" "c08ca01" 1
1002382 22518 "perindopril + amlodipine" "c09bb04" 2
1029599 22434 "telmisartan + amlodipine" "c09db04" 2
1034746 22454 "amlodipine" "c08ca01" 1
1058374 22506 "perindopril + amlodipine" "c09bb04" 2
1109334 22556 "perindopril + amlodipine" "c09bb04" 2
1260694 22380 "perindopril + amlodipine" "c09bb04" 2
1313392 22530 "amlodipine" "c08ca01" 1
1330429 22409 "amlodipine + atorvastatin" "c10bx03" 2
1498674 22507 "amlodipine" "c08ca01" 1
1624554 22297 "telmisartan + amlodipine" "c09db04" 2
1818279 22454 "amlodipine" "c08ca01" 1
2027189 22266 "perindopril + amlodipine" "c09bb04" 2
2051112 22378 "perindopril + amlodipine" "c09bb04" 2
2113962 22546 "amlodipine + atorvastatin" "c10bx03" 2
2149775 22587 "amlodipine" "c08ca01" 1
2221068 22416 "amlodipine" "c08ca01" 1
2236548 22548 "amlodipine" "c08ca01" 1
2387755 22395 "amlodipine" "c08ca01" 1
2550852 22489 "amlodipine" "c08ca01" 1
2877316 22527 "perindopril + amlodipine" "c09bb04" 2
3011485 22499 "amlodipine" "c08ca01" 1
3099995 22553 "amlodipine" "c08ca01" 1
3293720 22448 "amlodipine" "c08ca01" 1
3519668 22497 "amlodipine + valsartan" "c09db01" 2
3546885 22521 "amlodipine" "c08ca01" 1
3608178 22383 "amlodipine + atorvastatin" "c10bx03" 2
3659138 22415 "amlodipine" "c08ca01" 1
4024556 22306 "perindopril + amlodipine" "c09bb04" 2
4101175 22572 "amlodipine + valsartan" "c09db01" 2
4216723 22391 "amlodipine" "c08ca01" 1
4216723 22564 "amlodipine + valsartan" "c09db01" 2
4271284 22307 "amlodipine + atorvastatin" "c10bx03" 2
4289779 22467 "olmesartan + amlodipine" "c09db02" 2
4531181 22573 "perindopril + amlodipine" "c09bb04" 2
4668914 22288 "olmesartan + amlodipine" "c09db02" 2
4922079 22340 "olmesartan + amlodipine" "c09db02" 2
4922079 22365 "olmesartan + amlodipine + hydrochlorothiazide" "c09dx03" 3
5188987 22351 "amlodipine + valsartan + hydrochlorothiazide" "c09dx01" 3
5428704 22385 "perindopril + amlodipine" "c09bb04" 2
5454184 22539 "perindopril + amlodipine" "c09bb04" 2
5642761 22227 "amlodipine" "c08ca01" 1
5647909 22299 "amlodipine" "c08ca01" 1
5678574 22429 "amlodipine + atorvastatin" "c10bx03" 2
5712496 22375 "amlodipine" "c08ca01" 1
5731271 22576 "amlodipine + valsartan + hydrochlorothiazide" "c09dx01" 3
5731271 22344 "telmisartan + amlodipine" "c09db04" 2
5765347 22332 "amlodipine" "c08ca01" 1
5807711 22277 "perindopril + amlodipine" "c09bb04" 2
5836639 22251 "telmisartan + amlodipine" "c09db04" 2
5838376 22329 "amlodipine + valsartan" "c09db01" 2
6032101 22417 "amlodipine + valsartan" "c09db01" 2
6073062 22613 "perindopril + amlodipine" "c09bb04" 2
6491177 22473 "telmisartan + amlodipine" "c09db04" 2
6645832 22342 "amlodipine" "c08ca01" 1
6645832 22519 "amlodipine + valsartan" "c09db01" 2
6650979 22351 "olmesartan + amlodipine" "c09db02" 2
6662870 22539 "amlodipine" "c08ca01" 1
6681465 22500 "amlodipine + valsartan + hydrochlorothiazide" "c09dx01" 3
6786860 22351 "perindopril + amlodipine" "c09bb04" 2
6865037 22531 "olmesartan + amlodipine + hydrochlorothiazide" "c09dx03" 3
6865037 22291 "telmisartan + amlodipine" "c09db04" 2
6868485 22495 "amlodipine" "c08ca01" 1
6975437 22397 "telmisartan + amlodipine" "c09db04" 2
7080985 22339 "amlodipine" "c08ca01" 1
7322259 22611 "perindopril + amlodipine" "c09bb04" 2
7463287 22269 "amlodipine + valsartan" "c09db01" 2
7616385 22357 "amlodipine" "c08ca01" 1
7791488 22284 "amlodipine" "c08ca01" 1
7791488 22460 "perindopril + amlodipine" "c09bb04" 2
7818705 22355 "amlodipine + valsartan" "c09db01" 2
7951137 22558 "amlodipine" "c08ca01" 1
8056685 22576 "amlodipine" "c08ca01" 1
8309850 22458 "amlodipine" "c08ca01" 1
8474800 22371 "amlodipine" "c08ca01" 1
8634603 22461 "olmesartan + amlodipine + hydrochlorothiazide" "c09dx03" 3
8671973 22283 "perindopril + amlodipine" "c09bb04" 2
8680568 22560 "amlodipine + valsartan + hydrochlorothiazide" "c09dx01" 3
8680568 22294 "perindopril + amlodipine" "c09bb04" 2
8753445 22503 "telmisartan + amlodipine" "c09db04" 2
8775630 22342 "telmisartan + amlodipine" "c09db04" 2
8954208 22416 "perindopril + amlodipine" "c09bb04" 2
8974540 22410 "perindopril + amlodipine" "c09bb04" 2
9047570 22567 "perindopril + amlodipine" "c09bb04" 2
9090088 22316 "amlodipine + atorvastatin" "c10bx03" 2
9168265 22501 "amlodipine" "c08ca01" 1
9356996 22566 "amlodipine" "c08ca01" 1
9397803 22448 "amlodipine + atorvastatin" "c10bx03" 2
9418136 22466 "amlodipine + atorvastatin" "c10bx03" 2
9712261 22424 "amlodipine" "c08ca01" 1
9727715 22480 "telmisartan + amlodipine" "c09db04" 2
9836251 22480 "amlodipine" "c08ca01" 1
9889101 22404 "amlodipine + valsartan + hydrochlorothiazide" "c09dx01" 3
end
format %d supply_date
[/CODE]

[Daniel Schaefer]

I'm getting variable pat_id not found when I run this. did participant_id get renamed at some point?

[Daniel Schaefer]

Okay, so here is some technique for combinatorics with subsets of two that should make your code more compact:

local amlodipinecombos "c08ca01 c09bb04 c09db01 c09db02 c09db04 c09dx01 c09dx03 c10bx03"
forv i = 1/7{
    local first_code = word("`amlodipinecombos'", `i')
    local j_start = `i' + 1
    forv j = `j_start'/8{
        local second_code = word("`amlodipinecombos'", `j')
        di "`first_code' with `second_code'"
        *forv k = 0(1)5 {
        *    bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`first_code'") & ATCcode[[_n-`k']] == "`second_code'"
        *    bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`first_code'") & ATCcode[[_n+`k']] == "`second_code'"
        *    bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`second_code'") & ATCcode[[_n-`k']] == "`first_code'"
        *    bysort pat_id: replace switch_combination_single = 1 if (ATCcode == "`second_code'") & ATCcode[[_n+`k']] == "`first_code'"
        *}
    }
    di "NEXT FIRST CODE OR END"
}

Still can't run this though obviously without commenting out some of the lines specific to your implementation. It looks like you also don't show us how switch_combination_single is initialized.

Edit: I've not used it myself, but I've been told tuples on SSC makes this kind of thing even easier.

[Tabitha Green]

Thank you so much for such a prompt reply.

I just created the switch variable as a missing variable prior to running the code.
"

gen switch_combination_single = .

I've just run that code (removing the asterixes) and it's worked beautifully. Thank you!!!

c08ca01 with c09bb04
c08ca01 with c09db01
c08ca01 with c09db02
c08ca01 with c09db04
c08ca01 with c09dx01
c08ca01 with c09dx03
c08ca01 with c10bx03
NEXT FIRST CODE OR END
c09bb04 with c09db01
c09bb04 with c09db02
c09bb04 with c09db04
c09bb04 with c09dx01
c09bb04 with c09dx03
c09bb04 with c10bx03
NEXT FIRST CODE OR END
c09db01 with c09db02
c09db01 with c09db04
c09db01 with c09dx01
c09db01 with c09dx03
c09db01 with c10bx03
NEXT FIRST CODE OR END
c09db02 with c09db04
c09db02 with c09dx01
c09db02 with c09dx03
c09db02 with c10bx03
NEXT FIRST CODE OR END
c09db04 with c09dx01
c09db04 with c09dx03
c09db04 with c10bx03
NEXT FIRST CODE OR END
c09dx01 with c09dx03
c09dx01 with c10bx03
NEXT FIRST CODE OR END
c09dx03 with c10bx03
NEXT FIRST CODE OR END

[Tabitha Green]

I'm getting variable pat_id not found when I run this. did participant_id get renamed at some point?

Yes that's correct. pat_id was renamed participant_id

[Tabitha Green]

Hi Daniel, your code above has been very helpful for capturing switches in medicines that occur at any time, thanks again. I do have a follow-up query, I now need to identify if the medicine was switched between two quarters (i.e. quarter 2 [April-June], and quarter 4 [October-December]). The code I am trying to run is:

local amlodipinecombo c09dx03 c09dx01 c09db04 c09db02 c09db01 c10bx03 c08ca01 c09bb04
    forvalue i= 1/7 {
    local first_code = word("`amlodipinecombo'", `i')
    local j_start = `i' + 1
    forv j = `j_start'/8{
        local second_code = word("`amlodipinecombo'", `j')
        di "`first_code' with `second_code'"
         forv k = 0(1)5 {
             bysort pat_id: replace switch_between = 1 if ((ATCcode == "`first_code'") & dispensed_q2 == 1)  & ((ATCcode[[_n-`k']] == "`second_code'" & (dispensed_q4 == 1)))
             bysort pat_id: replace switch_between = 1 if ((ATCcode == "`first_code'") & dispensed_q2 == 1)  & ((ATCcode[[_n+`k']] == "`second_code'" & (dispensed_q4 == 1)))
             bysort pat_id: replace switch_between = 1 if ((ATCcode == "`second_code'") & dispensed_q2 == 1) & ((ATCcode[[_n-`k']] == "`first_code'" & (dispensed_q4 == 1)))
             bysort pat_id: replace switch_between = 1 if ((ATCcode == "`second_code'") & dispensed_q2 == 1) & ((ATCcode[[_n+`k']] == "`first_code'" & (dispensed_q4 == 1)))
         }
    }
    di "NEXT FIRST CODE OR END"
}

Having run some checks I can see that the first section of the code is capturing medicines dispensed_q2==1, no problem, but I can't work out how to limit the second part of the code to medicines dispensed_q4 only, rather it is capturing the second medicine if dispensed at any time. I have tried every bracket combination I can think of but I am afraid I have reached my limit.

Any insight would be greatly appreciated.

Please see the updated data extract in the next post:

[Tabitha Green]

* Example generated by -dataex-. For more info, type help dataex
clear
input float pat_id str7 ATCcode strL drug_name float(dispensed_q2 dispensed_q4 switch_between)
 1 "c09bb04" "perindopril + amlodipine"                      1 . .
 2 "c08ca01" "amlodipine"                                    . 1 .
 3 "c08ca01" "amlodipine"                                    . 1 .
 4 "c08ca01" "amlodipine"                                    . 1 .
 5 "c08ca01" "amlodipine"                                    . 1 .
 6 "c08ca01" "amlodipine"                                    . 1 .
 7 "c08ca01" "amlodipine"                                    . 1 .
 8 "c08ca01" "amlodipine"                                    . 1 .
 9 "c09bb04" "perindopril + amlodipine"                      1 . .
10 "c08ca01" "amlodipine"                                    1 . .
10 "c09db01" "amlodipine + valsartan"                        . 1 .
11 "c09db01" "amlodipine + valsartan"                        . 1 .
12 "c08ca01" "amlodipine"                                    . 1 .
13 "c09db04" "telmisartan + amlodipine"                      1 . .
14 "c09bb04" "perindopril + amlodipine"                      . 1 .
15 "c08ca01" "amlodipine"                                    1 . .
16 "c09dx03" "olmesartan + amlodipine + hydrochlorothiazide" . 1 .
17 "c09dx01" "amlodipine + valsartan + hydrochlorothiazide"  . 1 .
18 "c09db02" "olmesartan + amlodipine"                       1 . .
18 "c09dx03" "olmesartan + amlodipine + hydrochlorothiazide" . 1 .
19 "c09db04" "telmisartan + amlodipine"                      . 1 .
20 "c09bb04" "perindopril + amlodipine"                      . 1 .
21 "c08ca01" "amlodipine"                                    . 1 .
21 "c09bb04" "perindopril + amlodipine"                      . 1 .
22 "c10bx03" "amlodipine + atorvastatin"                     . 1 .
23 "c08ca01" "amlodipine"                                    1 . .
24 "c08ca01" "amlodipine"                                    1 . .
25 "c08ca01" "amlodipine"                                    . 1 .
26 "c09dx01" "amlodipine + valsartan + hydrochlorothiazide"  . 1 .
27 "c08ca01" "amlodipine"                                    . 1 .
28 "c09bb04" "perindopril + amlodipine"                      . 1 .
29 "c09bb04" "perindopril + amlodipine"                      . 1 .
30 "c09bb04" "perindopril + amlodipine"                      . 1 .
31 "c09db01" "amlodipine + valsartan"                        . 1 .
32 "c08ca01" "amlodipine"                                    . 1 .
33 "c09dx01" "amlodipine + valsartan + hydrochlorothiazide"  . 1 .
34 "c09bb04" "perindopril + amlodipine"                      . 1 .
35 "c08ca01" "amlodipine"                                    . 1 .
36 "c09bb04" "perindopril + amlodipine"                      . 1 .
37 "c08ca01" "amlodipine"                                    . 1 .
38 "c08ca01" "amlodipine"                                    . 1 .
39 "c09bb04" "perindopril + amlodipine"                      . 1 .
40 "c09db02" "olmesartan + amlodipine"                       . 1 .
41 "c09db01" "amlodipine + valsartan"                        . 1 .
42 "c08ca01" "amlodipine"                                    . 1 .
43 "c08ca01" "amlodipine"                                    1 . .
44 "c08ca01" "amlodipine"                                    1 . .
45 "c08ca01" "amlodipine"                                    . 1 .
46 "c08ca01" "amlodipine"                                    1 . .
47 "c08ca01" "amlodipine"                                    . 1 .
47 "c09db01" "amlodipine + valsartan"                        . 1 .
48 "c10bx03" "amlodipine + atorvastatin"                     . 1 .
49 "c08ca01" "amlodipine"                                    . 1 .
50 "c08ca01" "amlodipine"                                    . 1 .
51 "c08ca01" "amlodipine"                                    1 . .
52 "c09bb04" "perindopril + amlodipine"                      1 . .
53 "c09db02" "olmesartan + amlodipine"                       . 1 .
53 "c10bx03" "amlodipine + atorvastatin"                     . 1 .
54 "c08ca01" "amlodipine"                                    . 1 .
55 "c08ca01" "amlodipine"                                    1 . .
56 "c08ca01" "amlodipine"                                    . 1 .
57 "c08ca01" "amlodipine"                                    1 . .
58 "c08ca01" "amlodipine"                                    1 . .
59 "c08ca01" "amlodipine"                                    . 1 .
60 "c08ca01" "amlodipine"                                    . 1 .
61 "c09db04" "telmisartan + amlodipine"                      . 1 .
62 "c09bb04" "perindopril + amlodipine"                      1 . .
63 "c08ca01" "amlodipine"                                    . 1 .
64 "c08ca01" "amlodipine"                                    . 1 .
65 "c09db02" "olmesartan + amlodipine"                       . 1 .
66 "c08ca01" "amlodipine"                                    1 . .
67 "c08ca01" "amlodipine"                                    . 1 .
68 "c09bb04" "perindopril + amlodipine"                      1 . .
69 "c08ca01" "amlodipine"                                    1 . .
70 "c09bb04" "perindopril + amlodipine"                      . 1 .
71 "c08ca01" "amlodipine"                                    1 . .
72 "c09bb04" "perindopril + amlodipine"                      1 . .
73 "c09db01" "amlodipine + valsartan"                        . 1 .
74 "c08ca01" "amlodipine"                                    . 1 .
75 "c09bb04" "perindopril + amlodipine"                      . 1 .
76 "c09dx03" "olmesartan + amlodipine + hydrochlorothiazide" 1 . .
77 "c09db04" "telmisartan + amlodipine"                      . 1 .
78 "c09dx01" "amlodipine + valsartan + hydrochlorothiazide"  1 . .
79 "c09bb04" "perindopril + amlodipine"                      . 1 .
80 "c09db04" "telmisartan + amlodipine"                      . 1 .
81 "c09db01" "amlodipine + valsartan"                        1 . .
82 "c09bb04" "perindopril + amlodipine"                      . 1 .
83 "c08ca01" "amlodipine"                                    . 1 .
84 "c08ca01" "amlodipine"                                    . 1 .
85 "c09dx01" "amlodipine + valsartan + hydrochlorothiazide"  . 1 .
86 "c09dx01" "amlodipine + valsartan + hydrochlorothiazide"  1 . .
87 "c08ca01" "amlodipine"                                    . 1 .
88 "c10bx03" "amlodipine + atorvastatin"                     . 1 .
89 "c09db04" "telmisartan + amlodipine"                      . 1 .
90 "c08ca01" "amlodipine"                                    . 1 .
91 "c09dx03" "olmesartan + amlodipine + hydrochlorothiazide" . 1 .
92 "c08ca01" "amlodipine"                                    1 . .
92 "c09db01" "amlodipine + valsartan"                        . 1 .
93 "c10bx03" "amlodipine + atorvastatin"                     . 1 .
94 "c09db04" "telmisartan + amlodipine"                      . 1 .
end

[Daniel Schaefer]

Hi Tabitha,

Thanks for the data example and updated code. I think this is what you're looking for:

local amlodipinecombo c09dx03 c09dx01 c09db04 c09db02 c09db01 c10bx03 c08ca01 c09bb04
forvalue i = 1/8 {
    local first_code = word("`amlodipinecombo'", `i')
    forv j = 1/8{
        local second_code = word("`amlodipinecombo'", `j')
        if `i' != `j' {
            di "`first_code' with `second_code'"
            forv k = 1(1)5 {
                bysort pat_id: replace switch_between = 1 if ((dispensed_q2 == 1 & dispensed_q4[_n+`k'] == 1) ///
                                                        | (dispensed_q4 == 1 & dispensed_q2[_n-`k'] == 1)) ///
                                                      & ((ATCcode == "`first_code'" & ATCcode[_n-`k'] == "`second_code'") ///
                                                        | (ATCcode == "`first_code'" & ATCcode[_n+`k'] == "`second_code'"))
             }
        }
    }
}

I switched to permutations of the amlodipinecombo codes rather than combinations as a simplification step. Also, note that when k == 0, the ATCcodes are the same, so although k=0 wasn't doing any harm, we can safely leave it out.

[Daniel Schaefer]

I notice in your example data, there is never a case where q2 comes after q4 within a single pat_id. Basically, a situation like this based on your data example:

clear
input float pat_id str7 ATCcode strL drug_name float(dispensed_q2 dispensed_q4 switch_between)
[...]
95 "c10bx03" "amlodipine + atorvastatin" . 1 .
95 "c09db04" "telmisartan + amlodipine" 1 . .
end

If you need to handle a case like this, something a little more complicated may be called for:

local amlodipinecombo c09dx03 c09dx01 c09db04 c09db02 c09db01 c10bx03 c08ca01 c09bb04
forvalue i = 1/8 {
    local first_code = word("`amlodipinecombo'", `i')
    forv j = 1/8{
        local second_code = word("`amlodipinecombo'", `j')
        if `i' != `j' {
            di "`first_code' with `second_code'"
            forv k = 1(1)5 {
                bysort pat_id: replace switch_between = 1 if ((dispensed_q2 == 1 & dispensed_q4[_n+`k'] == 1) ///
                                                        | (dispensed_q4 == 1 & dispensed_q2[_n-`k'] == 1) ///
                                                        | (dispensed_q2 == 1 & dispensed_q4[_n-`k'] == 1) ///
                                                        | (dispensed_q4 == 1 & dispensed_q2[_n+`k'] == 1)) ///
                                                      & ((ATCcode == "`first_code'" & ATCcode[_n-`k'] == "`second_code'") ///
                                                        | (ATCcode == "`first_code'" & ATCcode[_n+`k'] == "`second_code'"))
             }
        }
    }
}

[Tabitha Green]

Hi Daniel,

This new code, in addition to being much shorter, has worked perfectly! I've been working on this for weeks and you've managed to solve it in minutes, I am so grateful, thank you so much!!

[Tabitha Green]

Hi Daniel,

I just saw your follow up post, very observant, but yes, there are instances in the full dataset where q2 comes after q4. I've run the updated code in the complete dataset, it has captured an additional 2,771 switches!

[Tabitha Green]

Hi Daniel,
I’ve encountered another issue, this time when trying to capture switches within a single quarter, i.e. a person with amlodipine, and amlodipine + valsartan both dispensed in Q2. Up until now, I have been testing my code in a subset of data that just includes amlodipine and its combinations. The code I’ve written (inspired by your switch_between code) works in the amlodipine subset. However, when tested it in a random sample that includes other medicines, the code *I think* is capturing the medicine above and/or below an amlodipine medicine dispensed in Q2, and maybe some others.
Using the extract above the following code appears to work. Using the new extract, including other medicines (see below), I am getting a lot of false positives.

gen switch_within = .
local amlodipinecombo c09dx03 c09dx01 c09db04 c09db02 c09db01 c10bx03 c08ca01 c09bb04
forvalue i = 1/8 {
    local first_code = word("`amlodipinecombo'", `i')
    forv j = 1/8{
        local second_code = word("`amlodipinecombo'", `j')
        if `i' != `j' {
            di "`first_code' with `second_code'"
            forv k = 1(1)5 {
                bysort pat_id: replace switch_within = 1 if (dispensed_q2 == 1 & dispensed_q2[_n-`k'] == 1) | (dispensed_q2 == 1 & dispensed_q2[_n+`k'] == 1) & ((ATCcode == "`first_code'" & ATCcode[_n-`k'] == "`second_code'") | (ATCcode == "`first_code'" & ATCcode[_n+`k'] == "`second_code'"))
             }
        }
    }
}

You have been incredibly helpful with your advice so far, if you wouldn’t mind having another look, I have no doubt you’ll have a simple explanation for me.

[Tabitha Green]

Data extract including amlodipine (and combinations) amoxicillin (and combinations), and atorvastatin (and combinations)

* Example generated by -dataex-. For more info, type help dataex
clear
input float pat_id str7 ATCcode strL drug_name float(dispensed_q2 dispensed_q4 switch_within)
 1 "j01cr02" "amoxicillin + clavulanic acid"                . 1 .
 2 "j01ca04" "amoxicillin"                                  1 . .
 3 "j01cr02" "amoxicillin + clavulanic acid"                . . .
 4 "c10aa05" "atorvastatin"                                 1 . .
 5 "j01ca04" "amoxicillin"                                  1 . .
 6 "j01cr02" "amoxicillin + clavulanic acid"                . . .
 7 "j01cr02" "amoxicillin + clavulanic acid"                . . .
 8 "j01cr02" "amoxicillin + clavulanic acid"                1 . .
 9 "j01ca04" "amoxicillin"                                  1 . .
10 "j01cr02" "amoxicillin + clavulanic acid"                1 . .
11 "j01cr02" "amoxicillin + clavulanic acid"                . . .
12 "j01ca04" "amoxicillin"                                  . . .
13 "j01ca04" "amoxicillin"                                  . . .
14 "c09db01" "amlodipine + valsartan"                       1 1 .
14 "j01cr02" "amoxicillin + clavulanic acid"                . 1 .
15 "j01cr02" "amoxicillin + clavulanic acid"                1 . .
16 "c09bb04" "perindopril + amlodipine"                     1 . .
17 "j01cr02" "amoxicillin + clavulanic acid"                . . .
18 "j01ca04" "amoxicillin"                                  1 . .
18 "j01cr02" "amoxicillin + clavulanic acid"                . 1 .
19 "j01ca04" "amoxicillin"                                  . 1 .
20 "j01ca04" "amoxicillin"                                  . . .
21 "c10aa05" "atorvastatin"                                 1 1 .
22 "j01ca04" "amoxicillin"                                  . . .
23 "c09db04" "telmisartan + amlodipine"                     1 1 .
23 "j01ca04" "amoxicillin"                                  . . .
24 "c10aa05" "atorvastatin"                                 . . .
25 "j01ca04" "amoxicillin"                                  . 1 .
26 "j01ca04" "amoxicillin"                                  . . .
27 "j01ca04" "amoxicillin"                                  . 1 .
27 "j01cr02" "amoxicillin + clavulanic acid"                . 1 .
28 "j01ca04" "amoxicillin"                                  . . .
29 "c10bx03" "amlodipine + atorvastatin"                    1 1 .
29 "j01ca04" "amoxicillin"                                  . . .
30 "c10aa05" "atorvastatin"                                 1 1 .
30 "j01ca04" "amoxicillin"                                  1 . 1
31 "j01ca04" "amoxicillin"                                  1 . .
32 "c10aa05" "atorvastatin"                                 1 1 .
33 "j01ca04" "amoxicillin"                                  1 . .
34 "c08ca01" "amlodipine"                                   1 1 .
34 "c10aa05" "atorvastatin"                                 1 1 1
35 "j01ca04" "amoxicillin"                                  . 1 .
36 "c10aa05" "atorvastatin"                                 1 1 .
37 "j01ca04" "amoxicillin"                                  . . .
38 "c10aa05" "atorvastatin"                                 1 . .
38 "c10ba05" "ezetimibe + atorvastatin"                     1 1 1
39 "c09db01" "amlodipine + valsartan"                       1 1 .
40 "j01cr02" "amoxicillin + clavulanic acid"                . . .
41 "c10aa05" "atorvastatin"                                 1 1 .
42 "c10aa05" "atorvastatin"                                 1 1 .
42 "j01ca04" "amoxicillin"                                  1 . 1
42 "j01cr02" "amoxicillin + clavulanic acid"                . 1 .
43 "j01ca04" "amoxicillin"                                  . . .
44 "j01ca04" "amoxicillin"                                  1 . .
45 "j01cr02" "amoxicillin + clavulanic acid"                1 . .
46 "j01ca04" "amoxicillin"                                  1 . .
47 "c09bb04" "perindopril + amlodipine"                     1 1 .
48 "j01cr02" "amoxicillin + clavulanic acid"                . . .
49 "j01cr02" "amoxicillin + clavulanic acid"                . . .
50 "j01ca04" "amoxicillin"                                  . . .
51 "j01ca04" "amoxicillin"                                  . . .
52 "j01ca04" "amoxicillin"                                  . 1 .
53 "j01ca04" "amoxicillin"                                  . . .
54 "j01ca04" "amoxicillin"                                  1 1 .
55 "c08ca01" "amlodipine"                                   1 1 .
55 "j01cr02" "amoxicillin + clavulanic acid"                1 . 1
56 "c08ca01" "amlodipine"                                   1 1 .
57 "j01ca04" "amoxicillin"                                  . . .
58 "j01ca04" "amoxicillin"                                  1 . .
59 "j01ca04" "amoxicillin"                                  1 . .
60 "j01ca04" "amoxicillin"                                  . . .
61 "c09bb04" "perindopril + amlodipine"                     1 1 .
61 "c10aa05" "atorvastatin"                                 1 1 1
62 "c09bb04" "perindopril + amlodipine"                     1 1 .
63 "j01ca04" "amoxicillin"                                  . 1 .
63 "j01cr02" "amoxicillin + clavulanic acid"                . . .
64 "c09db04" "telmisartan + amlodipine"                     1 1 .
65 "c10aa05" "atorvastatin"                                 1 1 .
65 "j01ca04" "amoxicillin"                                  . . .
66 "c08ca01" "amlodipine"                                   . 1 .
67 "j01ca04" "amoxicillin"                                  . . .
68 "j01ca04" "amoxicillin"                                  . . .
69 "j01ca04" "amoxicillin"                                  . 1 .
70 "c09bb04" "perindopril + amlodipine"                     1 1 .
71 "j01ca04" "amoxicillin"                                  1 . .
72 "j01cr02" "amoxicillin + clavulanic acid"                . . .
73 "j01ca04" "amoxicillin"                                  1 . .
73 "j01cr02" "amoxicillin + clavulanic acid"                1 . 1
74 "j01cr02" "amoxicillin + clavulanic acid"                . 1 .
75 "c10aa05" "atorvastatin"                                 1 1 .
76 "c09dx01" "amlodipine + valsartan + hydrochlorothiazide" 1 1 .
77 "c10aa05" "atorvastatin"                                 1 1 .
78 "j01cr02" "amoxicillin + clavulanic acid"                . . .
79 "j01ca04" "amoxicillin"                                  . 1 .
80 "j01cr02" "amoxicillin + clavulanic acid"                1 1 .
81 "j01ca04" "amoxicillin"                                  1 . .
82 "c08ca01" "amlodipine"                                   . 1 .
82 "j01ca04" "amoxicillin"                                  . . .
83 "c10aa05" "atorvastatin"                                 1 1 .
83 "j01cr02" "amoxicillin + clavulanic acid"                . . .
end

[Daniel Schaefer]

I don't think I'm seeing any examples of a false positive in your example data. It looks to me like each of these 1s corresponds to a change in the ATCode for any two observations with the dispensed_q2 tag.

[Tabitha Green]

Ah, yes, I now see there aren't many amlodipine medicines in this extract - there is an amlodipine-specific false positive with pat_id 61. The issue with the loop I have run (see below), is that the local macros are indicating medicines only containing amlodipine, however, the loop has captured switches for amoxicillin and atorvastatin, which is incorrect.