Working with date ranges – creating a flag in panel

I have two datasets:

1. Panel – years from 2010 to 2020
2. Ranges for each firm (one or many) when they took a loan – also the range indicates the loan duration

First can look like this (each company has all the year-observation records):

   id  year
   1   2010
   1   ...
   1   2020
   2   2010
   2   ...
   2   2020

Second can look like this (so all variety, a company can have all years with a loan, some gaps in the beginning and in the end):

   id start end
   1  2010  2011
   1  2011  2014
   1  2017  2018
   1  2012  2020
   2  2014  2018
   3  2011  2012
   3  2015  2018
   3  2018  2020
   4  2011  2012
   4  2015  2018
   4  2010  2018

The idea is to merge the two, so each company gets 0 or 1 for a year, 1 if there was a loan that year (so any of the ranges matching), and 0 if a company didn’t have a loan that year.

Example company based on the above would look like this:

   id  year  flag
   3   2010    0
   3   2011    1
   3   2012    1
   3   2013    0
   3   2014    0
   3   2015    1
   3   2016    1
   3   2017    1
   3   2018    1
   3   2019    1
   3   2020    1

Hope that makes sense. I tried inrange() but there are too many different scenarios and my code gets messy, thought there is a simple and clean way to do it.

>Solution :

If you work on the second dataset, you can get something fit to merge with your main dataset.

clear 
input id start end
1  2010  2011
1  2011  2014
1  2017  2018
1  2012  2020
2  2014  2018
3  2011  2012
3  2015  2018
3  2018  2020
4  2011  2012
4  2015  2018
end 

gen long ID = _n 
gen toexpand = end - start + 1
expand toexpand 
bysort ID : gen year = start + _n - 1 
drop start end ID toexpand 
duplicates drop id year, force 
sort id year 
list, sepby(id)

    +-----------+
     | id   year |
     |-----------|
  1. |  1   2010 |
  2. |  1   2011 |
  3. |  1   2012 |
  4. |  1   2013 |
  5. |  1   2014 |
  6. |  1   2015 |
  7. |  1   2016 |
  8. |  1   2017 |
  9. |  1   2018 |
 10. |  1   2019 |
 11. |  1   2020 |
     |-----------|
 12. |  2   2014 |
 13. |  2   2015 |
 14. |  2   2016 |
 15. |  2   2017 |
 16. |  2   2018 |
     |-----------|
 17. |  3   2011 |
 18. |  3   2012 |
 19. |  3   2015 |
 20. |  3   2016 |
 21. |  3   2017 |
 22. |  3   2018 |
 23. |  3   2019 |
 24. |  3   2020 |
     |-----------|
 25. |  4   2011 |
 26. |  4   2012 |
 27. |  4   2015 |
 28. |  4   2016 |
 29. |  4   2017 |
 30. |  4   2018 |
     +-----------+

After the merge 1:1 id year it is

gen flag = _merge == 3 
```