Computing rolling lagged sums with bysort

[Christian Durach]

Dear Statalist users,

I have monthly time series data on flight_delays per air_carrier. For each air_carrier (uniquely identified by date and carrier identifier), I want to compute the rolling sum of delays across the past 5 months (from t=0 until t=-5).

I was looking into tsegen but did not figure out how to solve the issue that tsegen does not allow for bysort.

It would be really kind if someone could help me with this, I have been trying to find a solution for this all morning.

Thanks

Christian

[Øyvind Snilsberg]

perhaps,

gsort air_carrier -date
by air_carrier: gen sum = sum(flight_delays)

[Nick Cox]

0 1 2 3 4 5 gives you 6 months, not 5.

tsegen is from SSC as you are asked to explain (FAQ Advice #12).

The comment about bysort makes little sense to me, as tsegen provides automatically the support for panels and respect for time order that you need here.

webuse grunfeld, clear
tsegen cusum = rowtotal(L(1/5).mvalue)

is a token example. See the help for tsegen to see how to insist on using 5 non-missing values, or 6 as the case may be.

[Christian Durach]

Thank you both. I just found another solution that I wanted to share:

rangestat (sum) flight_delays, interval(month -5 0) by(n_ID)

forvalues i=1(1)5 {
replace flight_delays_sum=. if L`i'.n_ID != n_ID
}

[Nick Cox]

I don't know what the loop is intended to do as rangestat, by(n_id) ensures separate calculations for each identifier.

[Christian Durach]

The loop removes those observations that rangestat caculated with fewer than 6 lagged data points. In other words, if there are no proceeding 6 mths in the dataset yet, it replaces flight_delays_sum with a missing value.