Tsfill

[Juliana Cunha]

Dear All, I have two datasets that I want to merge to construct an event study, but I am not sure if I should use TSFILL in this case.I first need to count number of times the individual committed crime in a month. Then, merge both data sets. But as I don't have a proper panel in either data sets, I am not sure if I can use TSFILL or anything else.Do you have any suggestions?

* Example generated by -dataex-. For more info, type help dataex
clear
input float var1 str10 date_ill float ill
 1 ""           0
 1 "05/06/2019" 1
 1 ""           0
 2 ""           0
 3 "25/12/2020" 1
 3 "18/02/2021" 1
 4 "07/09/2018" 1
 4 ""           0
 4 ""           0
 5 ""           0
 5 ""           0
 5 ""           0
 6 ""           0
 6 "20/10/2021" 1
 7 ""           0
 8 ""           0
 9 "11/06/2018" 1
10 ""           0
end

* Example generated by -dataex-. For more info, type help dataex
clear
input float id str9 crimedate float(month day year)
1 "02Jan2018" 1  2 2018
2 "15Feb2018" 2 15 2018
3 "17Aug2019" 8 17 2019
4 "22Feb2018" 2 22 2018
4 "25Feb2018" 2 25 2018
5 "13Mar2021" 3 13 2021
5 "25Mar2021" 3 25 2021
5 "10Jul2022" 7 10 2022
6 "12Feb2018" 2 12 2018
7 "11Jan2019" 1 11 2019
end

[Nick Cox]

Here is some technique based on your data example:

. * Example generated by -dataex-. For more info, type help dataex
. clear

. input float id str9 crimedate float(month day year)

            id  crimedate      month        day       year
  1. 1 "02Jan2018" 1  2 2018
  2. 2 "15Feb2018" 2 15 2018
  3. 3 "17Aug2019" 8 17 2019
  4. 4 "22Feb2018" 2 22 2018
  5. 4 "25Feb2018" 2 25 2018
  6. 5 "13Mar2021" 3 13 2021
  7. 5 "25Mar2021" 3 25 2021
  8. 5 "10Jul2022" 7 10 2022
  9. 6 "12Feb2018" 2 12 2018
 10. 7 "11Jan2019" 1 11 2019
 11. end

. 
. gen mdate = ym(year, month)

. contract mdate

. list 

     +---------------+
     | mdate   _freq |
     |---------------|
  1. |   696       1 |
  2. |   697       4 |
  3. |   708       1 |
  4. |   715       1 |
  5. |   734       2 |
     |---------------|
  6. |   750       1 |
     +---------------+

. 
. tsset mdate 

Time variable: mdate, 696 to 750, but with gaps
        Delta: 1 unit

. tsfill 

. replace _freq = 0 if missing(_freq)
(49 real changes made)

. 
. format mdate %tm

. list

     +-----------------+
     |   mdate   _freq |
     |-----------------|
  1. |  2018m1       1 |
  2. |  2018m2       4 |
  3. |  2018m3       0 |
  4. |  2018m4       0 |
  5. |  2018m5       0 |
     |-----------------|
  6. |  2018m6       0 |
  7. |  2018m7       0 |
  8. |  2018m8       0 |
  9. |  2018m9       0 |
 10. | 2018m10       0 |
     |-----------------|
 11. | 2018m11       0 |
 12. | 2018m12       0 |
 13. |  2019m1       1 |
 14. |  2019m2       0 |
 15. |  2019m3       0 |
     |-----------------|
 16. |  2019m4       0 |
 17. |  2019m5       0 |
 18. |  2019m6       0 |
 19. |  2019m7       0 |
 20. |  2019m8       1 |
     |-----------------|
 21. |  2019m9       0 |
 22. | 2019m10       0 |
 23. | 2019m11       0 |
 24. | 2019m12       0 |
 25. |  2020m1       0 |
     |-----------------|
 26. |  2020m2       0 |
 27. |  2020m3       0 |
 28. |  2020m4       0 |
 29. |  2020m5       0 |
 30. |  2020m6       0 |
     |-----------------|
 31. |  2020m7       0 |
 32. |  2020m8       0 |
 33. |  2020m9       0 |
 34. | 2020m10       0 |
 35. | 2020m11       0 |
     |-----------------|
 36. | 2020m12       0 |
 37. |  2021m1       0 |
 38. |  2021m2       0 |
 39. |  2021m3       2 |
 40. |  2021m4       0 |
     |-----------------|
 41. |  2021m5       0 |
 42. |  2021m6       0 |
 43. |  2021m7       0 |
 44. |  2021m8       0 |
 45. |  2021m9       0 |
     |-----------------|
 46. | 2021m10       0 |
 47. | 2021m11       0 |
 48. | 2021m12       0 |
 49. |  2022m1       0 |
 50. |  2022m2       0 |
     |-----------------|
 51. |  2022m3       0 |
 52. |  2022m4       0 |
 53. |  2022m5       0 |
 54. |  2022m6       0 |
 55. |  2022m7       1 |
     +-----------------+