Calculate mean within a specified interval defined by a certain variable

[Alberto Alvarez]

Dear Stata Users,

I have the data below, where "gvkey" is a firm ID, "begin" is a variable that specifies when the year starts and "end" specified when year ends. "final_date" includes the starting and ending dates of a year. I need to calculate the mean of "ret" from beginning date till ending date (both date should be inluded in calculation). If there are missing observations as at the beginning (no beginning date) or at the end (no end date) this observations should be left untreated. Please, help me with this issue.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double ret str6 gvkey float(end begin final_date)
  -.1849214732646942 "001004"   .   .   .
 -.21935483813285828 "001004"   .   .   .
     .17768594622612 "001004"   .   .   .
  .07143859565258026 "001004"   .   .   .
  .03947368264198303 "001004" 472   . 472
   .1487341821193695 "001004"   . 473 473
 -.06787879019975662 "001004"   .   .   .
 .014836795628070831 "001004"   .   .   .
 -.15789473056793213 "001004"   .   .   .
  -.0681944414973259 "001004"   .   .   .
 -.01123595517128706 "001004"   .   .   .
  .08712121099233627 "001004"   .   .   .
-.009198606014251709 "001004"   .   .   .
   .3427561819553375 "001004"   .   .   .
  -.2973684072494507 "001004"   .   .   .
 -.09228464215993881 "001004"   .   .   .
 -.07883817702531815 "001004" 484   . 484
 -.13513512909412384 "001004"   . 485 485
 .007083333563059568 "001004"   .   .   .
              -.0625 "001004"   .   .   .
  .01666666753590107 "001004"   .   .   .
 .051147542893886566 "001004"   .   .   .
 -.13089005649089813 "001004"   .   .   .
  .21686747670173645 "001004"   .   .   .
  .20990096032619476 "001004"   .   .   .
  -.1046740785241127 "001004"   .   .   .
 -.06176471710205078 "001004"   .   .   .
 -.11324456334114075 "001004"   .   .   .
  .24666078388690948 "001004" 496   . 496
  .22142860293388367 "001004"   . 497 497
-.028947362676262856 "001004"   .   .   .
  .03268758952617645 "001004"   .   .   .
  -.5304806232452393 "001004"   .   .   .
-.048064958304166794 "001004"   .   .   .
  .07236838340759277 "001004"   .   .   .
   .1055215522646904 "001004"   .   .   .
 -.04273028299212456 "001004"   .   .   .
 -.13953490555286407 "001004"   .   .   .
  .45540544390678406 "001004"   .   .   .
  .18291543424129486 "001004"   .   .   .
  -.1000785082578659 "001004" 508   . 508
 -.10839159041643143 "001004"   . 509 509
  -.1406862735748291 "001004"   .   .   .
 -.29061785340309143 "001004"   .   .   .
 -.23387093842029572 "001004"   .   .   .
  -.1768420934677124 "001004"   .   .   .
   .4066495895385742 "001004"   .   .   .
 -.06363634765148163 "001004"   .   .   .
 -.07572812587022781 "001004"   .   .   .
 -.06512613594532013 "001004"   .   .   .
 -.15056176483631134 "001004"   .   .   .
 .026455065235495567 "001004"   .   .   .
  .15979377925395966 "001004" 520   . 520
   .5688889026641846 "001004"   . 521 521
   .1473088413476944 "001004"   .   .   .
-.011111129075288773 "001004"   .   .   .
.0012484679464250803 "001004"   .   .   .
  .35910212993621826 "001004"   .   .   .
 -.04036693647503853 "001004"   .   .   .
  .42925429344177246 "001004"   .   .   .
 -.10702337324619293 "001004"   .   .   .
 -.03220976144075394 "001004"   .   .   .
 -.04953562840819359 "001004"   .   .   .
 -.17345277965068817 "001004"   .   .   .
 -.05615760758519173 "001004" 532   . 532
   .1847599595785141 "001005"   . 533 533
  -.0837005078792572 "001005"   .   .   .
 .028846172615885735 "001005"   .   .   .
  .16355140507221222 "001005"   .   .   .
 -.05622488632798195 "001005"   .   .   .
  .16340425610542297 "001005"   .   .   .
-.003657658351585269 "001005"   .   .   .
   -.144640251994133 "001005"   .   .   .
-.042918454855680466 "001005"   .   .   .
  .21973101794719696 "001005"   .   .   .
  .08308817446231842 "001005"   .   .   .
  .08893424272537231 "001005" 544   . 544
 -.02057361975312233 "001005"   . 545 545
  .14385737478733063 "001005"   .   .   .
-.020589813590049744 "001005"   .   .   .
-.023863639682531357 "001005"   .   .   .
 -.07275901734828949 "001005"   .   .   .
  .31450095772743225 "001005"   .   .   .
  .14374403655529022 "001005"   .   .   .
