Creating a patient-flow

[preeti singh]

I have data of a longitudinal study, where every patient goes through a series of processes. For each step, a patient gets a status code. For e.g., if there are 5 steps from A to Z, a patient go from step 1 to step 2 to step 3 and so on in a serial order. However, some patients can go from step 1 to a higher step by jumping few steps in between. My desired output is the understand the flow of patients. How can I do it? One can use the lowest "visit number" as status code for step 1 and the "largest visit number" for the final step. [Important Note: Not all patients go through= similar number of steps].

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 patientid str2 status_code byte visit
"10-915"  "RI" 1
"10-915"  "RC" 2
"10-915"  "ER" 3
"10-986"  "RI" 1
"10-986"  "RC" 2
"11-1050" "RI" 1
"11-1050" "RC" 2
"11-1050" "ER" 3
"11-1050" "ER" 4
"11-1050" "UV" 5
"11-1050" "ER" 6
"2-109"   "RI" 1
"2-109"   "ER" 2
"2-109"   "RC" 3
"2-109"   "DC" 4
"2-128"   "RI" 1
"2-128"   "RC" 2
"2-128"   "ER" 3
end

Thanks.

[preeti singh]

Also, its difficult to set an order for status code, since one patient as per their health status can have multiple possible status code on every step.

[Nick Cox]

Thanks for the clear example. Lowest and highest visit number are just easy jobs for egen. As a bonus, here is a further technique that is sometimes useful. At least, I have posted a similar device in answer to many questions here.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 patientid str2 status_code byte visit
"10-915"  "RI" 1
"10-915"  "RC" 2
"10-915"  "ER" 3
"10-986"  "RI" 1
"10-986"  "RC" 2
"11-1050" "RI" 1
"11-1050" "RC" 2
"11-1050" "ER" 3
"11-1050" "ER" 4
"11-1050" "UV" 5
"11-1050" "ER" 6
"2-109"   "RI" 1
"2-109"   "ER" 2
"2-109"   "RC" 3
"2-109"   "DC" 4
"2-128"   "RI" 1
"2-128"   "RC" 2
"2-128"   "ER" 3
end

egen max = max(visit), by(patientid) 
egen min = min(visit), by(patientid) 

bysort patientid (visit) : gen history = status_code[1] 
by patientid : replace history = history[_n-1] + " " + status_code if _n > 1 
by patientid : replace history = history[_N] 

list, sepby(patientid) 

     +-------------------------------------------------------------+
     | patien~d   status~e   visit   max   min             history |
     |-------------------------------------------------------------|
  1. |   10-915         RI       1     3     1            RI RC ER |
  2. |   10-915         RC       2     3     1            RI RC ER |
  3. |   10-915         ER       3     3     1            RI RC ER |
     |-------------------------------------------------------------|
  4. |   10-986         RI       1     2     1               RI RC |
  5. |   10-986         RC       2     2     1               RI RC |
     |-------------------------------------------------------------|
  6. |  11-1050         RI       1     6     1   RI RC ER ER UV ER |
  7. |  11-1050         RC       2     6     1   RI RC ER ER UV ER |
  8. |  11-1050         ER       3     6     1   RI RC ER ER UV ER |
  9. |  11-1050         ER       4     6     1   RI RC ER ER UV ER |
 10. |  11-1050         UV       5     6     1   RI RC ER ER UV ER |
 11. |  11-1050         ER       6     6     1   RI RC ER ER UV ER |
     |-------------------------------------------------------------|
 12. |    2-109         RI       1     4     1         RI ER RC DC |
 13. |    2-109         ER       2     4     1         RI ER RC DC |
 14. |    2-109         RC       3     4     1         RI ER RC DC |
 15. |    2-109         DC       4     4     1         RI ER RC DC |
     |-------------------------------------------------------------|
 16. |    2-128         RI       1     3     1            RI RC ER |
 17. |    2-128         RC       2     3     1            RI RC ER |
 18. |    2-128         ER       3     3     1            RI RC ER |
     +-------------------------------------------------------------+

