Transform data that has a start and end date into monthly data

[Leo Nard]

Dear all,

I have a dataset that includes the start and end dates of events that I would like to transform into a monthly dataset.

The dataset I have is of the form :

clear
// create example data
input  str1 id str2 nseq datdt datft  str6 start  str6 end_ duration  str1 situation
"1"    "01"    9    10    "JUL-07"    "AUG-07"    2    "6"
"1"    "02"    11    20    "SEP-07"    "JUN-08"    10    "8"
"1"    "03"    21    22    "JUL-08"    "AUG-08"    2    "1"
"1"    "04"    23    34    "SEP-08"    "AUG-09"    12    "3"
"1"    "05"    35    47    "SEP-09"    "SEP-10"    13    "7"
"2"    "01"    9     24    "JUL-07"    "OCT-08"    16    "2"
"2"    "02"    25    32    "NOV-08"    "JUN-09"    8    "1"
"2"    "03"    33    34    "JUL-09"    "AUG-09"    2    "3"
"2"    "04"    35    45    "SEP-09"    "JUL-10"    11    "1"
"2"    "05"    46    47    "AUG-10"    "SEP-10"    2    "9"
end

Data starts in November 2006.
I want to transform it to make 1) event-study and 2) difference-in-differences event study. So I guess the simplest way is in the form :

id month situation
1 1 .
1 2 .
1 3 .
1 4 .
1 5 .
1 6 .
1 7 .
1 8 .
1 9 6
1 10 6
1 11 8
1 12 8
1 13 8
1 14 8
1 15 8
1 16 8
1 17 8
1 18 8
1 19 8
1 20 8
1 21 1

How could I do that?
Thank you in advance!

[Nick Cox]

This may help. I am not clear why blocks of missing values at the beginning are of any use, but you can get them.

clear 

input  str1 id str2 nseq datdt datft  str6 start  str6 end_ duration  str1 situation
"1"    "01"    9    10    "JUL-07"    "AUG-07"    2    "6"
"1"    "02"    11    20    "SEP-07"    "JUN-08"    10    "8"
"1"    "03"    21    22    "JUL-08"    "AUG-08"    2    "1"
"1"    "04"    23    34    "SEP-08"    "AUG-09"    12    "3"
"1"    "05"    35    47    "SEP-09"    "SEP-10"    13    "7"
"2"    "01"    9     24    "JUL-07"    "OCT-08"    16    "2"
"2"    "02"    25    32    "NOV-08"    "JUN-09"    8    "1"
"2"    "03"    33    34    "JUL-09"    "AUG-09"    2    "3"
"2"    "04"    35    45    "SEP-09"    "JUL-10"    11    "1"
"2"    "05"    46    47    "AUG-10"    "SEP-10"    2    "9"
end

gen mstart = monthly(start, "MY", 2025)
gen mend = monthly(end_, "MY", 2025)


expand duration 
bysort id nseq : gen mdate = mstart + _n - 1 
by id nseq : assert mend == mdate[_N]

format mstart mend mdate %tm 

destring id, replace 
tsset id mdate 
su mdate, meanonly 
if r(min) > ym(2006, 11) {
    insobs 1 
    replace mdate = ym(2006, 11) in L 
    su id, meanonly 
    local maxp1 = r(max) + 1 
    replace id = `maxp1' in L 
    tsfill, full 
    drop if id == `maxp1'
}

ds dat* start end_, not 