-.005010473076254129 "001005"   .   .   .
  .06210656836628914 "001005"   .   .   .
  .12524694204330444 "001005"   .   .   .
 -.06320222467184067 "001005"   .   .   .
 -.09745128452777863 "001005" 556   . 556
 -.07682725787162781 "001005"   . 557 557
   .0652272030711174 "001005"   .   .   .
 -.05785476416349411 "001005"   .   .   .
  .06857914477586746 "001005"   .   .   .
  .09228190779685974 "001005"   .   .   .
 .021121321246027946 "001005"   .   .   .
  .09778113663196564 "001005"   .   .   .
  .02055499702692032 "001005"   .   .   .
-.023833533748984337 "001005"   .   .   .
 -.05226961523294449 "001005"   .   .   .
  .10812777280807495 "001005"   .   .   .
end
format %tm end
format %tm begin
format %tm final_date

[Nick Cox]

You're formatting the dates as monthly dates but that seems to make little sense. As I read it it implies that begin and end date are 1 month different, so the period to be summarized is 2 months long. On the other hand it appears that you want yearly summaries of monthly data.

I suspect that unclear explanation is the reason there have been no replies previously.

This may help, and if not you should explain why not.

gen BEGIN = begin 
sort gvkey, stable 
by gvkey : replace BEGIN = BEGIN[_n-1] if missing(BEGIN) 

egen mean = mean(ret), by(gvkey BEGIN) 

