Generating a overlap variable based on dates

[Enrique Oriundo]

Hi all,

I have a doubt about how to generate a variable that identifies loans that overlap each other based on the date they started/ended. My dataset is as follows:

clear
input strL uniqueborrowerid float(start_date end_date)
"26DKJJJ13EJ13E26DBJZ13E" 20927 21292
"26DKJJJ13EJ13E26DBJZ13E" 21329 21694
"26DKJJJ13EJ13E26DBJZ13E" 21693 22059
"26Z6613JJDJEILD26"       21206 21296
"26Z6613JJDJEILD26"       21274 21488
"26Z6613JJDJEILD26"       21518 21730
"26DKJJJ13EJ13E26DBJZ13E" 21509 22056
"26DKJJJ13EJ13E26DBJZ13E" 21693 22243
"26Z6613JJDJEILD26"       21158 23074
end
format %td start_date
format %td end_date

So, basically, I want to tag those loans, per borrower ID, that are within the exact period of time or part of them (so, for the first borrower, first row = 0, but the other 2 must be =1 since the end date of one of the loans is greater than the start date of the other, and so on). I don't want to reshape since my dataset has millions of observations and using uniqueborrowerid as key is computationally heavy to do that (more than 600K unique ID).

Thanks!

[Clyde Schechter]

Well, I can think of a few ways to do this without -reshape- that would be even slower and require more memory. But clearly that's not what you want.

Here's an approach that avoids -reshape- and may be more efficient. But the reality is that it's just emulating what -reshape- would do. So it will spare you some of the overhead associated with using -reshape-, but not really the substance:

gen `c(obs_t)' obs_no = _n
expand 2
by obs_no, sort: gen date = cond(_n == 1, start_date, end_date)
by obs_no: gen inout = cond(_n == 1, 1, -1)
by uniqueborrowerid (date inout), sort: gen n_loans = sum(inout) 
by obs_no (inout), sort: replace n_loans = n_loans[1] 
drop inout date
by obs_no, sort: keep if _n == 1

The variable n_loans that gets calculated here will be 0 if a given loan does not overlap with any other loan. If a loan does overlap with some other loan(s) n_loans will be a count of the number of loans it overlaps with (excluding itself). If you just want a 0/1 variable for any overlap, -replace n_loans = min(n_loans, 1)- at the end.

[Nick Cox]

See also

SJ-13-1 dm0068  . . . . . Stata tip 114: Expand paired dates to pairs of dates
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q1/13   SJ 13(1):217--219                                (no commands)
        tip on using expand to deal with paired dates

https://journals.sagepub.com/doi/pdf...867X1301300116

[Hemanshu Kumar]

I think this also does the job (not of counting the number of overlapping loans, but of generating a binary flag), and might be a bit faster than the solution in #2.

(note: I changed the data example a bit, to include a greater variety of situations)

clear
input strL uniqueborrowerid float(start_date end_date)
"26DKJJJ13EJ13E26DBJZ13E" 20927 21292
"26DKJJJ13EJ13E26DBJZ13E" 21329 21694
"26DKJJJ13EJ13E26DBJZ13E" 21693 22049
"26Z6613JJDJEILD26"       21206 21296
"26Z6613JJDJEILD26"       21274 21488
"26Z6613JJDJEILD26"       21518 21730
"26DKJJJ13EJ13E26DBJZ13E" 21509 22056
"26DKJJJ13EJ13E26DBJZ13E" 22053 22243
"26DKJJJ13EJ13E26DBJZ13E" 22250 22300
"26Z6613JJDJEILD26"       21158 23074
end
format %td start_date end_date

sort uniqueborrowerid start_date end_date
by uniqueborrowerid: gen next_loan_start = start_date[_n+1]

by uniqueborrowerid: gen last_loan_end = end_date[_n-1]
by uniqueborrowerid: replace last_loan_end = max(last_loan_end, last_loan_end[_n-1])

gen byte wanted = (next_loan_start <= end_date) | (start_date <= last_loan_end & !missing(last_loan_end))
drop last_loan_end next_loan_start

which produces:

. list, noobs sepby(uniqueborrowerid)

  +----------------------------------------------------------+
  |        uniqueborrowerid   start_d~e    end_date   wanted |
  |----------------------------------------------------------|
  | 26DKJJJ13EJ13E26DBJZ13E   18apr2017   18apr2018        0 |
  | 26DKJJJ13EJ13E26DBJZ13E   25may2018   25may2019        1 |
  | 26DKJJJ13EJ13E26DBJZ13E   21nov2018   21may2020        1 |
  | 26DKJJJ13EJ13E26DBJZ13E   24may2019   14may2020        1 |
  | 26DKJJJ13EJ13E26DBJZ13E   18may2020   24nov2020        1 |
  | 26DKJJJ13EJ13E26DBJZ13E   01dec2020   20jan2021        0 |
  |----------------------------------------------------------|
  |       26Z6613JJDJEILD26   05dec2017   05mar2023        1 |
  |       26Z6613JJDJEILD26   22jan2018   22apr2018        1 |
  |       26Z6613JJDJEILD26   31mar2018   31oct2018        1 |
  |       26Z6613JJDJEILD26   30nov2018   30jun2019        1 |
  +----------------------------------------------------------+