list `r(varlist)', sepby(id nseq)


     +---------------------------------------------------------------+
     | id   nseq   duration   situat~n    mstart      mend     mdate |
     |---------------------------------------------------------------|
  1. |  1                 .                    .         .   2006m11 |
  2. |  1                 .                    .         .   2006m12 |
  3. |  1                 .                    .         .    2007m1 |
  4. |  1                 .                    .         .    2007m2 |
  5. |  1                 .                    .         .    2007m3 |
  6. |  1                 .                    .         .    2007m4 |
  7. |  1                 .                    .         .    2007m5 |
  8. |  1                 .                    .         .    2007m6 |
     |---------------------------------------------------------------|
  9. |  1     01          2          6    2007m7    2007m8    2007m7 |
 10. |  1     01          2          6    2007m7    2007m8    2007m8 |
     |---------------------------------------------------------------|
 11. |  1     02         10          8    2007m9    2008m6    2007m9 |
 12. |  1     02         10          8    2007m9    2008m6   2007m10 |
 13. |  1     02         10          8    2007m9    2008m6   2007m11 |
 14. |  1     02         10          8    2007m9    2008m6   2007m12 |
 15. |  1     02         10          8    2007m9    2008m6    2008m1 |
 16. |  1     02         10          8    2007m9    2008m6    2008m2 |
 17. |  1     02         10          8    2007m9    2008m6    2008m3 |
 18. |  1     02         10          8    2007m9    2008m6    2008m4 |
 19. |  1     02         10          8    2007m9    2008m6    2008m5 |
 20. |  1     02         10          8    2007m9    2008m6    2008m6 |
     |---------------------------------------------------------------|
 21. |  1     03          2          1    2008m7    2008m8    2008m7 |
 22. |  1     03          2          1    2008m7    2008m8    2008m8 |
     |---------------------------------------------------------------|
 23. |  1     04         12          3    2008m9    2009m8    2008m9 |
 24. |  1     04         12          3    2008m9    2009m8   2008m10 |
 25. |  1     04         12          3    2008m9    2009m8   2008m11 |
 26. |  1     04         12          3    2008m9    2009m8   2008m12 |
 27. |  1     04         12          3    2008m9    2009m8    2009m1 |
 28. |  1     04         12          3    2008m9    2009m8    2009m2 |
 29. |  1     04         12          3    2008m9    2009m8    2009m3 |
 30. |  1     04         12          3    2008m9    2009m8    2009m4 |
 31. |  1     04         12          3    2008m9    2009m8    2009m5 |
 32. |  1     04         12          3    2008m9    2009m8    2009m6 |
 33. |  1     04         12          3    2008m9    2009m8    2009m7 |
 34. |  1     04         12          3    2008m9    2009m8    2009m8 |
     |---------------------------------------------------------------|
 35. |  1     05         13          7    2009m9    2010m9    2009m9 |
 36. |  1     05         13          7    2009m9    2010m9   2009m10 |
 37. |  1     05         13          7    2009m9    2010m9   2009m11 |
 38. |  1     05         13          7    2009m9    2010m9   2009m12 |
 39. |  1     05         13          7    2009m9    2010m9    2010m1 |
 40. |  1     05         13          7    2009m9    2010m9    2010m2 |
 41. |  1     05         13          7    2009m9    2010m9    2010m3 |
 42. |  1     05         13          7    2009m9    2010m9    2010m4 |
 43. |  1     05         13          7    2009m9    2010m9    2010m5 |
 44. |  1     05         13          7    2009m9    2010m9    2010m6 |
 45. |  1     05         13          7    2009m9    2010m9    2010m7 |
 46. |  1     05         13          7    2009m9    2010m9    2010m8 |
 47. |  1     05         13          7    2009m9    2010m9    2010m9 |
     |---------------------------------------------------------------|
 48. |  2                 .                    .         .   2006m11 |
 49. |  2                 .                    .         .   2006m12 |
 50. |  2                 .                    .         .    2007m1 |
 51. |  2                 .                    .         .    2007m2 |
 52. |  2                 .                    .         .    2007m3 |
 53. |  2                 .                    .         .    2007m4 |
 54. |  2                 .                    .         .    2007m5 |
 55. |  2                 .                    .         .    2007m6 |
     |---------------------------------------------------------------|
 56. |  2     01         16          2    2007m7   2008m10    2007m7 |
 57. |  2     01         16          2    2007m7   2008m10    2007m8 |
 58. |  2     01         16          2    2007m7   2008m10    2007m9 |
 59. |  2     01         16          2    2007m7   2008m10   2007m10 |
 60. |  2     01         16          2    2007m7   2008m10   2007m11 |
 61. |  2     01         16          2    2007m7   2008m10   2007m12 |
 62. |  2     01         16          2    2007m7   2008m10    2008m1 |
 63. |  2     01         16          2    2007m7   2008m10    2008m2 |
 64. |  2     01         16          2    2007m7   2008m10    2008m3 |
 65. |  2     01         16          2    2007m7   2008m10    2008m4 |
 66. |  2     01         16          2    2007m7   2008m10    2008m5 |
 67. |  2     01         16          2    2007m7   2008m10    2008m6 |
 68. |  2     01         16          2    2007m7   2008m10    2008m7 |
 69. |  2     01         16          2    2007m7   2008m10    2008m8 |
 70. |  2     01         16          2    2007m7   2008m10    2008m9 |
 71. |  2     01         16          2    2007m7   2008m10   2008m10 |
     |---------------------------------------------------------------|
 72. |  2     02          8          1   2008m11    2009m6   2008m11 |
 73. |  2     02          8          1   2008m11    2009m6   2008m12 |
 74. |  2     02          8          1   2008m11    2009m6    2009m1 |
 75. |  2     02          8          1   2008m11    2009m6    2009m2 |
 76. |  2     02          8          1   2008m11    2009m6    2009m3 |
 77. |  2     02          8          1   2008m11    2009m6    2009m4 |
 78. |  2     02          8          1   2008m11    2009m6    2009m5 |
 79. |  2     02          8          1   2008m11    2009m6    2009m6 |
     |---------------------------------------------------------------|
 80. |  2     03          2          3    2009m7    2009m8    2009m7 |
 81. |  2     03          2          3    2009m7    2009m8    2009m8 |
     |---------------------------------------------------------------|
 82. |  2     04         11          1    2009m9    2010m7    2009m9 |
 83. |  2     04         11          1    2009m9    2010m7   2009m10 |
 84. |  2     04         11          1    2009m9    2010m7   2009m11 |
 85. |  2     04         11          1    2009m9    2010m7   2009m12 |
 86. |  2     04         11          1    2009m9    2010m7    2010m1 |
 87. |  2     04         11          1    2009m9    2010m7    2010m2 |
 88. |  2     04         11          1    2009m9    2010m7    2010m3 |
 89. |  2     04         11          1    2009m9    2010m7    2010m4 |
 90. |  2     04         11          1    2009m9    2010m7    2010m5 |
 91. |  2     04         11          1    2009m9    2010m7    2010m6 |
 92. |  2     04         11          1    2009m9    2010m7    2010m7 |
     |---------------------------------------------------------------|
 93. |  2     05          2          9    2010m8    2010m9    2010m8 |
 94. |  2     05          2          9    2010m8    2010m9    2010m9 |
     +---------------------------------------------------------------+

[Leo Nard]

Thanks a lot Nick, it works perfectly!