list, sepby(gvkey BEGIN) 

     +----------------------------------------------------------------------+
     |        ret    gvkey      end    begin   final_~e   BEGIN        mean |
     |----------------------------------------------------------------------|
  1. | -.18492147   001004        .        .          .       .   -.0231356 |
  2. | -.21935484   001004        .        .          .       .   -.0231356 |
  3. |  .17768595   001004        .        .          .       .   -.0231356 |
  4. |   .0714386   001004        .        .          .       .   -.0231356 |
  5. |  .03947368   001004   1999m5        .     1999m5       .   -.0231356 |
     |----------------------------------------------------------------------|
  6. |  .14873418   001004        .   1999m6     1999m6     473   -.0157871 |
  7. | -.06787879   001004        .        .          .     473   -.0157871 |
  8. |   .0148368   001004        .        .          .     473   -.0157871 |
  9. | -.15789473   001004        .        .          .     473   -.0157871 |
 10. | -.06819444   001004        .        .          .     473   -.0157871 |
 11. | -.01123596   001004        .        .          .     473   -.0157871 |
 12. |  .08712121   001004        .        .          .     473   -.0157871 |
 13. | -.00919861   001004        .        .          .     473   -.0157871 |
 14. |  .34275618   001004        .        .          .     473   -.0157871 |
 15. | -.29736841   001004        .        .          .     473   -.0157871 |
 16. | -.09228464   001004        .        .          .     473   -.0157871 |
 17. | -.07883818   001004   2000m5        .     2000m5     473   -.0157871 |
     |----------------------------------------------------------------------|
 18. | -.13513513   001004        .   2000m6     2000m6     485    .0116765 |
 19. |  .00708333   001004        .        .          .     485    .0116765 |
 20. |     -.0625   001004        .        .          .     485    .0116765 |
 21. |  .01666667   001004        .        .          .     485    .0116765 |
 22. |  .05114754   001004        .        .          .     485    .0116765 |
 23. | -.13089006   001004        .        .          .     485    .0116765 |
 24. |  .21686748   001004        .        .          .     485    .0116765 |
 25. |  .20990096   001004        .        .          .     485    .0116765 |
 26. | -.10467408   001004        .        .          .     485    .0116765 |
 27. | -.06176472   001004        .        .          .     485    .0116765 |
 28. | -.11324456   001004        .        .          .     485    .0116765 |
 29. |  .24666078   001004   2001m5        .     2001m5     485    .0116765 |
     |----------------------------------------------------------------------|
 30. |   .2214286   001004        .   2001m6     2001m6     497    .0150409 |
 31. | -.02894736   001004        .        .          .     497    .0150409 |
 32. |  .03268759   001004        .        .          .     497    .0150409 |
 33. | -.53048062   001004        .        .          .     497    .0150409 |
 34. | -.04806496   001004        .        .          .     497    .0150409 |
 35. |  .07236838   001004        .        .          .     497    .0150409 |
 36. |  .10552155   001004        .        .          .     497    .0150409 |
 37. | -.04273028   001004        .        .          .     497    .0150409 |
 38. | -.13953491   001004        .        .          .     497    .0150409 |
 39. |  .45540544   001004        .        .          .     497    .0150409 |
 40. |  .18291543   001004        .        .          .     497    .0150409 |
 41. | -.10007851   001004   2002m5        .     2002m5     497    .0150409 |
     |----------------------------------------------------------------------|
 42. | -.10839159   001004        .   2002m6     2002m6     509   -.0593802 |
 43. | -.14068627   001004        .        .          .     509   -.0593802 |
 44. | -.29061785   001004        .        .          .     509   -.0593802 |
 45. | -.23387094   001004        .        .          .     509   -.0593802 |
 46. | -.17684209   001004        .        .          .     509   -.0593802 |
 47. |  .40664959   001004        .        .          .     509   -.0593802 |
 48. | -.06363635   001004        .        .          .     509   -.0593802 |
 49. | -.07572813   001004        .        .          .     509   -.0593802 |
 50. | -.06512614   001004        .        .          .     509   -.0593802 |
 51. | -.15056176   001004        .        .          .     509   -.0593802 |
 52. |  .02645507   001004        .        .          .     509   -.0593802 |
 53. |  .15979378   001004   2003m5        .     2003m5     509   -.0593802 |
     |----------------------------------------------------------------------|
 54. |   .5688889   001004        .   2003m6     2003m6     521    .0863288 |
 55. |  .14730884   001004        .        .          .     521    .0863288 |
 56. | -.01111113   001004        .        .          .     521    .0863288 |
 57. |  .00124847   001004        .        .          .     521    .0863288 |
 58. |  .35910213   001004        .        .          .     521    .0863288 |
 59. | -.04036694   001004        .        .          .     521    .0863288 |
 60. |  .42925429   001004        .        .          .     521    .0863288 |
 61. | -.10702337   001004        .        .          .     521    .0863288 |
 62. | -.03220976   001004        .        .          .     521    .0863288 |
 63. | -.04953563   001004        .        .          .     521    .0863288 |
 64. | -.17345278   001004        .        .          .     521    .0863288 |
 65. | -.05615761   001004   2004m5        .     2004m5     521    .0863288 |
     |----------------------------------------------------------------------|
 66. |  .18475996   001005        .   2004m6     2004m6     533    .0500978 |
 67. | -.08370051   001005        .        .          .     533    .0500978 |
 68. |  .02884617   001005        .        .          .     533    .0500978 |
 69. |  .16355141   001005        .        .          .     533    .0500978 |
 70. | -.05622489   001005        .        .          .     533    .0500978 |
 71. |  .16340426   001005        .        .          .     533    .0500978 |
 72. | -.00365766   001005        .        .          .     533    .0500978 |
 73. | -.14464025   001005        .        .          .     533    .0500978 |
 74. | -.04291845   001005        .        .          .     533    .0500978 |
 75. |  .21973102   001005        .        .          .     533    .0500978 |
 76. |  .08308817   001005        .        .          .     533    .0500978 |
 77. |  .08893424   001005   2005m5        .     2005m5     533    .0500978 |
     |----------------------------------------------------------------------|
 78. | -.02057362   001005        .   2005m6     2005m6     545    .0405005 |
 79. |  .14385737   001005        .        .          .     545    .0405005 |
 80. | -.02058981   001005        .        .          .     545    .0405005 |
 81. | -.02386364   001005        .        .          .     545    .0405005 |
 82. | -.07275902   001005        .        .          .     545    .0405005 |
 83. |  .31450096   001005        .        .          .     545    .0405005 |
 84. |  .14374404   001005        .        .          .     545    .0405005 |
 85. | -.00501047   001005        .        .          .     545    .0405005 |
 86. |  .06210657   001005        .        .          .     545    .0405005 |
 87. |  .12524694   001005        .        .          .     545    .0405005 |
 88. | -.06320222   001005        .        .          .     545    .0405005 |
 89. | -.09745128   001005   2006m5        .     2006m5     545    .0405005 |
     |----------------------------------------------------------------------|
 90. | -.07682726   001005        .   2006m6     2006m6     557    .0238989 |
 91. |   .0652272   001005        .        .          .     557    .0238989 |
 92. | -.05785476   001005        .        .          .     557    .0238989 |
 93. |  .06857914   001005        .        .          .     557    .0238989 |
 94. |  .09228191   001005        .        .          .     557    .0238989 |
 95. |  .02112132   001005        .        .          .     557    .0238989 |
 96. |  .09778114   001005        .        .          .     557    .0238989 |
 97. |    .020555   001005        .        .          .     557    .0238989 |
 98. | -.02383353   001005        .        .          .     557    .0238989 |
 99. | -.05226962   001005        .        .          .     557    .0238989 |
100. |  .10812777   001005        .        .          .     557    .0238989 |
     +----------------------------------------------------------------------+