Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating different Event Windows, conditional sum of columns

    I want to calculate cumulative returns for several event horizons, in the following data I need to create a sum of xstret (excess returns).

    The requirement is to generate new columns with desired sum, such sume of xstret [-6,0] , xstret [ -13,0] ......so to [-62,0].

    I have got over 4,800 cross-sections/group ids. The following data pertains to first
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input int index long PERMNO int dif float xstret 
    index dif PERMNO xstret
    1 -60 50032 0.006708
    1 -59 50032 0.007209
    1 -58 50032 -0.00596
    1 -57 50032 0.011661
    1 -56 50032 0.020184
    1 -55 50032 -0.00938
    1 -54 50032 0.003783
    1 -53 50032 -0.01444
    1 -52 50032 -0.03474
    1 -51 50032 -0.0209
    1 -50 50032 -9.8E-05
    1 -49 50032 0.014929
    1 -48 50032 0.002364
    1 -47 50032 -0.01301
    1 -46 50032 -0.0088
    1 -45 50032 0.016493
    1 -44 50032 0.001625
    1 -43 50032 0.001018
    1 -42 50032 0.008057
    1 -41 50032 0.009621
    1 -40 50032 -0.00601
    1 -39 50032 0.010867
    1 -38 50032 0.008031
    1 -37 50032 -0.00618
    1 -36 50032 0.007019
    1 -35 50032 0.020857
    1 -34 50032 -0.00794
    1 -33 50032 0.007665
    1 -32 50032 -0.00416
    1 -31 50032 0.021693
    1 -30 50032 0.019179
    1 -29 50032 -0.01019
    1 -28 50032 0.008612
    1 -27 50032 -0.02132
    1 -26 50032 -0.00829
    1 -25 50032 -0.0029
    1 -24 50032 0.010983
    1 -23 50032 0.011594
    1 -22 50032 0.012901
    1 -21 50032 -0.00595
    1 -20 50032 -0.00553
    1 -19 50032 -0.01135
    1 -18 50032 0.008863
    1 -17 50032 0.004165
    1 -16 50032 -0.00425
    1 -15 50032 -0.00415
    1 -14 50032 0.012025
    1 -13 50032 -0.01017
    1 -12 50032 -0.00325
    1 -11 50032 0.006568
    1 -10 50032 -0.00698
    1 -9 50032 0.003846
    1 -8 50032 0.00226
    1 -7 50032 0.004613
    1 -6 50032 0.012321
    1 -5 50032 0.011444
    1 -4 50032 -0.00646
    1 -3 50032 -0.00434
    1 -2 50032 0.00766
    1 -1 50032 0.021366
    1 0 50032 -0.01774
    end
    The following table provides first two cumulative returns
    Code:
     
    index dif PERMNO xstret CAR[-6,0] CAR[-13,0]
    1 -60 50032 0.006708 0.024255 0.021148
    1 -59 50032 0.007209 0.024255 0.021148
    1 -58 50032 -0.00596 0.024255 0.021148
    1 -57 50032 0.011661 0.024255 0.021148
    1 -56 50032 0.020184 0.024255 0.021148
    1 -55 50032 -0.00938 0.024255 0.021148
    1 -54 50032 0.003783 0.024255 0.021148
    1 -53 50032 -0.01444 0.024255 0.021148
    1 -52 50032 -0.03474 0.024255 0.021148
    1 -51 50032 -0.0209 0.024255 0.021148
    1 -50 50032 -9.8E-05 0.024255 0.021148
    1 -49 50032 0.014929 0.024255 0.021148
    1 -48 50032 0.002364 0.024255 0.021148
    1 -47 50032 -0.01301 0.024255 0.021148
    1 -46 50032 -0.0088 0.024255 0.021148
    1 -45 50032 0.016493 0.024255 0.021148
    1 -44 50032 0.001625 0.024255 0.021148
    1 -43 50032 0.001018 0.024255 0.021148
    1 -42 50032 0.008057 0.024255 0.021148
    1 -41 50032 0.009621 0.024255 0.021148
    1 -40 50032 -0.00601 0.024255 0.021148
    1 -39 50032 0.010867 0.024255 0.021148
    1 -38 50032 0.008031 0.024255 0.021148
    1 -37 50032 -0.00618 0.024255 0.021148
    1 -36 50032 0.007019 0.024255 0.021148
    1 -35 50032 0.020857 0.024255 0.021148
    1 -34 50032 -0.00794 0.024255 0.021148
    1 -33 50032 0.007665 0.024255 0.021148
    1 -32 50032 -0.00416 0.024255 0.021148
    1 -31 50032 0.021693 0.024255 0.021148
    1 -30 50032 0.019179 0.024255 0.021148
    1 -29 50032 -0.01019 0.024255 0.021148
    1 -28 50032 0.008612 0.024255 0.021148
    1 -27 50032 -0.02132 0.024255 0.021148
    1 -26 50032 -0.00829 0.024255 0.021148
    1 -25 50032 -0.0029 0.024255 0.021148
    1 -24 50032 0.010983 0.024255 0.021148
    1 -23 50032 0.011594 0.024255 0.021148
    1 -22 50032 0.012901 0.024255 0.021148
    1 -21 50032 -0.00595 0.024255 0.021148
    1 -20 50032 -0.00553 0.024255 0.021148
    1 -19 50032 -0.01135 0.024255 0.021148
    1 -18 50032 0.008863 0.024255 0.021148
    1 -17 50032 0.004165 0.024255 0.021148
    1 -16 50032 -0.00425 0.024255 0.021148
    1 -15 50032 -0.00415 0.024255 0.021148
    1 -14 50032 0.012025 0.024255 0.021148
    1 -13 50032 -0.01017 0.024255 0.021148
    1 -12 50032 -0.00325 0.024255 0.021148
    1 -11 50032 0.006568 0.024255 0.021148
    1 -10 50032 -0.00698 0.024255 0.021148
    1 -9 50032 0.003846 0.024255 0.021148
    1 -8 50032 0.00226 0.024255 0.021148
    1 -7 50032 0.004613 0.024255 0.021148
    1 -6 50032 0.012321 0.024255 0.021148
    1 -5 50032 0.011444 0.024255 0.021148
    1 -4 50032 -0.00646 0.024255 0.021148
    1 -3 50032 -0.00434 0.024255 0.021148
    1 -2 50032 0.00766 0.024255 0.021148
    1 -1 50032 0.021366 0.024255 0.021148
    1 0 50032 -0.01774 0.024255 0.021148

  • #2
    Here are two ways you can do it:

    Code:
    foreach low of numlist 6(7)62 {
        egen double car_`low'_0 = total(cond(inrange(dif, -`low', 0), xstret, .))
    }
    This one works exclusively with official Stata functions, and it's pretty transparent.

    OR

    Code:
     gen high = 0
     gen low = .
     foreach low of numlist 6(7)62 {
        replace low = -`low'
        rangestat (sum) car_`low'_0 = xstret, by(permno) interval(dif low high)
    }
    This requires installing the -rangestat- program, written by Robert Picard, Nick Cox, and Roberto Ferrer, available from SSC. (It is an amazingly useful program for many purposes, worth having in any case, IMO.) This will also run noticeably faster if your data set is very large.

    By the way, your data example is not the actual output of -dataex-, and when used produces a mangled data set that, among other things, lacks the permno variable. It appears that you edited the -dataex- output manually for some reason, and in doing so you broke it. -dataex- output should never be hand edited: the whole point is to guarantee a completely faithful replica of your example data. -dataex- has been thoroughly tested, was originally written by one of the best Stata programmers around (Robert Picard), and is now an official Stata command in versions 15.1 and 14.2. So -dataex- can be relied on to get it right. Editing the output cannot improve upon it.

    Comment


    • #3
      Thank you! it works wonders

      -rangestat- is not only fast it is easier to apply, I agree its very versatile. I did edit the data from dataex as it was incomplete also included a time period that I was not interested in so I had to improvise the bit.

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input int index long PERMNO int dif float xstret
      1 50032 -146  .003754
      1 50032 -145 -.005983
      1 50032 -144 -.007289
      1 50032 -143  .018614
      1 50032 -142 -.011495
      1 50032 -141  .008172
      1 50032 -140  .001683
      1 50032 -139  .014617
      1 50032 -138  .005357
      1 50032 -137  .012488
      1 50032 -136  .004042
      1 50032 -135  .014306
      1 50032 -134  .006223
      1 50032 -133 -.009413
      1 50032 -132 -.020314
      1 50032 -131 -.002465
      1 50032 -130  .007479
      1 50032 -129  .000941
      1 50032 -128  -.03279
      1 50032 -127 -.012821
      1 50032 -126 -.011346
      1 50032 -125  .026955
      1 50032 -124 -.013804
      1 50032 -123 -.005911
      1 50032 -122 -.000873
      1 50032 -121  .011227
      1 50032 -120  .028967
      1 50032 -119  .021669
      1 50032 -118  .012906
      1 50032 -117  .012531
      1 50032 -116  .005753
      1 50032 -115 -.007808
      1 50032 -114  .002986
      1 50032 -113 -.006993
      1 50032 -112 -.004306
      1 50032 -111  .008428
      1 50032 -110    .0052
      1 50032 -109  .008833
      1 50032 -108 -.002381
      1 50032 -107  .008482
      1 50032 -106  .000672
      1 50032 -105  .005384
      1 50032 -104 -.019243
      1 50032 -103  .012395
      1 50032 -102  .015121
      1 50032 -101  .012325
      1 50032 -100 -.005313
      1 50032  -99 -.014605
      1 50032  -98  -.01944
      1 50032  -97  .002895
      1 50032  -96  .011162
      1 50032  -95 -.002914
      1 50032  -94 -.006056
      1 50032  -93  .013336
      1 50032  -92  .023688
      1 50032  -91 -.031695
      1 50032  -90 -.013209
      1 50032  -89  .009818
      1 50032  -88  .018487
      1 50032  -87 -.003978
      1 50032  -86  -.01392
      1 50032  -85 -.016181
      1 50032  -84  .001971
      1 50032  -83  .003042
      1 50032  -82  .022312
      1 50032  -81  .001576
      1 50032  -80 -.013977
      1 50032  -79  .000269
      1 50032  -78  .017122
      1 50032  -77  .013939
      1 50032  -76  .012855
      1 50032  -75 -.019055
      1 50032  -74  .012392
      1 50032  -73  .015925
      1 50032  -72 -.006392
      1 50032  -71  .004308
      1 50032  -70 -.027043
      1 50032  -69  .015264
      1 50032  -68 -.008214
      1 50032  -67 -.003799
      1 50032  -66 -.010275
      1 50032  -65 -.002078
      1 50032  -64 -.027896
      1 50032  -63 -.010055
      1 50032  -62 -.011902
      1 50032  -61  .005263
      1 50032  -60  .006708
      1 50032  -59  .007209
      1 50032  -58 -.005957
      1 50032  -57  .011661
      1 50032  -56  .020184
      1 50032  -55 -.009383
      1 50032  -54  .003783
      1 50032  -53 -.014436
      1 50032  -52 -.034742
      1 50032  -51 -.020902
      1 50032  -50 -.000098
      1 50032  -49  .014929
      1 50032  -48  .002364
      1 50032  -47 -.013005
      end

      Comment

      Working...
      X