Counting events within a certain period

[Lydia Ewurabena Gamey]

Hello all,

Please I have a dataset where patients have several episodes of the disease. I am seeking to count a patient once within a 14 day window period and provide a clean dataset for analysis. I want to use loop programming.
Thanks for the help

Please find a sample of my data below

* Example generated by -dataex-. For more info, type help dataex
clear
input long pateid byte(sex ageatevent) int eventdate str5 read_4or5_code str30 description
 420431 0  3 16883 "H2z.." "Pneumonia or influenza NOS"    
 420431 0  3 16887 "H2..." "Pneumonia and influenza"       
 420431 0  3 16891 "H2z.." "Pneumonia or influenza NOS"    
3438114 1 12 16475 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16478 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16511 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16519 "H25.." "Bronchopneumonia,organism unsp"
3438455 0  1 16066 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16077 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16124 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16127 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16197 "H26.." "Pneumonia, organism unspecif." 
3440095 0  0 16429 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16461 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16593 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16628 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16727 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16729 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16736 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16742 "H25.." "Bronchopneumonia,organism unsp"
end
format %dD/N/CY eventdate
label values sex sexlbl
label def sexlbl 0 "Female", modify
label def sexlbl 1 "Male", modify

[Carlo Lazzaro]

Lydia:
you may want to try:

. bysort pateid (eventdate): egen wanted= sum(eventdate[_n+1]- eventdate[_n])

. bysort pateid: g flag=1 if wanted[1]>14

. bysort pateid: replace flag=0 if wanted[1]<=14

. label define flag 0 "within 14-day threshold" 1 "outside 14-day threshold"

. label val flag flag

[Lydia Ewurabena Gamey]

Hi Carlo, thanks so much. I tried the code, but some patients are not getting counted. There are patients with just one episode whilst others have multiple episodes as follow up visits. The goal is to count every patient with an episode but patients with multiple episodes can only be counted once within a 14 -day period.

Thanks

[Carlo Lazzaro]

Lydia:
hopefully what follows is nearer to what you're after:

. bysort pateid (eventdate): gen wanted2= (eventdate[_n+1]- eventdate[_n]) if _N>1

. bysort pateid (eventdate): replace wanted2=eventdate-0 if _N==1 & wanted2==.

. bysort pateid: g days=sum(wanted2)

. g flag2=1 if days>14

. replace flag2=0 if days<=14

. label define flag2 0 "within 14-day threshold" 1 "outside 14-day threshold"

. label val flag2 flag2

[Lydia Ewurabena Gamey]

Thanks sooo much Carlo. Will send an update soon

[Lydia Ewurabena Gamey]

Hi Carlo, Pls I tried the command but again, the patients with multiple episodes are not being counted.

[Nick Cox]

How is episode defined in relation to your variable names? I fear you may need to go beyond the data example to show what your desired result would be.

[Lydia Ewurabena Gamey]

Thanks Nick, All patients in the dataset have pneumonia, irrespective of the classification. I want to count all the patients in the dataset. But those with multiple entries must be counted once within a 14 day window period as those visits are considered as a follow-up visit and not a new episode. I hope that gives the question some clarity. Thanks

[Andrew Musau]

Is this what you want?

* Example generated by -dataex-. For more info, type help dataex
clear
input long pateid byte(sex ageatevent) int eventdate str5 read_4or5_code str30 description
 420431 0  3 16883 "H2z.." "Pneumonia or influenza NOS"    
 420431 0  3 16887 "H2..." "Pneumonia and influenza"       
 420431 0  3 16891 "H2z.." "Pneumonia or influenza NOS"    
3438114 1 12 16475 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16478 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16511 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16519 "H25.." "Bronchopneumonia,organism unsp"
3438455 0  1 16066 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16077 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16124 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16127 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16197 "H26.." "Pneumonia, organism unspecif." 
3440095 0  0 16429 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16461 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16593 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16628 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16727 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16729 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16736 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16742 "H25.." "Bronchopneumonia,organism unsp"
end
format %dD/N/CY eventdate
label values sex sexlbl
label def sexlbl 0 "Female", modify
label def sexlbl 1 "Male", modify

bys pateid (eventdate): gen difference= eventdate-eventdate[_n-1]
bys pateid: egen wanted = total(difference>14)

Res.:

. l pateid eventdate difference wanted, sepby( pateid )

     +------------------------------------------+
     |  pateid    eventdate   differ~e   wanted |
     |------------------------------------------|
  1. |  420431   23/03/2006          .        1 |
  2. |  420431   27/03/2006          4        1 |
  3. |  420431   31/03/2006          4        1 |
     |------------------------------------------|
  4. | 3438114   08/02/2005          .        2 |
  5. | 3438114   11/02/2005          3        2 |
  6. | 3438114   16/03/2005         33        2 |
  7. | 3438114   24/03/2005          8        2 |
     |------------------------------------------|
  8. | 3438455   27/12/2003          .        3 |
  9. | 3438455   07/01/2004         11        3 |
 10. | 3438455   23/02/2004         47        3 |
 11. | 3438455   26/02/2004          3        3 |
 12. | 3438455   06/05/2004         70        3 |
     |------------------------------------------|
 13. | 3440095   24/12/2004          .        5 |
 14. | 3440095   25/01/2005         32        5 |
 15. | 3440095   06/06/2005        132        5 |
 16. | 3440095   11/07/2005         35        5 |
 17. | 3440095   18/10/2005         99        5 |
 18. | 3440095   20/10/2005          2        5 |
 19. | 3440095   27/10/2005          7        5 |
 20. | 3440095   02/11/2005          6        5 |
     +------------------------------------------+

