Flag/drop individual-case observations when the treatment arm is switched

[Sergey Alexeev]

Dear Stata masters,

I analyse a clinical trial where some patient switch treatment arms. I seek an effective code to flag observations only before patients switch the treatment arm.

For concreteness, consider the following patient observed 6 times (admitted 6 times into medical wards). Admission 4 and 5 (N=4,5) are in treated wards (treat=1), others in control (treat=0).

I must flag observation but only before "treat" changes value.

N	URN	WardAdmTime	treat	sample
1	15928	5/10/2019 14:27	0	1
2	15928	18/11/2019 21:02	0	1
3	15928	4/01/2020 12:03	0	1
4	15928	17/01/2020 9:49	1	0
5	15928	21/02/2020 18:53	0	0
6	15928	18/03/2020 12:38	1	0

I have a solution that (for data sorted by URN WardAdmTime) involves creating a variable "or_treat" equal to the first value of "treat" by URN. I then put a "flag" when "treat" and "or_treat" are different and then replace the value of "or_treat" with "flag" if the previous value is "flag."

An extract from data

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 URN double WardAdmTime float treat
"1156015"     1888661460000 1
"1156015"      1.888839e+12 0
"1156015" 1889784359999.998 0
"115621"  1900952700000.002 1
"115621"  1901388300000.002 0
"115621"      1903238940000 0
"115621"  1903291800000.002 0
"115621"  1903949700000.002 0
"115649"  1898796539999.998 0
"115658"      1898358240000 0
"1158249" 1894534919999.998 0
"1158255"     1887288960000 0
"1158411"     1.8842607e+12 0
"1158577"     1883843280000 1
"1158577"     1.8843471e+12 1
"1158577"     1884528960000 1
"1158577"     1.8858339e+12 0
"1158577"     1885896420000 1
"1158577"     1.8898806e+12 1
"1158577"     1894974360000 1
"1158577"     1897739160000 1
"115889"  1902539579999.998 0
"115889"      1902744120000 0
end
format %tcdd-Mon-CCYY_HH:MM WardAdmTime

