Events happening between two dates

[Marit Stormoen]

Hi,
I have a dataset with a large amount of sites with sub-units, with reoccurring year classes at the same sites. Once in a while an event takes place on one or more of the subunits (event=1, no event=missing). I need to find out the number of units at a site having an event within +/- three days of another unit so I can calculate the percent of the total units going through an event at the same time (same time being within +/- 3 days).

The dataset is tsset with year class, site and unit as identifiers (I made an unique ID from these three variables). It is easy enough to get the date three days before or three days after an event at a single unit, but I'm at a loss how to code to get all units at the site having an event withing the given time span. Hopefull I'm just tired on a friday afternoon, and this is really easy

I've attached data from two sites so you can see the structure.

Hope you can help, thanks in advance!

[Nick Cox]

No spreadsheets, thanks! https://www.statalist.org/forums/help#stata explains. You were asked to read it before posting!

When that's done, there will probably be an answer using rangestat (SSC) except that I'm taking a mini-break and won't be posting it.

[Marit Stormoen]

Sorry about that, here is a smaller section of the data in dataex format. I'll try to explore rangestat.

input int yr_class str5 site byte unit float date byte event float(daysafter daysbefore ID)
2014 "Site2" 2 20121 .     . . 10
2014 "Site2" 3 20121 .     . . 11
2014 "Site2" 4 20121 .     . . 12
2014 "Site2" 5 20121 .     . . 13
2014 "Site2" 1 20128 .     . .  9
2014 "Site2" 2 20128 .     . . 10
2014 "Site2" 3 20128 .     . . 11
2014 "Site2" 4 20128 .     . . 12
2014 "Site2" 5 20128 .     . . 13
2014 "Site2" 2 20131 1 20134 20128 10
2014 "Site2" 4 20131 1 20134 20128 12
2014 "Site2" 5 20132 1 20135 20129 13
2014 "Site2" 1 20135 .     . .  9
2014 "Site2" 2 20135 .     . . 10
2014 "Site2" 3 20135 .     . . 11
2014 "Site2" 4 20135 .     . . 12
2014 "Site2" 5 20135 .     . . 13
2014 "Site2" 1 20146 .     . .  9
2014 "Site2" 2 20146 .     . . 10
2014 "Site2" 3 20146 .     . . 11
2014 "Site2" 4 20146 .     . . 12
2014 "Site2" 5 20146 .     . . 13
2014 "Site2" 4 20149 1 20152 20146 12
2014 "Site2" 1 20151 .     . .  9
2014 "Site2" 2 20151 .     . . 10
2014 "Site2" 3 20151 .     . . 11
2014 "Site2" 4 20151 .     . . 12
2014 "Site2" 5 20151 .     . . 13
2014 "Site2" 1 20152 .     . .  9
2014 "Site2" 3 20152 .     . . 11
2014 "Site2" 5 20152 .     . . 13
2014 "Site2" 1 20153 .     . .  9
2014 "Site2" 3 20153 .     . . 11
2014 "Site2" 5 20153 .     . . 13
2014 "Site2" 1 20154 .     . .  9
2014 "Site2" 3 20154 .     . . 11
2014 "Site2" 5 20154 .     . . 13
2014 "Site2" 1 20155 .     . .  9
2014 "Site2" 3 20155 .     . . 11
2014 "Site2" 5 20155 .     . . 13
2014 "Site2" 1 20156 .     . .  9
2014 "Site2" 3 20156 .     . . 11
2014 "Site2" 5 20156 .     . . 13
2014 "Site2" 1 20157 .     . .  9
2014 "Site2" 3 20157 .     . . 11
2014 "Site2" 5 20157 .     . . 13
2014 "Site2" 1 20158 .     . .  9
2014 "Site2" 2 20158 .     . . 10
2014 "Site2" 3 20158 .     . . 11
2014 "Site2" 4 20158 .     . . 12
2014 "Site2" 5 20158 .     . . 13
2014 "Site2" 1 20159 .     . .  9
2014 "Site2" 3 20159 .     . . 11
2014 "Site2" 5 20159 .     . . 13
2014 "Site2" 1 20160 .     . .  9
2014 "Site2" 3 20160 .     . . 11
2014 "Site2" 1 20164 .     . .  9
2014 "Site2" 2 20164 .     . . 10
2014 "Site2" 3 20164 .     . . 11
2014 "Site2" 4 20164 .     . . 12
2014 "Site2" 5 20164 .     . . 13
2014 "Site2" 2 20170 1 20173 20167 10
2014 "Site2" 1 20172 .     . .  9
2014 "Site2" 2 20172 .     . . 10
2014 "Site2" 3 20172 .     . . 11
2014 "Site2" 5 20172 .     . . 13
2014 "Site2" 4 20173 .     . . 12
2014 "Site2" 1 20177 .     . .  9
2014 "Site2" 2 20177 .     . . 10
2014 "Site2" 3 20177 .     . . 11
2014 "Site2" 4 20177 .     . . 12
2014 "Site2" 5 20177 .     . . 13
2014 "Site2" 1 20186 .     . .  9
2014 "Site2" 2 20186 .     . . 10
2014 "Site2" 3 20186 .     . . 11
2014 "Site2" 4 20186 .     . . 12
2014 "Site2" 5 20186 .     . . 13
2014 "Site2" 1 20192 .     . .  9
2014 "Site2" 2 20192 .     . . 10
2014 "Site2" 3 20192 .     . . 11
2014 "Site2" 4 20192 .     . . 12
2014 "Site2" 5 20192 .     . . 13
2014 "Site2" 1 20200 .     . .  9
2014 "Site2" 2 20200 .     . . 10
2014 "Site2" 3 20200 .     . . 11
2014 "Site2" 4 20200 .     . . 12
2014 "Site2" 5 20200 .     . . 13
2014 "Site2" 1 20206 .     . .  9
2014 "Site2" 2 20206 .     . . 10
2014 "Site2" 3 20206 .     . . 11
2014 "Site2" 4 20206 .     . . 12
2014 "Site2" 5 20206 .     . . 13
2014 "Site2" 1 20212 .     . .  9
2014 "Site2" 2 20212 .     . . 10
2014 "Site2" 3 20212 .     . . 11
2014 "Site2" 4 20212 .     . . 12
2014 "Site2" 5 20212 .     . . 13
end
format %td date
format %td daysafter
format %td daysbefore

