summing observations over a date range; not equally spaced dataset; using forvalues and bysort

[Luke Benvenuto]

Hello,

I have a dataset and I am trying to sum observations over a time interval of 365 days in not equally spaced data. My data set includes a person, center, date and events. I am trying to sum the number of events that occur at each center over the next 365 days for each person. Not every observation/person has an event, sometimes it is 2 events, sometimes 1 event, sometimes 0. The dates are not equally spaced. When I use the code below, what results is the number of persons or the count over the 365 day period rather than the sum of events.

sort ctr Date
generate sum_events =.
local N = _N
quietly forvalues i = 1/`N' {
sum(events) if ctr == ctr[`i'] & inrange(Date, Date[`i'], Date[`i']+365),
replace sum_events = r(N) in `i'
}

This returns the count of persons/observations over 365 days rather than the number of events over 365 days

input double person_code str8 ctr float Date double events
89 "0009" 19947 0
49 "0009" 20048 2
24 "0009" 20198 2
809 "0009" 20262 0
550 "0009" 20328 2
336 "0009" 20376 2
330 "0009" 20402 2
709 "0009" 20429 2
586 "0021" 16560 0
301 "0021" 16560 1
891 "0021" 16560 2
612 "0021" 16560 0

I have tried this code as well

sort ctr Date
by ctr: gen sum_events = sum(events) if inrange(Date, Date[`i'], Date[`i']+365)

The r(111) message returns and it says "Date not found"

I am pretty new to stata. I appreciate any help/assistance greatly. I hope my question is clear.

Thank you.

Luke

[Clyde Schechter]

I'm a little confused by your description of the problem. But if I do understand what you want, it can be done with:

rangestat (sum) total_events = events, interval(Date 0 365) by(ctr)

Note: -rangestat- is not part of official Stata. It was written by Robert Picard, Nick Cox, and Roberto Ferrer. You can get it from SSC.

[Nick Cox]

I agree with Clyde. If the counts are desired for each person and center combination you need

 
 rangestat (sum) total_events = events, interval(Date 0 365) by(ctr person)

but if it is just each center then Clyde's code is fine.

In your original code you need r(sum) not r(N).

[Luke Benvenuto]

Thank you Nick and Clyde. Clyde's code worked perfectly for my problem

Also as Nick stated changing r(N) to r(sum) also fixed my problem as well.

Thanks a ton.

[Nick Cox]

Good, but in the original wording

the number of events that occur at each center over the next 365 days for each person

"for each person" is better omitted.

[Luke Benvenuto]

Ok, I see that now, sorry for confusion. Thanks again.