Summing variables based on specific conditions

[Kostov Daniel]

Dear Statalist Members,


I really appreciate this platform and I hope someone can help me out with a problem. Currently I am working in a data set were I need to sum up values in the column (percentage_shares_repur) before a specific event/date in another column (DateAnnounced). Please take a look at the example below.

-----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input double date str8 cusip float percentage_shares_repur long DateAnnounced float i
 6998 "00036110"         .     .  1
 7090 "00036110"         0     .  2
 7182 "00036110"         0     .  3
 7273 "00036110"         0     .  4
 7364 "00036110"         0     .  5
 7456 "00036110"         0     .  6
 7548 "00036110"         0     .  7
 7639 "00036110"         0     .  8
 7729 "00036110"         0     .  9
 7821 "00036110"         0     . 10
 7913 "00036110"         0     . 11
 8004 "00036110" 1.5261446     . 12
 8094 "00036110"         0     . 13
 8186 "00036110"         0     . 14
 8278 "00036110"   .761035     . 15
 8369 "00036110"         0     . 16
 8459 "00036110"         0     . 17
 8551 "00036110"         0     . 18
 8643 "00036110"         0     . 19
 8734 "00036110"         0     . 20
 8825 "00036110"         0     . 21
 8917 "00036110"         0     . 22
 9009 "00036110"         0     . 23
 9100 "00036110"         0     . 24
 9190 "00036110"         0     . 25
 9282 "00036110"         0     . 26
 9374 "00036110"         0     . 27
 9465 "00036110"         0     . 28
 9555 "00036110"         0     . 29
 9647 "00036110"         0     . 30
 9739 "00036110"         0     . 31
 9830 "00036110"         0     . 32
 9920 "00036110"         0     . 33
10012 "00036110"         0     . 34
10104 "00036110"         0     . 35
10195 "00036110"         0     . 36
10286 "00036110"         0     . 37
10378 "00036110"         0     . 38
10470 "00036110"         0     . 39
10561 "00036110"         0     . 40
10651 "00036110"         0     . 41
10743 "00036110"  .0873744     . 42
10835 "00036110"         0     . 43
10926 "00036110"         0     . 44
11016 "00036110"         0     . 45
11108 "00036110"         0     . 46
11200 "00036110"         0     . 47
11221 "00036110"         . 11221 48
11291 "00036110" 1.2868333     . 49
11381 "00036110"         0     . 50
11473 "00036110"         0     . 51
11565 "00036110"         0     . 52
11656 "00036110"         0     . 53
 7821 "00103010"         .     .  1
 7913 "00103010"         .     .  2
 8004 "00103010"         .     .  3
 8094 "00103010"         .     .  4
 8186 "00103010"         0     .  5
 8278 "00103010"         0     .  6
 8369 "00103010"         0     .  7
 8459 "00103010"         0     .  8
 8551 "00103010"         0     .  9
 8643 "00103010"         0     . 10
 8734 "00103010"         0     . 11
 8825 "00103010"         0     . 12
 8917 "00103010" .48709205     . 13
 8958 "00103010"         .  8958 14
 9009 "00103010"         0     . 15
 9100 "00103010"         0     . 16
 9190 "00103010"         0     . 17
 9282 "00103010"         0     . 18
 9374 "00103010"         0     . 19
 9465 "00103010"         0     . 20
 9555 "00103010"         0     . 21
 9647 "00103010" 1.7903365     . 22
 9739 "00103010"  2.547771     . 23
 9830 "00103010"  .1352265     . 24
 9920 "00103010" 1.2186866     . 25
10012 "00103010"  4.021019     . 26
10104 "00103010"         0     . 27
10195 "00103010"  3.189717     . 28
10286 "00103010" 2.3604622     . 29
10378 "00103010"         0     . 30
10470 "00103010"         0     . 31
10561 "00103010"         0     . 32
10651 "00103010"  1.536137     . 33
10743 "00103010" .46035805     . 34
10835 "00103010"         0     . 35
10926 "00103010"         0     . 36
11016 "00103010"         0     . 37
11108 "00103010"         0     . 38
11200 "00103010"         0     . 39
11291 "00103010"         0     . 40
11381 "00103010"  .2569373     . 41
11473 "00103010"         0     . 42
11565 "00103010"         0     . 43
11656 "00103010"         0     . 44
 9131 "00190710"         .     .  1
 9221 "00190710"         0     .  2
 9312 "00190710"         0     .  3
end
format %d date
format %d DateAnnounced

------------------
Listed 100 out of 38546 observation