[preeti singh]

This is beautiful! Thanks!
Now, if I have to compute a table for all the different combinations at every step, how do I do it? For example, how many went from RI to RC in step 1, how many went from RI to ER in step 1 or step 2.

[Nick Cox]

Here is some technique. See also https://www.stata-journal.com/sjpdf....iclenum=dm0055 and mentions here of dm0055.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 patientid str2 status_code byte visit
"10-915"  "RI" 1
"10-915"  "RC" 2
"10-915"  "ER" 3
"10-986"  "RI" 1
"10-986"  "RC" 2
"11-1050" "RI" 1
"11-1050" "RC" 2
"11-1050" "ER" 3
"11-1050" "ER" 4
"11-1050" "UV" 5
"11-1050" "ER" 6
"2-109"   "RI" 1
"2-109"   "ER" 2
"2-109"   "RC" 3
"2-109"   "DC" 4
"2-128"   "RI" 1
"2-128"   "RC" 2
"2-128"   "ER" 3
end

egen max = max(visit), by(patientid) 
egen min = min(visit), by(patientid) 

bysort patientid (visit) : gen history = status_code[1] 
by patientid : replace history = history[_n-1] + " " + status_code if _n > 1 
by patientid : replace history = history[_N] 

gen wanted1 = substr(history, 1, 5) == "RI RC" 

egen wanted2 = total((st == "RI" & v == 1) | (st == "RC" & v == 2)), by(patientid) 
replace wanted2 = wanted2 == 2 

count if wanted1 & v == 1 
4 

list, sepby(patientid) 

     +---------------------------------------------------------------------------------+
     | patien~d   status~e   visit   max   min             history   wanted1   wanted2 |
     |---------------------------------------------------------------------------------|
  1. |   10-915         RI       1     3     1            RI RC ER         1         1 |
  2. |   10-915         RC       2     3     1            RI RC ER         1         1 |
  3. |   10-915         ER       3     3     1            RI RC ER         1         1 |
     |---------------------------------------------------------------------------------|
  4. |   10-986         RI       1     2     1               RI RC         1         1 |
  5. |   10-986         RC       2     2     1               RI RC         1         1 |
     |---------------------------------------------------------------------------------|
  6. |  11-1050         RI       1     6     1   RI RC ER ER UV ER         1         1 |
  7. |  11-1050         RC       2     6     1   RI RC ER ER UV ER         1         1 |
  8. |  11-1050         ER       3     6     1   RI RC ER ER UV ER         1         1 |
  9. |  11-1050         ER       4     6     1   RI RC ER ER UV ER         1         1 |
 10. |  11-1050         UV       5     6     1   RI RC ER ER UV ER         1         1 |
 11. |  11-1050         ER       6     6     1   RI RC ER ER UV ER         1         1 |
     |---------------------------------------------------------------------------------|
 12. |    2-109         RI       1     4     1         RI ER RC DC         0         0 |
 13. |    2-109         ER       2     4     1         RI ER RC DC         0         0 |
 14. |    2-109         RC       3     4     1         RI ER RC DC         0         0 |
 15. |    2-109         DC       4     4     1         RI ER RC DC         0         0 |
     |---------------------------------------------------------------------------------|
 16. |    2-128         RI       1     3     1            RI RC ER         1         1 |
 17. |    2-128         RC       2     3     1            RI RC ER         1         1 |
 18. |    2-128         ER       3     3     1            RI RC ER         1         1 |
     +--------------------------------------------------------------------------------

[preeti singh]

Thanks a ton, Nick. Is there a way to have the flow only till the status_code is "ER"?
Since there is a possibility of thousands of code combinations, I can have a much cleaner output if I remove the status_code after "ER" in history variable [ER=patient enrolled on the plan].

[Nick Cox]

If you're interested only in what precedes ER then you want

gen history2  = substr(history, 1, strpos(history, "ER") - 2)

[preeti singh]

Thanks! I'm interested only in what precedes till first ER. Anything after first ER is junk. Apparently, the person can have multiple ER.