[Robert Picard]

It looks like you want, for each observation in the data, a count of the number of units at the same site having an event in a -3/+3 window of the observation's date. Since I worry that a unit may have multiple events within the window, I would use rangejoin (from SSC) to tackle this problem:

* add an observation id once the data is tsset and save the data example
drop daysafter daysbefore
tsset ID date
gen long obs = _n
save "example_dataex.dta", replace

* save the events
keep if event == 1
keep site date ID
save "example_events.dta", replace
list

* match each initial obs with events within the window
use "example_dataex.dta", clear
rangejoin date -3 3 using "example_events.dta", by(site) suffix(_event)

* reduce to one event within a unit
bysort obs ID_event (date_event): keep if _n == 1

* the number of units with an event within the window
by obs: gen in_window = _N

the listing of events shows:

. list

     +------------------------+
     |  site        date   ID |
     |------------------------|
  1. | Site2   12feb2015   10 |
  2. | Site2   23mar2015   10 |
  3. | Site2   12feb2015   12 |
  4. | Site2   02mar2015   12 |
  5. | Site2   13feb2015   13 |
     +------------------------+

.

and here's the final listing of matched events:

. list obs ID date ID_event date_event in_window if !mi(date_event), sepby(obs)

     +--------------------------------------------------------+
     | obs   ID        date   ID_event   date_ev~t   in_win~w |
     |--------------------------------------------------------|
  1. |   1    9   09feb2015         10   12feb2015          2 |
  2. |   1    9   09feb2015         12   12feb2015          2 |
     |--------------------------------------------------------|
  3. |   2    9   16feb2015         13   13feb2015          1 |
     |--------------------------------------------------------|
  4. |   3    9   27feb2015         12   02mar2015          1 |
     |--------------------------------------------------------|
  5. |   4    9   04mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
  6. |   5    9   05mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 16. |  15    9   25mar2015         10   23mar2015          1 |
     |--------------------------------------------------------|
 24. |  23   10   09feb2015         10   12feb2015          2 |
 25. |  23   10   09feb2015         12   12feb2015          2 |
     |--------------------------------------------------------|
 26. |  24   10   12feb2015         10   12feb2015          3 |
 27. |  24   10   12feb2015         12   12feb2015          3 |
 28. |  24   10   12feb2015         13   13feb2015          3 |
     |--------------------------------------------------------|
 29. |  25   10   16feb2015         13   13feb2015          1 |
     |--------------------------------------------------------|
 30. |  26   10   27feb2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 31. |  27   10   04mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 34. |  30   10   23mar2015         10   23mar2015          1 |
     |--------------------------------------------------------|
 35. |  31   10   25mar2015         10   23mar2015          1 |
     |--------------------------------------------------------|
 43. |  39   11   09feb2015         10   12feb2015          2 |
 44. |  39   11   09feb2015         12   12feb2015          2 |
     |--------------------------------------------------------|
 45. |  40   11   16feb2015         13   13feb2015          1 |
     |--------------------------------------------------------|
 46. |  41   11   27feb2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 47. |  42   11   04mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 48. |  43   11   05mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 58. |  53   11   25mar2015         10   23mar2015          1 |
     |--------------------------------------------------------|
 66. |  61   12   09feb2015         10   12feb2015          2 |
 67. |  61   12   09feb2015         12   12feb2015          2 |
     |--------------------------------------------------------|
 68. |  62   12   12feb2015         10   12feb2015          3 |
 69. |  62   12   12feb2015         12   12feb2015          3 |
 70. |  62   12   12feb2015         13   13feb2015          3 |
     |--------------------------------------------------------|
 71. |  63   12   16feb2015         13   13feb2015          1 |
     |--------------------------------------------------------|
 72. |  64   12   27feb2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 73. |  65   12   02mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 74. |  66   12   04mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 77. |  69   12   26mar2015         10   23mar2015          1 |
     |--------------------------------------------------------|
 85. |  77   13   09feb2015         10   12feb2015          2 |
 86. |  77   13   09feb2015         12   12feb2015          2 |
     |--------------------------------------------------------|
 87. |  78   13   13feb2015         10   12feb2015          3 |
 88. |  78   13   13feb2015         12   12feb2015          3 |
 89. |  78   13   13feb2015         13   13feb2015          3 |
     |--------------------------------------------------------|
 90. |  79   13   16feb2015         13   13feb2015          1 |
     |--------------------------------------------------------|
 91. |  80   13   27feb2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 92. |  81   13   04mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
 93. |  82   13   05mar2015         12   02mar2015          1 |
     |--------------------------------------------------------|
102. |  91   13   25mar2015         10   23mar2015          1 |
     +--------------------------------------------------------+

.

[Marit Stormoen]

Thank you so much Robert! This worked fine!