loopsdatestataoverlap

Stata: Number of overlapping days within multiple date ranges


I want to calculate the number of overlapping days within multiple date ranges. For example, in the sample data below, there are 167 overlapping days: first from 07jan to 04apr and second from 30may to 15aug.

start          end        
01jan2000    04apr2000 
30may2000    15aug2000
07jan2000    31dec2000

Solution

  • This is fairly crude but gets the job done. Essentially, you

    1. Reshape the data to be in long format, which is usually a good idea when working with panel data in Stata
    2. Fill in gaps between the start and end of each spell
    3. Keep dates that occur more than once
    4. Count the distinct values of dates

    clear

    /* Fake Data */
    input str9(start  end)      
    "01jan2000" "04apr2000"
    "30may2000" "15aug2000"
    "07jan2000" "31dec2000"
    end
    
    foreach var of varlist start end {
        gen d = date(`var', "DMY")
        drop `var'
        gen `var' = d
        format %td `var'
        drop d
    }
    
    /* Count Overlapping Days */
    rename (start end) date=
    gen spell = _n
    reshape long date, i(spell) j(range) string
    drop range
    xtset spell date, delta(1 day)
    tsfill
    bys date: keep if _N>1
    distinct date