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.

CodePudding user response：

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 
```