The code
sort URN WardAdmTime
browse URN WardAdmTime treat
bysort URN (WardAdmTime): gen or_treat=treat[1]
bysort URN (WardAdmTime): replace or_treat=999 if or_treat!=treat
forvalues i=1/20 {
bysort URN (WardAdmTime): replace or_treat=999 if or_treat[_n-`i']==999
}
gen sample=test1!=999

I'd like to learn better ways to do this in STATA.

Kind regards,
Sergey Alexeev | Senior Research Fellow - Health Economist
NHMRC Clinical Trials Centre
The University of Sydney, Faculty of Medicine and Health
[email protected]

[Andrew Musau]

It's difficult to follow your explanation. The best way to illustrate what you want is to add a variable to your dataex named "wanted" that shows what you want the final output to look like.

[Sergey Alexeev]

Dear Andrew,

My apologies for the messy post. I am seeking a code to construct the variable "wanted," which flags patient-visit observations prior to switching the treatment arm.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 URN double WardAdmTime float(treat wanted)
"3274159"     1893671760000 0 1
"3274159"     1.8936741e+12 0 1
"3274159" 1895720100000.002 1 0
"327438"  1898543099999.998 0 1
"3274458" 1884639959999.998 1 1
"3274458"     1891018920000 0 0
"3274458" 1893765419999.998 1 0
"3274521"     1884859080000 0 1
"3274521"     1887377880000 0 1
"3274847"     1884277860000 0 1
"3275325" 1895159999999.998 0 1
"327550"      1.8924402e+12 0 1
"3275524"     1883325420000 0 1
"3275524"     1883646780000 1 0
"3275526"     1.8887553e+12 1 1
"3275530"     1.8980967e+12 0 1
"327567"      1900082220000 0 1
"327571"      1899322980000 1 1
"327571"       1.899387e+12 1 1
"3275746"     1885735560000 0 1
"327585"      1899349680000 1 1
"3275916" 1885990859999.998 0 1
"3275916"      1.886268e+12 1 0
"3275916" 1886845019999.998 0 0
end
format %tcdd-Mon-CCYY_HH:MM WardAdmTime

Thank you very much.

[Andrew Musau]

Something like?

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 URN double WardAdmTime float(treat wanted)
"3274159"     1893671760000 0 1
"3274159"     1.8936741e+12 0 1
"3274159" 1895720100000.002 1 0
"327438"  1898543099999.998 0 1
"3274458" 1884639959999.998 1 1
"3274458"     1891018920000 0 0
"3274458" 1893765419999.998 1 0
"3274521"     1884859080000 0 1
"3274521"     1887377880000 0 1
"3274847"     1884277860000 0 1
"3275325" 1895159999999.998 0 1
"327550"      1.8924402e+12 0 1
"3275524"     1883325420000 0 1
"3275524"     1883646780000 1 0
"3275526"     1.8887553e+12 1 1
"3275530"     1.8980967e+12 0 1
"327567"      1900082220000 0 1
"327571"      1899322980000 1 1
"327571"       1.899387e+12 1 1
"3275746"     1885735560000 0 1
"327585"      1899349680000 1 1
"3275916" 1885990859999.998 0 1
"3275916"      1.886268e+12 1 0
"3275916" 1886845019999.998 0 0
end
format %tcdd-Mon-CCYY_HH:MM WardAdmTime

bys URN (WardAdmTime): g w= cond(_n>1, !sum(treat), 1)| sum(treat)==_n

Res.:

. bys URN (WardAdmTime): g w= cond(_n>1, !sum(treat), 1)| sum(treat)==_n

. l, sepby(URN)

     +--------------------------------------------------+
     |     URN         WardAdmTime   treat   wanted   w |
     |--------------------------------------------------|
  1. | 3274159    3-Jan-2020 11:56       0        1   1 |
  2. | 3274159    3-Jan-2020 12:35       0        1   1 |
  3. | 3274159   27-Jan-2020 04:55       1        0   0 |
     |--------------------------------------------------|
  4. |  327438   28-Feb-2020 21:04       0        1   1 |
     |--------------------------------------------------|
  5. | 3274458   20-Sep-2019 23:05       1        1   1 |
  6. | 3274458    3-Dec-2019 19:02       0        0   0 |
  7. | 3274458    4-Jan-2020 13:56       1        0   0 |
     |--------------------------------------------------|
  8. | 3274521   23-Sep-2019 11:58       0        1   1 |
  9. | 3274521   22-Oct-2019 15:38       0        1   1 |
     |--------------------------------------------------|
 10. | 3274847   16-Sep-2019 18:31       0        1   1 |
     |--------------------------------------------------|
 11. | 3275325   20-Jan-2020 17:19       0        1   1 |
     |--------------------------------------------------|
 12. |  327550   20-Dec-2019 05:50       0        1   1 |
     |--------------------------------------------------|
 13. | 3275524    5-Sep-2019 17:57       0        1   1 |
 14. | 3275524    9-Sep-2019 11:13       1        0   0 |
     |--------------------------------------------------|
 15. | 3275526    7-Nov-2019 14:15       1        1   1 |
     |--------------------------------------------------|
 16. | 3275530   23-Feb-2020 17:05       0        1   1 |
     |--------------------------------------------------|
 17. |  327567   17-Mar-2020 16:37       0        1   1 |
     |--------------------------------------------------|
 18. |  327571    8-Mar-2020 21:43       1        1   1 |
 19. |  327571    9-Mar-2020 15:30       1        1   1 |
     |--------------------------------------------------|
 20. | 3275746    3-Oct-2019 15:26       0        1   1 |
     |--------------------------------------------------|
 21. |  327585    9-Mar-2020 05:08       1        1   1 |
     |--------------------------------------------------|
 22. | 3275916    6-Oct-2019 14:20       0        1   1 |
 23. | 3275916    9-Oct-2019 19:20       1        0   0 |
 24. | 3275916   16-Oct-2019 11:36       0        0   0 |
     +--------------------------------------------------+

.

[Sergey Alexeev]

Dear Andrew Musau,

I can confirm that your code performs as expected. Thank you for your time and excellent suggestion!