What I would like is to sum up share repurchases before a DateAnnonced by cusip. The tricky part is that some cusip have multiple DateAnnounced and therefore I had problems using inrange function.
To make the example more concrete. In my attached data set. The sum of (percentage_shares_repur) for cusip "00036110" should become 1.526 + .761+0.0873 =2.3743 (until the DateAnnounced). If we have more DateAnnounced for a certain cusip it needs to sum the values between these DateAnnounced.

Could you please suggest a solution to this problem.


Kind Regards,
Daniel

[Nick Cox]

The essence here is that you have spells that end with an announcement within which you want to sum a variable (cumulatively?) .

For much more on spells, try

search spell, sj

including

SJ-15-1 dm0079 . . . . . . . . . . . . . . . Stata tip 123: Spell boundaries
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q1/15 SJ 15(1):319--323 (no commands)
shows how to identify spells

SJ-7-2 dm0029 . . . . . . . . . . . . . . Speaking Stata: Identifying spells
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q2/07 SJ 7(2):249--265 (no commands)
shows how to handle spells with complete control over
spell specification

Your criterion can be turned around: the start of a new spell is either just after an announcement. or the beginning of the record (if that wasn't an announcement date).

So. after your helpful example code I suggest

local DA DateAnnounced 
bysort cusip (date) : gen spell = sum((_n == 1 & missing(`DA'))| !missing(`DA'[_n-1])) 
bysort cusip spell (date) : gen wanted = sum(percentage_shares) 

list, sepby(cusip spell) 

     +---------------------------------------------------------------------+
     |      date      cusip   percen~r   DateAnn~d    i   spell     wanted |
     |---------------------------------------------------------------------|
  1. | 28feb1979   00036110          .           .    1       1          0 |
  2. | 31may1979   00036110          0           .    2       1          0 |
  3. | 31aug1979   00036110          0           .    3       1          0 |
  4. | 30nov1979   00036110          0           .    4       1          0 |
  5. | 29feb1980   00036110          0           .    5       1          0 |
  6. | 31may1980   00036110          0           .    6       1          0 |
  7. | 31aug1980   00036110          0           .    7       1          0 |
  8. | 30nov1980   00036110          0           .    8       1          0 |
  9. | 28feb1981   00036110          0           .    9       1          0 |
 10. | 31may1981   00036110          0           .   10       1          0 |
 11. | 31aug1981   00036110          0           .   11       1          0 |
 12. | 30nov1981   00036110   1.526145           .   12       1   1.526145 |
 13. | 28feb1982   00036110          0           .   13       1   1.526145 |
 14. | 31may1982   00036110          0           .   14       1   1.526145 |
 15. | 31aug1982   00036110    .761035           .   15       1    2.28718 |
 16. | 30nov1982   00036110          0           .   16       1    2.28718 |
 17. | 28feb1983   00036110          0           .   17       1    2.28718 |
 18. | 31may1983   00036110          0           .   18       1    2.28718 |
 19. | 31aug1983   00036110          0           .   19       1    2.28718 |
 20. | 30nov1983   00036110          0           .   20       1    2.28718 |
 21. | 29feb1984   00036110          0           .   21       1    2.28718 |
 22. | 31may1984   00036110          0           .   22       1    2.28718 |
 23. | 31aug1984   00036110          0           .   23       1    2.28718 |
 24. | 30nov1984   00036110          0           .   24       1    2.28718 |
 25. | 28feb1985   00036110          0           .   25       1    2.28718 |
 26. | 31may1985   00036110          0           .   26       1    2.28718 |
 27. | 31aug1985   00036110          0           .   27       1    2.28718 |
 28. | 30nov1985   00036110          0           .   28       1    2.28718 |
 29. | 28feb1986   00036110          0           .   29       1    2.28718 |
 30. | 31may1986   00036110          0           .   30       1    2.28718 |
 31. | 31aug1986   00036110          0           .   31       1    2.28718 |
 32. | 30nov1986   00036110          0           .   32       1    2.28718 |
 33. | 28feb1987   00036110          0           .   33       1    2.28718 |
 34. | 31may1987   00036110          0           .   34       1    2.28718 |
 35. | 31aug1987   00036110          0           .   35       1    2.28718 |
 36. | 30nov1987   00036110          0           .   36       1    2.28718 |
 37. | 29feb1988   00036110          0           .   37       1    2.28718 |
 38. | 31may1988   00036110          0           .   38       1    2.28718 |
 39. | 31aug1988   00036110          0           .   39       1    2.28718 |
 40. | 30nov1988   00036110          0           .   40       1    2.28718 |
 41. | 28feb1989   00036110          0           .   41       1    2.28718 |
 42. | 31may1989   00036110   .0873744           .   42       1   2.374554 |
 43. | 31aug1989   00036110          0           .   43       1   2.374554 |
 44. | 30nov1989   00036110          0           .   44       1   2.374554 |
 45. | 28feb1990   00036110          0           .   45       1   2.374554 |
 46. | 31may1990   00036110          0           .   46       1   2.374554 |
 47. | 31aug1990   00036110          0           .   47       1   2.374554 |
 48. | 21sep1990   00036110          .   21sep1990   48       1   2.374554 |
     |---------------------------------------------------------------------|
 49. | 30nov1990   00036110   1.286833           .   49       2   1.286833 |
 50. | 28feb1991   00036110          0           .   50       2   1.286833 |
 51. | 31may1991   00036110          0           .   51       2   1.286833 |
 52. | 31aug1991   00036110          0           .   52       2   1.286833 |
 53. | 30nov1991   00036110          0           .   53       2   1.286833 |
     |---------------------------------------------------------------------|
 54. | 31may1981   00103010          .           .    1       1          0 |
 55. | 31aug1981   00103010          .           .    2       1          0 |
 56. | 30nov1981   00103010          .           .    3       1          0 |
 57. | 28feb1982   00103010          .           .    4       1          0 |
 58. | 31may1982   00103010          0           .    5       1          0 |
 59. | 31aug1982   00103010          0           .    6       1          0 |
 60. | 30nov1982   00103010          0           .    7       1          0 |
 61. | 28feb1983   00103010          0           .    8       1          0 |
 62. | 31may1983   00103010          0           .    9       1          0 |
 63. | 31aug1983   00103010          0           .   10       1          0 |
 64. | 30nov1983   00103010          0           .   11       1          0 |
 65. | 29feb1984   00103010          0           .   12       1          0 |
 66. | 31may1984   00103010    .487092           .   13       1    .487092 |
 67. | 11jul1984   00103010          .   11jul1984   14       1    .487092 |
     |---------------------------------------------------------------------|
 68. | 31aug1984   00103010          0           .   15       2          0 |
 69. | 30nov1984   00103010          0           .   16       2          0 |
 70. | 28feb1985   00103010          0           .   17       2          0 |
 71. | 31may1985   00103010          0           .   18       2          0 |
 72. | 31aug1985   00103010          0           .   19       2          0 |
 73. | 30nov1985   00103010          0           .   20       2          0 |
 74. | 28feb1986   00103010          0           .   21       2          0 |
 75. | 31may1986   00103010   1.790336           .   22       2   1.790336 |
 76. | 31aug1986   00103010   2.547771           .   23       2   4.338108 |
 77. | 30nov1986   00103010   .1352265           .   24       2   4.473334 |
 78. | 28feb1987   00103010   1.218687           .   25       2    5.69202 |
 79. | 31may1987   00103010   4.021019           .   26       2   9.713039 |
 80. | 31aug1987   00103010          0           .   27       2   9.713039 |
 81. | 30nov1987   00103010   3.189717           .   28       2   12.90276 |
 82. | 29feb1988   00103010   2.360462           .   29       2   15.26322 |
 83. | 31may1988   00103010          0           .   30       2   15.26322 |
 84. | 31aug1988   00103010          0           .   31       2   15.26322 |
 85. | 30nov1988   00103010          0           .   32       2   15.26322 |
 86. | 28feb1989   00103010   1.536137           .   33       2   16.79936 |
 87. | 31may1989   00103010   .4603581           .   34       2   17.25971 |
 88. | 31aug1989   00103010          0           .   35       2   17.25971 |
 89. | 30nov1989   00103010          0           .   36       2   17.25971 |
 90. | 28feb1990   00103010          0           .   37       2   17.25971 |
 91. | 31may1990   00103010          0           .   38       2   17.25971 |
 92. | 31aug1990   00103010          0           .   39       2   17.25971 |
 93. | 30nov1990   00103010          0           .   40       2   17.25971 |
 94. | 28feb1991   00103010   .2569373           .   41       2   17.51665 |
 95. | 31may1991   00103010          0           .   42       2   17.51665 |
 96. | 31aug1991   00103010          0           .   43       2   17.51665 |
 97. | 30nov1991   00103010          0           .   44       2   17.51665 |
     |---------------------------------------------------------------------|
 98. | 31dec1984   00190710          .           .    1       1          0 |
 99. | 31mar1985   00190710          0           .    2       1          0 |
100. | 30jun1985   00190710          0           .    3       1          0 |
     +---------------------------------------------------------------------+

[Kostov Daniel]

Thank you for your assistance Mr. Cox. The code worked smoothly!