Computing running sum with moving time-window

Question

My data

I am working on a spell dataset in the following format:

cls
clear all
set more off

input id spellnr  str7 bdate_str  str7 edate_str  employed
       1    1         2008m1          2008m9          1  
       1    2        2008m12          2009m8          0   
       1    3        2009m11          2010m9          1  
       1    4        2010m10          2011m9          0  
       ///
       2    1         2007m4         2009m12          1
       2    2         2010m4          2011m4          1
       2    3         2011m6          2011m8          0
end

* translate to Stata monthly dates
gen bdate = monthly(bdate_str,"YM")
gen edate = monthly(edate_str,"YM")
drop *_str
format %tm bdate edate

list, sepby(id)

Corresponding to:

     +---------------------------------------------+
     | id   spellnr   employed     bdate     edate |
     |---------------------------------------------|
  1. |  1         1          1    2008m1    2008m9 |
  2. |  1         2          0   2008m12    2009m8 |
  3. |  1         3          1   2009m11    2010m9 |
  4. |  1         4          0   2010m10    2011m9 |
     |---------------------------------------------|
  5. |  2         1          1    2007m4   2009m12 |
  6. |  2         2          1    2010m4    2011m4 |
  7. |  2         3          0    2011m6    2011m8 |
     +---------------------------------------------+

Here a given person (id) can have multiple spells (spellnr) of two types (unempl: 1 for unemployment; 0 for employment). the start-end dates of each spell are definied by bdate and edate, respectively.

Imagine the data was already cleaned, and is such that no spells overlap with each other. There might be "missing" periods in between any two spells though. This is captured by the dummy dataset above.

My question:

For each unemployment spell, I need to compute the number of months spent in employment in the last 6 months, 12 months, and 24 months.

Note that, importantly, each id can go in and out from employment, and all past employment spells should be taken into account (not just the last one).

In my example, this would lead to the following desired output:

     +--------------------------------------------------------------+
     | id   spellnr   employed     bdate     edate   m6   m24   m48 |
     |--------------------------------------------------------------|
  1. |  1         1          1    2008m1    2008m9    .     .     . |
  2. |  1         2          0   2008m12    2009m8    4     9     9 |
  3. |  1         3          1   2009m11    2010m9    .     .     . |
  4. |  1         4          0   2010m10    2011m9    6    11    20 |
     |--------------------------------------------------------------|
  5. |  2         1          1    2007m4   2009m12    .     .     . |
  6. |  2         2          1    2010m4    2011m4    .     .     . |
  7. |  2         3          0    2011m6    2011m8    5    20    44 |
     +--------------------------------------------------------------+

My (working) attempt:

The following code returns the desired result.

* expand each spell to one observation per time unit (here "months"; works also for days)
expand edate-bdate+1
bysort id spellnr: gen spell_date = bdate + _n - 1
format %tm spell_date
list, sepby(id spellnr)

* fill-in empty months (not covered by spells)
xtset id spell_date, monthly 
tsfill

* compute cumulative time spent in employment and lagged values
bysort id (spell_date): gen cum_empl = sum(employed) if employed==1
bysort id (spell_date): replace cum_empl = cum_empl[_n-1] if cum_empl==.
bysort id (spell_date): gen lag_7  = L7.cum_empl  if employed==0  
bysort id (spell_date): gen lag_24 = L25.cum_empl if employed==0
bysort id (spell_date): gen lag_48 = L49.cum_empl if employed==0
qui replace lag_7=0  if lag_7==.  & employed==0  // fix computation for first spell of each "id" (if not enough time to go back with "L.")
qui replace lag_24=0 if lag_24==. & employed==0  
qui replace lag_48=0 if lag_48==. & employed==0  

* compute time spent in employment in the last 6, 24, 48 months, at the beginning of each unemployment spell
bysort id (spell_date): gen m6  = cum_empl - lag_7  if employed==0  
bysort id (spell_date): gen m24 = cum_empl - lag_24 if employed==0
bysort id (spell_date): gen m48 = cum_empl - lag_48 if employed==0
qui drop if (spellnr==.)
qui bysort id spellnr (spell_date): keep if _n == 1
drop spell_date cum_empl lag_*

list

This works fine, but becomes quite inefficient when using (several millions of) daily data. Can you suggest any alternative approach that does not involve expanding the dataset?

In words what I do above is:

1. I expand data to have one row per month;
2. I fill-in the "gaps" in between the spells with -tsfill-
3. I Compute the running time spent in employment, and use lag operators to get the three quantities of interest.

This is in the vein of what done here, in a past question that I posted. However the working example there was unnecessarily complicated and with some mistakes.

---

SOLUTIONS PERFORMANCE

I tried different approaches suggested in the accepted answer below (including using joinby as suggested in an earlier version of the answer). In order to create a larger dataset I used:

expand 500000
bysort id spellnr: gen new_id = _n
drop id 
rename new_id id

which creates a dataset with 500,000 id's (for a total of 3,500,000 spells). The first solution largely dominates the ones that use joinby or rangejoin (see also the comments to the accepted answer below).

Answer 1

Below code might save some running time.

bys id (employed): gen tag = _n if !employed
sum tag, meanonly
local maxtag = `r(max)'

foreach i in 6 24 48 {
gen m`i' = .

    forval d = 1/`maxtag' {
    by id: gen x = 1 + min(bdate[`d'],edate) - max(bdate[`d']-`i',bdate) if employed
    egen y = total(x*(x>0)), by(id)
    replace m`i' = y if tag == `d'
    drop x y
    }
}
sort id bdate

The same logic, along with -rangejoin- (ssc) should also deserve a try. Please kindly provide some feedback after testing with your (large) actual data.

preserve
    keep if employed
    replace employed = 0
    tempfile em
    save `em'
restore

foreach i in 6 24 48 {
gen _bd = bdate - `i'
rangejoin edate _bd bdate using `em', by(id employed) p(_)

egen m`i' = total(_edate - max(_bd,_bdate)+1) if !employed, by(id bdate)
bys id bdate: keep if _n==1
drop _*
}