forvalues may not be combined with by

[Ali Nato]

Dear All,

I'm trying to run a loop after sorting by group_id, the following is my code.
I'm getting error by stata as follows: 'forvalues may not be combined with by'

Any idea about an alternative to get this code working??

Many thanks,

by patientid: forvalues i = 1/`=_N' {
// Store the current start date and patient ID
local current_patient = patientid[`i']
local current_date = StartDate[`i']

// Check if there is an "MTX" in any subsequent record
replace coadmin_mtx = 1 if patientid == `current_patient' & ///
drugname == "Methotrexate" & StartDate > `current_date'
}

[Joseph Coveney]

. . . after sorting by group_id . . .,

. . . by patientid: forvalues i = 1/`=_N' { . . .

Did you mean by group_id: forvalues . . .?

To your question, I was going to suggest nested loops, but if your patient IDs are distinct for the dataset (that is, they aren't reused across group IDs), and if I've guessed about your dataset and intention correctly, then you could do something like the following.

bysort patientid (StartDate): generate byte coadmin_mtx = ///
    drugname == "Methotrexate" & StartDate > StartDate[1] if !missing(StartDate)

You didn't provide any data and so I couldn't test it, but something like that should get you close to what you're seeking, I think.

Regardless, it's usually better to avoid looping over observations, and a by . . .: with observation references in square brackets often allows that.

[Ali Nato]

Many thanks Joseph for your reply,

I'd be interested to learn more about how to do nested loops.

Yes, group_id is same as patientid and they are distinct for each patient in the dataset.
In your code, you are checking if drugname == "Methotrexate" for each row and if StartDate > StartDate [1] (first row).

I'm afraid what I want to do is more complex and I might not have explained properly.

For each row of the same patientid, I want coadmin_mtx to be 1 if patient received MTX in any other row of same patientid. i.e. coadmin_mtx will be used later as covariate in the regression model to see if the patient has received mtx at same time as primary medication and whether this influenced the outcome of primary medication he is being prescribed.

Hence, the need for nested loop I guess would be the correct way forward. I don't need to use bysort if nested loops would solve the problem.

Also when running your code above I got the error: byte not allowed

Kindly let me know if you can shed more light on how to use nested loops.

Many thanks,

[Nick Cox]

I don't see that you need nested loops. But for whatever happened later to be easily handled, reversing time is sometimes a good idea. Otherwise rangestat from SSC may help.

There is no data example above (FAQ Advice #12 and Joseph Coveney's clear request) but here is a silly example. The question answered is

Was methotrexate ever prescribed to this patient after this date (meaning, presumably, 1 or more days (?) later). And the answer is 1 if yes, 0 if no, missing if no information after this date.

* Example generated by -dataex-. For more info, type help dataex
clear
input float id str12 drugname float date
1 "apples "      1
1 "bananas"      2
1 "oranges"      3
1 "methotrexate" 4
1 "pineapple"    5
2 "raspberry"    1
2 "methotrexate" 2
2 "apples"       3
2 "apples "      4
2 "apples "      5
end

gen target = drugname == "methotrexate"

rangestat (max) wamted=target, interval(date 1 .) by(id)

list

     +--------------------------------------------+
     | id       drugname   date   target   wamted |
     |--------------------------------------------|
  1. |  1        apples       1        0        1 |
  2. |  1        bananas      2        0        1 |
  3. |  1        oranges      3        0        1 |
  4. |  1   methotrexate      4        1        0 |
  5. |  1      pineapple      5        0        . |
     |--------------------------------------------|
  6. |  2      raspberry      1        0        1 |
  7. |  2   methotrexate      2        1        0 |
  8. |  2         apples      3        0        0 |
  9. |  2        apples       4        0        0 |
 10. |  2        apples       5        0        . |
     +--------------------------------------------+

[Ali Nato]

Thank Nick,
Brilliant way to answer my question.

I've spent the last 2-3 hours trying to come up with solution using nested loops until I realised stata doesn't allow if statement within foreach loop!!


This is the table before applying the code (just in case you wanted to see the structure of the data).

patientid StartDate StopDate coadmin_mtx drugname 27 20-Oct-10 10-Jun-11 0 C 27 01-Jun-11 18-Jan-12 0 H 27 27-Jan-12 20-Apr-18 0 Methotrexate 27 20-Jun-18 0 T 28 23-Feb-12 14-Jan-13 0 E 28 29-Jan-13 06-Jun-13 0 Methotrexate 28 10-Jun-13 16-Mar-15 0 S 28 26-Jul-15 0 C 28 13-Mar-20 0 A

Your answer looked at the question differently and answered it nicely.

I wonder if we can expand on this, by looking at stop date also, i.e. if MTX was given after startdate but before stopdate?

I understand this can be very complicated. I'm grateful to both of your earlier answers regardless of whether this is possible

Thanks again.

[Nick Cox]

I realised Stata doesn't allow an if statement within a foreach loop

Not so, regardless of whether you mean the if command or an if qualifier.

If you show us code that you believe is prohibited, my guess is that the problem is different.

More importantly, please use dataex to give a data example. Otherwise experienced members here need guesses and rocket surgery to convert your table into code that can be run.

FAQ Advice #12 and

help dataex

explain.

[Ali Nato]

Many thanks Nick,

This is the code I've used for nested loops but keep getting the error: 'if not allowed'
I know the code needs tweaking but I lost the will because of the above error

gen coadmin_mtx = 0

* Loop through each unique patientid
levelsof patientid, local(patients)

foreach patient in `patients' {
* Find the number of records for this patient
count if patientid == `patient'
local N = r(N)

* Loop through each record of the patient
forvalues i = 1/`N' {
* Get the current start date for this patient record
local current_date = StartDate[`i'] if patientid == `patient'

* Loop through remaining records for this patient
forvalues j = `=`i'+1'/`N' {
* Check if Methotrexate is administered and if the start date is greater than the current date
if StartDate[`j'] > `current_date' & drugname[`j'] == "Methotrexate" & patientid[`j'] == `patient' {
replace coadmin_mtx = 1 if _n == `i'
break
}
}
}
}

below is the data output from dataex

input float(patientid StartDate StopDate coadmin_mtx target) double wanted str38 drugname
1 18555 18788 0 0 0 "Ciclosporin"
1 18779 19010 0 0 0 "adalimumab"
1 19019 21294 0 0 0 "ustekinumab"
1 21355 . 0 0 . "ixekizumab"
2 19046 19372 0 0 0 "etanercept"
2 19387 19515 0 0 0 "adalimumab"
2 19519 20163 0 0 0 "ustekinumab"
2 20295 . 0 0 0 "secukinumab"
2 21987 . 0 0 . "Acitretin"
3 21445 21476 0 0 0 "adalimumab"
3 21595 22053 0 0 . "ustekinumab"
4 19190 20677 0 0 1 "ustekinumab"
4 20947 . 0 0 1 "ustekinumab"
4 20951 21007 0 1 . "Methotrexate"
5 20230 22097 0 0 0 "ustekinumab"
5 22629 . 0 0 . "ustekinumab"
6 19787 21760 0 0 . "infliximab"
7 20413 20593 0 0 0 "infliximab"
7 20633 20991 0 0 0 "ustekinumab"
7 21028 . 0 0 . "ustekinumab"
8 20833 20967 0 0 0 "etanercept"
8 20974 21357 0 0 0 "etanercept"
8 21364 21441 0 0 0 "etanercept"
8 21462 . 0 0 . "etanercept"
9 21391 . 0 0 . "ustekinumab"
10 19974 . 0 1 1 "Methotrexate"
10 19995 20093 0 1 1 "Methotrexate"
10 20100 21083 0 1 1 "Methotrexate"
10 21084 21206 0 1 0 "Methotrexate"
10 21206 22134 0 0 0 "Apremilast"
10 22345 . 0 0 . "Guselkumab"
11 18536 19043 0 0 1 "adalimumab"
11 18807 19197 0 1 0 "Methotrexate"
11 19197 19254 0 0 0 "Ciclosporin"
11 19254 . 0 0 . "ustekinumab"
end
format %td StartDate
format %td StopDate

end

[Nick Cox]

Thanks for the data example. I am not sure that I understand what you want any more, but this may be suggestive.

clear 
input float(patientid StartDate StopDate coadmin_mtx target) double wanted str38 drugname
1 18555 18788 0 0 0 "Ciclosporin"
1 18779 19010 0 0 0 "adalimumab"
1 19019 21294 0 0 0 "ustekinumab"
1 21355 . 0 0 . "ixekizumab"
2 19046 19372 0 0 0 "etanercept"
2 19387 19515 0 0 0 "adalimumab"
2 19519 20163 0 0 0 "ustekinumab"
2 20295 . 0 0 0 "secukinumab"
2 21987 . 0 0 . "Acitretin"
3 21445 21476 0 0 0 "adalimumab"
3 21595 22053 0 0 . "ustekinumab"
4 19190 20677 0 0 1 "ustekinumab"
4 20947 . 0 0 1 "ustekinumab"
4 20951 21007 0 1 . "Methotrexate"
5 20230 22097 0 0 0 "ustekinumab"
5 22629 . 0 0 . "ustekinumab"
6 19787 21760 0 0 . "infliximab"
7 20413 20593 0 0 0 "infliximab"
7 20633 20991 0 0 0 "ustekinumab"
7 21028 . 0 0 . "ustekinumab"
8 20833 20967 0 0 0 "etanercept"
8 20974 21357 0 0 0 "etanercept"
8 21364 21441 0 0 0 "etanercept"
8 21462 . 0 0 . "etanercept"
9 21391 . 0 0 . "ustekinumab"
10 19974 . 0 1 1 "Methotrexate"
10 19995 20093 0 1 1 "Methotrexate"
10 20100 21083 0 1 1 "Methotrexate"
10 21084 21206 0 1 0 "Methotrexate"
10 21206 22134 0 0 0 "Apremilast"
10 22345 . 0 0 . "Guselkumab"
11 18536 19043 0 0 1 "adalimumab"
11 18807 19197 0 1 0 "Methotrexate"
11 19197 19254 0 0 0 "Ciclosporin"
11 19254 . 0 0 . "ustekinumab"
end
format %td StartDate
format %td StopDate

* target = drugname == "Methotrexate"
bysort patientid (StartDate) : gen counter = sum(target) 
by patientid : gen anylater = counter < counter[_N]

list patientid StartDate counter anylater if target == 1, sepby(patientid)

 

    +-------------------------------------------+
     | patien~d   StartDate   counter   anylater |
     |-------------------------------------------|
 14. |        4   12may2017         1          0 |
     |-------------------------------------------|
 26. |       10   08sep2014         1          1 |
 27. |       10   29sep2014         2          1 |
 28. |       10   12jan2015         3          1 |
 29. |       10   22sep2017         4          0 |
     |-------------------------------------------|
 33. |       11   29jun2011         1          0 |
     +-------------------------------------------+

[Nick Cox]

What seems a natural question here is whether any patient is on two or more medications at once. For that I find the device discussed in https://journals.sagepub.com/doi/pdf...867X1301300116 to be useful.

Essentially we use expand to reshape long without using the reshape command. If we then set a marker that is 1 when a medication is started and -1 when it is stopped then its cumulative sum is the number of medications being taken.

This is still sensitive to errors or omissions in the data. Consider the first observation for patient 10.

clear 
input float(patientid StartDate StopDate coadmin_mtx target) double wanted str38 drugname
1 18555 18788 0 0 0 "Ciclosporin"
1 18779 19010 0 0 0 "adalimumab"
1 19019 21294 0 0 0 "ustekinumab"
1 21355 . 0 0 . "ixekizumab"
2 19046 19372 0 0 0 "etanercept"
2 19387 19515 0 0 0 "adalimumab"
2 19519 20163 0 0 0 "ustekinumab"
2 20295 . 0 0 0 "secukinumab"
2 21987 . 0 0 . "Acitretin"
3 21445 21476 0 0 0 "adalimumab"
3 21595 22053 0 0 . "ustekinumab"
4 19190 20677 0 0 1 "ustekinumab"
4 20947 . 0 0 1 "ustekinumab"
4 20951 21007 0 1 . "Methotrexate"
5 20230 22097 0 0 0 "ustekinumab"
5 22629 . 0 0 . "ustekinumab"
6 19787 21760 0 0 . "infliximab"
7 20413 20593 0 0 0 "infliximab"
7 20633 20991 0 0 0 "ustekinumab"
7 21028 . 0 0 . "ustekinumab"
8 20833 20967 0 0 0 "etanercept"
8 20974 21357 0 0 0 "etanercept"
8 21364 21441 0 0 0 "etanercept"
8 21462 . 0 0 . "etanercept"
9 21391 . 0 0 . "ustekinumab"
10 19974 . 0 1 1 "Methotrexate"
10 19995 20093 0 1 1 "Methotrexate"
10 20100 21083 0 1 1 "Methotrexate"
10 21084 21206 0 1 0 "Methotrexate"
10 21206 22134 0 0 0 "Apremilast"
10 22345 . 0 0 . "Guselkumab"
11 18536 19043 0 0 1 "adalimumab"
11 18807 19197 0 1 0 "Methotrexate"
11 19197 19254 0 0 0 "Ciclosporin"
11 19254 . 0 0 . "ustekinumab"
end
format %td StartDate
format %td StopDate

duplicates report patientid StartDate
assert StopDate > StartDate

expand 2 

bysort patientid StartDate : gen date = cond(_n == 1, StartDate, StopDate)
by patientid StartDate : gen change = cond(_n == 1, 1, -1)

bysort patientid (date StartDate) : gen number = sum(change) if date < . 
by patientid : replace number = number[_n-1] if date == . 

format date %td 
list , sepby(patientid)


     +------------------------------------------------------------------------------------------------------------+
     | patien~d   StartDate    StopDate   coadmi~x   target   wanted       drugname        date   change   number |
     |------------------------------------------------------------------------------------------------------------|
  1. |        1   20oct2010   10jun2011          0        0        0    Ciclosporin   20oct2010        1        1 |
  2. |        1   01jun2011   18jan2012          0        0        0     adalimumab   01jun2011        1        2 |
  3. |        1   20oct2010   10jun2011          0        0        0    Ciclosporin   10jun2011       -1        1 |
  4. |        1   01jun2011   18jan2012          0        0        0     adalimumab   18jan2012       -1        0 |
  5. |        1   27jan2012   20apr2018          0        0        0    ustekinumab   27jan2012        1        1 |
  6. |        1   27jan2012   20apr2018          0        0        0    ustekinumab   20apr2018       -1        0 |
  7. |        1   20jun2018           .          0        0        .     ixekizumab   20jun2018        1        1 |
  8. |        1   20jun2018           .          0        0        .     ixekizumab           .       -1        1 |
     |------------------------------------------------------------------------------------------------------------|
  9. |        2   23feb2012   14jan2013          0        0        0     etanercept   23feb2012        1        1 |
 10. |        2   23feb2012   14jan2013          0        0        0     etanercept   14jan2013       -1        0 |
 11. |        2   29jan2013   06jun2013          0        0        0     adalimumab   29jan2013        1        1 |
 12. |        2   29jan2013   06jun2013          0        0        0     adalimumab   06jun2013       -1        0 |
 13. |        2   10jun2013   16mar2015          0        0        0    ustekinumab   10jun2013        1        1 |
 14. |        2   10jun2013   16mar2015          0        0        0    ustekinumab   16mar2015       -1        0 |
 15. |        2   26jul2015           .          0        0        0    secukinumab   26jul2015        1        1 |
 16. |        2   13mar2020           .          0        0        .      Acitretin   13mar2020        1        2 |
 17. |        2   26jul2015           .          0        0        0    secukinumab           .       -1        2 |
 18. |        2   13mar2020           .          0        0        .      Acitretin           .       -1        2 |
     |------------------------------------------------------------------------------------------------------------|
 19. |        3   18sep2018   19oct2018          0        0        0     adalimumab   18sep2018        1        1 |
 20. |        3   18sep2018   19oct2018          0        0        0     adalimumab   19oct2018       -1        0 |
 21. |        3   15feb2019   18may2020          0        0        .    ustekinumab   15feb2019        1        1 |
 22. |        3   15feb2019   18may2020          0        0        .    ustekinumab   18may2020       -1        0 |
     |------------------------------------------------------------------------------------------------------------|
 23. |        4   16jul2012   11aug2016          0        0        1    ustekinumab   16jul2012        1        1 |
 24. |        4   16jul2012   11aug2016          0        0        1    ustekinumab   11aug2016       -1        0 |
 25. |        4   08may2017           .          0        0        1    ustekinumab   08may2017        1        1 |
 26. |        4   12may2017   07jul2017          0        1        .   Methotrexate   12may2017        1        2 |
 27. |        4   12may2017   07jul2017          0        1        .   Methotrexate   07jul2017       -1        1 |
 28. |        4   08may2017           .          0        0        1    ustekinumab           .       -1        1 |
     |------------------------------------------------------------------------------------------------------------|
 29. |        5   22may2015   01jul2020          0        0        0    ustekinumab   22may2015        1        1 |
 30. |        5   22may2015   01jul2020          0        0        0    ustekinumab   01jul2020       -1        0 |
 31. |        5   15dec2021           .          0        0        .    ustekinumab   15dec2021        1        1 |
 32. |        5   15dec2021           .          0        0        .    ustekinumab           .       -1        1 |
     |------------------------------------------------------------------------------------------------------------|
 33. |        6   05mar2014   30jul2019          0        0        .     infliximab   05mar2014        1        1 |
 34. |        6   05mar2014   30jul2019          0        0        .     infliximab   30jul2019       -1        0 |
     |------------------------------------------------------------------------------------------------------------|
 35. |        7   21nov2015   19may2016          0        0        0     infliximab   21nov2015        1        1 |
 36. |        7   21nov2015   19may2016          0        0        0     infliximab   19may2016       -1        0 |
 37. |        7   28jun2016   21jun2017          0        0        0    ustekinumab   28jun2016        1        1 |
 38. |        7   28jun2016   21jun2017          0        0        0    ustekinumab   21jun2017       -1        0 |
 39. |        7   28jul2017           .          0        0        .    ustekinumab   28jul2017        1        1 |
 40. |        7   28jul2017           .          0        0        .    ustekinumab           .       -1        1 |
     |------------------------------------------------------------------------------------------------------------|
 41. |        8   14jan2017   28may2017          0        0        0     etanercept   14jan2017        1        1 |
 42. |        8   14jan2017   28may2017          0        0        0     etanercept   28may2017       -1        0 |
 43. |        8   04jun2017   22jun2018          0        0        0     etanercept   04jun2017        1        1 |
 44. |        8   04jun2017   22jun2018          0        0        0     etanercept   22jun2018       -1        0 |
 45. |        8   29jun2018   14sep2018          0        0        0     etanercept   29jun2018        1        1 |
 46. |        8   29jun2018   14sep2018          0        0        0     etanercept   14sep2018       -1        0 |
 47. |        8   05oct2018           .          0        0        .     etanercept   05oct2018        1        1 |
 48. |        8   05oct2018           .          0        0        .     etanercept           .       -1        1 |
     |------------------------------------------------------------------------------------------------------------|
 49. |        9   26jul2018           .          0        0        .    ustekinumab   26jul2018        1        1 |
 50. |        9   26jul2018           .          0        0        .    ustekinumab           .       -1        1 |
     |------------------------------------------------------------------------------------------------------------|
 51. |       10   08sep2014           .          0        1        1   Methotrexate   08sep2014        1        1 |
 52. |       10   29sep2014   05jan2015          0        1        1   Methotrexate   29sep2014        1        2 |
 53. |       10   29sep2014   05jan2015          0        1        1   Methotrexate   05jan2015       -1        1 |
 54. |       10   12jan2015   21sep2017          0        1        1   Methotrexate   12jan2015        1        2 |
 55. |       10   12jan2015   21sep2017          0        1        1   Methotrexate   21sep2017       -1        1 |
 56. |       10   22sep2017   22jan2018          0        1        0   Methotrexate   22sep2017        1        2 |
 57. |       10   22sep2017   22jan2018          0        1        0   Methotrexate   22jan2018       -1        1 |
 58. |       10   22jan2018   07aug2020          0        0        0     Apremilast   22jan2018        1        2 |
 59. |       10   22jan2018   07aug2020          0        0        0     Apremilast   07aug2020       -1        1 |
 60. |       10   06mar2021           .          0        0        .     Guselkumab   06mar2021        1        2 |
 61. |       10   08sep2014           .          0        1        1   Methotrexate           .       -1        2 |
 62. |       10   06mar2021           .          0        0        .     Guselkumab           .       -1        2 |
     |------------------------------------------------------------------------------------------------------------|
 63. |       11   01oct2010   20feb2012          0        0        1     adalimumab   01oct2010        1        1 |
 64. |       11   29jun2011   23jul2012          0        1        0   Methotrexate   29jun2011        1        2 |
 65. |       11   01oct2010   20feb2012          0        0        1     adalimumab   20feb2012       -1        1 |
 66. |       11   29jun2011   23jul2012          0        1        0   Methotrexate   23jul2012       -1        0 |
 67. |       11   23jul2012   18sep2012          0        0        0    Ciclosporin   23jul2012        1        1 |
 68. |       11   23jul2012   18sep2012          0        0        0    Ciclosporin   18sep2012       -1        0 |
 69. |       11   18sep2012           .          0        0        .    ustekinumab   18sep2012        1        1 |
 70. |       11   18sep2012           .          0        0        .    ustekinumab           .       -1        1 |
     +------------------------------------------------------------------------------------------------------------+