.

[Lydia Ewurabena Gamey]

Hi Andrew, close to it.
But you realize that the first patient with id 420431 has 3 records which all occur within a 14 day period, so that patient is counted once.
The second patient with id 3438114 has 4 records: 2 occurring in February and the other 2 in March(different 14-day window periods). As such this patient has 2 episodes.
Patient 3 has 5 records but only 3 episodes due to the 14-day window period.
I wish to count the the episodes.
Thank you

[Andrew Musau]

The variable "wanted" gives you a count of episodes per patient. To count all episodes, just tag a patient and sum.

* Example generated by -dataex-. For more info, type help dataex
clear
input long pateid byte(sex ageatevent) int eventdate str5 read_4or5_code str30 description
 420431 0  3 16883 "H2z.." "Pneumonia or influenza NOS"    
 420431 0  3 16887 "H2..." "Pneumonia and influenza"       
 420431 0  3 16891 "H2z.." "Pneumonia or influenza NOS"    
3438114 1 12 16475 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16478 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16511 "H25.." "Bronchopneumonia,organism unsp"
3438114 1 12 16519 "H25.." "Bronchopneumonia,organism unsp"
3438455 0  1 16066 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16077 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16124 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16127 "H26.." "Pneumonia, organism unspecif." 
3438455 0  2 16197 "H26.." "Pneumonia, organism unspecif." 
3440095 0  0 16429 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16461 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16593 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16628 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16727 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16729 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16736 "H25.." "Bronchopneumonia,organism unsp"
3440095 0  1 16742 "H25.." "Bronchopneumonia,organism unsp"
end
format %dD/N/CY eventdate
label values sex sexlbl
label def sexlbl 0 "Female", modify
label def sexlbl 1 "Male", modify

bys pateid (eventdate): gen difference= eventdate-eventdate[_n-1]
bys pateid: egen total = total(difference>14)
egen tag=tag(pateid)
egen wanted= total(cond(tag, total,.))

Res.:

. l, sep(0)

     +------------------------------------------------------------------------------------------------------------------------+
     |  pateid      sex   ageate~t    eventdate   read_4~e                      description   differ~e   total   tag   wanted |
     |------------------------------------------------------------------------------------------------------------------------|
  1. |  420431   Female          3   23/03/2006      H2z..       Pneumonia or influenza NOS          .       1     1       11 |
  2. |  420431   Female          3   27/03/2006      H2...          Pneumonia and influenza          4       1     0       11 |
  3. |  420431   Female          3   31/03/2006      H2z..       Pneumonia or influenza NOS          4       1     0       11 |
  4. | 3438114     Male         12   08/02/2005      H25..   Bronchopneumonia,organism unsp          .       2     1       11 |
  5. | 3438114     Male         12   11/02/2005      H25..   Bronchopneumonia,organism unsp          3       2     0       11 |
  6. | 3438114     Male         12   16/03/2005      H25..   Bronchopneumonia,organism unsp         33       2     0       11 |
  7. | 3438114     Male         12   24/03/2005      H25..   Bronchopneumonia,organism unsp          8       2     0       11 |
  8. | 3438455   Female          1   27/12/2003      H26..    Pneumonia, organism unspecif.          .       3     1       11 |
  9. | 3438455   Female          2   07/01/2004      H26..    Pneumonia, organism unspecif.         11       3     0       11 |
 10. | 3438455   Female          2   23/02/2004      H26..    Pneumonia, organism unspecif.         47       3     0       11 |
 11. | 3438455   Female          2   26/02/2004      H26..    Pneumonia, organism unspecif.          3       3     0       11 |
 12. | 3438455   Female          2   06/05/2004      H26..    Pneumonia, organism unspecif.         70       3     0       11 |
 13. | 3440095   Female          0   24/12/2004      H25..   Bronchopneumonia,organism unsp          .       5     1       11 |
 14. | 3440095   Female          1   25/01/2005      H25..   Bronchopneumonia,organism unsp         32       5     0       11 |
 15. | 3440095   Female          1   06/06/2005      H25..   Bronchopneumonia,organism unsp        132       5     0       11 |
 16. | 3440095   Female          1   11/07/2005      H25..   Bronchopneumonia,organism unsp         35       5     0       11 |
 17. | 3440095   Female          1   18/10/2005      H25..   Bronchopneumonia,organism unsp         99       5     0       11 |
 18. | 3440095   Female          1   20/10/2005      H25..   Bronchopneumonia,organism unsp          2       5     0       11 |
 19. | 3440095   Female          1   27/10/2005      H25..   Bronchopneumonia,organism unsp          7       5     0       11 |
 20. | 3440095   Female          1   02/11/2005      H25..   Bronchopneumonia,organism unsp          6       5     0       11 |
     +------------------------------------------------------------------------------------------------------------------------+

[Lydia Ewurabena Gamey]

Thank you very much. Really appreciate

[Lydia Ewurabena Gamey]

Hello all, the commands provided here were very helpful. Guided me to arrive at this solution.


bysort pateid : gen eventorder=_n
by pateid : gen interval=eventdate-eventdate[_n-1]
gen keeprec=1 if eventorder==1
replace keeprec=1 if interval>=14
replace keeprec=0 if interval==0
replace keeprec=0 if keeprec[_n-1]==1&interval<14


Thanks a lot