Calculating Trading Days around Earning Announcements - Multiple Events per Firm

[Philipp Rossmanith]

Dear Statalist Community,

I have a problem calculating the number trading days around event dates, more specific around the dates of quarterly earning announcements. I use the current Stata version, Stata 15.1.

In general I have a data set of panel data, which is xtset by xtset permno date. Permno and cik are both variables, that allow me to identify my firms, while date describes the specific calendar date, prmean the average stock price on the given date and earningsannouncement corresponds to a dummy variable that equals 1("Yes"), if the firm announced its quarterly earnings on the given date and 0("No") otherwise.

In the below example you can find a part of my dataset only focussing on one specific firm (cik = 350563).

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(cik permno) float date double prmean float earningsannouncement
350563 37161 12057 41.546542553191486 0
350563 37161 12058 41.458333333333336 0
350563 37161 12059  41.44202898550725 0
350563 37161 12060  41.18541666666667 0
350563 37161 12061       40.783203125 0
350563 37161 12064  40.72222222222222 0
350563 37161 12065  40.94763513513514 0
350563 37161 12066 41.213235294117645 0
350563 37161 12067  41.26865671641791 0
350563 37161 12068  41.38257575757576 0
350563 37161 12071 41.685267857142854 0
350563 37161 12072 41.934859154929576 0
350563 37161 12073  42.15573770491803 0
350563 37161 12074  41.96082089552239 0
350563 37161 12075  42.23134328358209 0
350563 37161 12078 41.823113207547166 0
350563 37161 12079 41.933035714285715 0
350563 37161 12080   41.8235294117647 0
350563 37161 12081   41.8445945945946 0
350563 37161 12082 41.816287878787875 0
350563 37161 12085  42.30357142857143 0
350563 37161 12086  42.57258064516129 0
350563 37161 12087  42.99013157894737 0
350563 37161 12088  43.13771186440678 0
350563 37161 12089  42.93614130434783 0
350563 37161 12092            43.1875 0
350563 37161 12093  43.39473684210526 0
350563 37161 12094 43.438380281690144 0
350563 37161 12095 43.343283582089555 0
350563 37161 12096 43.364754098360656 0
350563 37161 12100  43.31505102040816 0
350563 37161 12101   43.1764705882353 0
350563 37161 12102  43.31923076923077 0
350563 37161 12103  43.39715189873418 0
350563 37161 12106  43.85227272727273 0
350563 37161 12107  44.51151315789474 0
350563 37161 12108  44.96683673469388 0
350563 37161 12109  44.88901869158879 0
350563 37161 12110          44.878125 0
350563 37161 12113  45.06849315068493 0
350563 37161 12114  45.51013513513514 0
350563 37161 12115   45.6468023255814 0
350563 37161 12116 45.536144578313255 0
350563 37161 12117 45.364197530864196 0
350563 37161 12120         45.4609375 0
350563 37161 12121             45.625 0
350563 37161 12122         45.4078125 0
350563 37161 12123 45.422348484848484 0
350563 37161 12124  45.30283505154639 0
350563 37161 12127  44.90396341463415 0
350563 37161 12128 44.861301369863014 0
350563 37161 12129            44.9375 0
350563 37161 12130 45.036764705882355 0
350563 37161 12131  44.85339506172839 0
350563 37161 12134  44.57303370786517 0
350563 37161 12135 44.726027397260275 0
350563 37161 12136  44.81707317073171 0
350563 37161 12137  44.76411290322581 0
350563 37161 12138 44.929245283018865 0
350563 37161 12141  45.04094827586207 0
350563 37161 12142  45.27254098360656 0
350563 37161 12143 45.609848484848484 0
350563 37161 12144 45.669444444444444 0
350563 37161 12145  45.29891304347826 0
350563 37161 12148 45.467948717948715 0
350563 37161 12149  45.54967948717949 0
350563 37161 12150  45.71913580246913 0
350563 37161 12151 45.916666666666664 0
350563 37161 12155 46.467171717171716 0
350563 37161 12156  46.66029411764706 0
350563 37161 12157         46.5953125 0
350563 37161 12158 46.702651515151516 1
350563 37161 12159  47.35394736842105 0
350563 37161 12162  47.16470588235294 0
350563 37161 12163 46.985632183908045 0
350563 37161 12164   46.7373595505618 0
350563 37161 12165  46.53382352941176 0
350563 37161 12166  46.34487951807229 0
350563 37161 12169  45.72205882352941 0
350563 37161 12170  45.57471264367816 0
350563 37161 12171  45.49050632911393 0
350563 37161 12172  45.45192307692308 0
350563 37161 12173  45.48007246376812 0
350563 37161 12176 45.669642857142854 0
350563 37161 12177  45.71818181818182 0
350563 37161 12178  46.47844827586207 0
350563 37161 12179 46.684859154929576 0
350563 37161 12180        46.70703125 0
350563 37161 12183 46.682870370370374 0
350563 37161 12184  46.64558823529412 0
350563 37161 12185  46.54049295774648 0
350563 37161 12186 46.251543209876544 0
350563 37161 12187  45.86693548387097 0
350563 37161 12190  45.45684523809524 0
350563 37161 12191 44.793432203389834 0
350563 37161 12192  44.08522727272727 0
350563 37161 12193            44.8125 0
350563 37161 12194       44.693359375 0
350563 37161 12197 44.582770270270274 0
350563 37161 12198 44.926339285714285 0
350563 37161 12199  45.58132530120482 0
350563 37161 12200  45.92460317460318 0
350563 37161 12201  45.68333333333333 0
350563 37161 12205 46.354166666666664 0
350563 37161 12206 46.791666666666664 0
350563 37161 12207  46.62755102040816 0
350563 37161 12208  46.55846774193548 0
350563 37161 12211 46.525862068965516 0
350563 37161 12212  45.89673913043478 0
350563 37161 12213 46.110169491525426 0
350563 37161 12214  46.22767857142857 0
350563 37161 12215 46.099489795918366 0
350563 37161 12218  45.77291666666667 0
350563 37161 12219  45.45696721311475 0
350563 37161 12220  45.51973684210526 0
350563 37161 12221 45.933098591549296 0
350563 37161 12222  46.06578947368421 0
350563 37161 12225  46.19444444444444 0
350563 37161 12226 46.150862068965516 0
350563 37161 12227  46.28365384615385 0
350563 37161 12228 46.424479166666664 0
350563 37161 12229   46.3578431372549 0
350563 37161 12232  46.80172413793103 0
350563 37161 12233  47.28846153846154 0
350563 37161 12234  47.28448275862069 0
350563 37161 12235 47.301923076923075 0
350563 37161 12236 47.432065217391305 0
350563 37161 12240  47.61830357142857 0
350563 37161 12241 47.690140845070424 0
350563 37161 12242  47.55227272727273 0
350563 37161 12243 47.666666666666664 0
350563 37161 12246 47.639150943396224 0
350563 37161 12247  47.66041666666667 0
350563 37161 12248  47.69444444444444 0
350563 37161 12249            47.8125 0
350563 37161 12250  47.97149122807018 1
350563 37161 12253  47.99583333333333 0
350563 37161 12254 48.107894736842105 0
350563 37161 12255 48.544117647058826 0
350563 37161 12256  48.73550724637681 0
350563 37161 12257  48.57258064516129 0
350563 37161 12260  48.06578947368421 0
350563 37161 12261         48.0546875 0
350563 37161 12262  48.06702898550725 0
350563 37161 12263 48.313291139240505 0
350563 37161 12264  48.30288461538461 0
350563 37161 12267 48.395270270270274 0
350563 37161 12268  48.51315789473684 0
350563 37161 12269  48.91235632183908 0
350563 37161 12270  49.61918604651163 0
350563 37161 12271  49.56349206349206 0
350563 37161 12274  49.62797619047619 0
350563 37161 12275             49.625 0
350563 37161 12276 49.445402298850574 0
350563 37161 12277             49.365 0
350563 37161 12278 49.441326530612244 0
350563 37161 12281         49.1765625 0
350563 37161 12282  48.72560975609756 0
350563 37161 12283 48.869791666666664 0
350563 37161 12284  49.05769230769231 0
350563 37161 12285 49.485416666666666 0
350563 37161 12288  49.45454545454545 0
350563 37161 12289   49.4365671641791 0
350563 37161 12290  49.52256944444444 0
350563 37161 12291 49.464622641509436 0
350563 37161 12292  49.43103448275862 0
350563 37161 12295  49.44556451612903 0
350563 37161 12296            25.1875 0
350563 37161 12297   25.1453488372093 0
350563 37161 12298 25.420516304347824 0
350563 37161 12299  25.63855421686747 0
350563 37161 12303  25.66023489932886 0
350563 37161 12304 25.480132450331126 0
350563 37161 12305 25.389084507042252 0
350563 37161 12306 25.591216216216218 0
350563 37161 12309 25.780133928571427 0
350563 37161 12310 25.610491071428573 0
350563 37161 12311 25.355978260869566 0
350563 37161 12312 25.498062015503876 0
350563 37161 12313  25.31030701754386 0
350563 37161 12316 25.259146341463413 0
350563 37161 12317 25.177455357142858 0
350563 37161 12318  25.11697247706422 0
350563 37161 12319 25.272959183673468 0
350563 37161 12320 25.235759493670887 0
350563 37161 12323 25.623809523809523 0
350563 37161 12324             25.825 0
350563 37161 12325 25.741935483870968 0
350563 37161 12326 25.558673469387756 0
350563 37161 12327 25.533088235294116 0
350563 37161 12330 25.571691176470587 0
350563 37161 12331 25.508223684210527 0
350563 37161 12332 25.362068965517242 0
350563 37161 12333  25.00978260869565 0
350563 37161 12334 24.968373493975903 0
350563 37161 12337       25.099609375 0
350563 37161 12338 25.180059523809526 0
350563 37161 12339 25.098333333333333 0
350563 37161 12340  25.32528409090909 0
350563 37161 12341 25.413194444444443 1
350563 37161 12344 25.193055555555556 0
350563 37161 12345 25.190384615384616 0
350563 37161 12346 25.214285714285715 0
350563 37161 12347  24.99418604651163 0
350563 37161 12348  24.78188775510204 0
350563 37161 12351  24.05827067669173 0
350563 37161 12352  23.95420792079208 0
350563 37161 12353  24.37087912087912 0
350563 37161 12354 24.544303797468356 0
350563 37161 12355 24.545833333333334 0
350563 37161 12358  24.63653846153846 0
350563 37161 12359 24.348863636363635 0
350563 37161 12360  22.91764018691589 0
350563 37161 12361         22.7734375 0
350563 37161 12362  22.53611111111111 0
350563 37161 12365 22.761607142857144 0
350563 37161 12366   22.8640873015873 0
350563 37161 12367 23.049778761061948 0
350563 37161 12368 23.011494252873565 0
350563 37161 12369 22.851136363636364 0
350563 37161 12372 22.593023255813954 0
350563 37161 12373 22.569166666666668 0
350563 37161 12374 23.225235849056602 0
350563 37161 12375 23.166666666666668 0
350563 37161 12376  22.96276595744681 0
350563 37161 12379             22.875 0
350563 37161 12380  22.96638655462185 0
350563 37161 12381  23.32894736842105 0
350563 37161 12383 23.404761904761905 0
350563 37161 12386 23.287760416666668 0
350563 37161 12387 23.327272727272728 0
350563 37161 12388 22.951923076923077 0
350563 37161 12389             22.845 0
350563 37161 12390  22.81140350877193 0
350563 37161 12393 22.904605263157894 0
350563 37161 12394  22.77195945945946 0
350563 37161 12395  22.76909722222222 0
350563 37161 12396 22.734154929577464 0
350563 37161 12397 22.567028985507246 0
350563 37161 12400  22.55034722222222 0
350563 37161 12401 22.291208791208792 0
350563 37161 12402 22.361280487804876 0
350563 37161 12403 22.573943661971832 0
350563 37161 12404 22.741438356164384 0
350563 37161 12407  22.83776595744681 0
350563 37161 12408 22.681318681318682 0
350563 37161 12409 22.776785714285715 0
350563 37161 12410 22.963541666666668 0
350563 37161 12414        22.91796875 0
350563 37161 12415 22.865384615384617 0
350563 37161 12416  22.91231343283582 0
350563 37161 12417 22.819444444444443 0
350563 37161 12418  22.78409090909091 0
350563 37161 12421 22.404605263157894 0
350563 37161 12422  22.28030303030303 0
350563 37161 12423 22.107905982905983 0
350563 37161 12424  22.03429203539823 0
350563 37161 12425 21.727272727272727 0
350563 37161 12428 21.625954198473284 0
350563 37161 12429 21.439732142857142 0
350563 37161 12430 21.308189655172413 0
350563 37161 12431 21.194318181818183 0
350563 37161 12432  20.95612582781457 0
350563 37161 12435 20.734110169491526 1
350563 37161 12436 20.787852112676056 0
350563 37161 12437 20.712786259541986 0
350563 37161 12438  20.68846153846154 0
350563 37161 12439 20.524590163934427 0
350563 37161 12442 20.209033613445378 0
350563 37161 12443 20.112723214285715 0
350563 37161 12444   20.1706008583691 0
350563 37161 12445   20.9779792746114 0
350563 37161 12446          20.921875 0
350563 37161 12449 20.755172413793105 0
350563 37161 12450 20.549212598425196 0
350563 37161 12451 20.198426573426573 0
350563 37161 12452              20.45 0
350563 37161 12453 20.887218045112782 0
350563 37161 12456              20.65 0
350563 37161 12457  20.61413043478261 0
350563 37161 12458 20.716535433070867 0
350563 37161 12459 20.721217105263158 0
350563 37161 12460 20.698529411764707 0
350563 37161 12463 20.645833333333332 0
350563 37161 12464 20.796677215189874 0
350563 37161 12465 20.652397260273972 0
350563 37161 12466 20.377777777777776 0
350563 37161 12467          20.159375 0
350563 37161 12471 20.113065326633166 0
350563 37161 12472 20.013392857142858 0
350563 37161 12473  19.84452736318408 0
350563 37161 12474 20.104545454545455 0
350563 37161 12477 20.220833333333335 0
350563 37161 12478 20.214139344262296 0
350563 37161 12479 19.833896396396398 0
350563 37161 12480  19.85763888888889 0
350563 37161 12481 20.074380165289256 0
350563 37161 12484 20.403508771929825 0
350563 37161 12485 20.285194174757283 0
350563 37161 12486  20.30909090909091 0
end
format %td date
label values earningsannouncement earningsannouncementlabel
label def earningsannouncementlabel 0 "NO", modify
label def earningsannouncementlabel 1 "YES", modify

For an event study I want to calculate the trading days around the event dates of the given earning announcements. In line with a detailed description on the homepage of the the library of the Princeton University (https://dss.princeton.edu/online_hel...ventstudy.html) I did this with the following code:

// Define a variable for the date of the earnings announcements
generate earnings_date = date if earningsannouncement == 1
format earnings_date %td

// Calculate trading days around the date of the earnings announcements
sort cik date
by cik: gen datenum = _n
by cik: gen target = datenum if date == earnings_date
egen td = min(target), by(cik)
drop target
gen diff = datenum-td

This gives me now, as I wanted, a variable diff, that calculates with negative values (-1 to -71) the trading days before the first earnings announcement and with positive values the trading days after the first earnings announcement, as you can see below.
However, this is actually the problem. The variable only calculates the trading days around the first earning announcement on April 15th in 1993. What I actually want is a variable that counts the trading days before and after each earning announcement, such that for the second earning announcement on July 6th 1993 the variable diff has a value of 0 again. I know that this is in such a sense not possible, as the 63rd trading day after the first earning announcement (diff = +63) is also the first day before the second announcement.

Therefore, I thought about a variable that counts just the last 10 trading days before an announcement and the following 20 trading days after an announcement, as this will be the maximum time horizon I need for my event study. However I didn't came up with a solution to that. In general I'm searching for an approach that allows me to generalize the approach of the Princeton University to panel data, where we have multiple event dates for every individual firm.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(cik permno) float date double prmean float(earningsannouncement earnings_date datenum td diff)
350563 37161 12057 41.546542553191486 0     .   1 72 -71
350563 37161 12058 41.458333333333336 0     .   2 72 -70
350563 37161 12059  41.44202898550725 0     .   3 72 -69
350563 37161 12060  41.18541666666667 0     .   4 72 -68
350563 37161 12061       40.783203125 0     .   5 72 -67
350563 37161 12064  40.72222222222222 0     .   6 72 -66
350563 37161 12065  40.94763513513514 0     .   7 72 -65
350563 37161 12066 41.213235294117645 0     .   8 72 -64
350563 37161 12067  41.26865671641791 0     .   9 72 -63
350563 37161 12068  41.38257575757576 0     .  10 72 -62
350563 37161 12071 41.685267857142854 0     .  11 72 -61
350563 37161 12072 41.934859154929576 0     .  12 72 -60
350563 37161 12073  42.15573770491803 0     .  13 72 -59
350563 37161 12074  41.96082089552239 0     .  14 72 -58
350563 37161 12075  42.23134328358209 0     .  15 72 -57
350563 37161 12078 41.823113207547166 0     .  16 72 -56
350563 37161 12079 41.933035714285715 0     .  17 72 -55
350563 37161 12080   41.8235294117647 0     .  18 72 -54
350563 37161 12081   41.8445945945946 0     .  19 72 -53
350563 37161 12082 41.816287878787875 0     .  20 72 -52
350563 37161 12085  42.30357142857143 0     .  21 72 -51
350563 37161 12086  42.57258064516129 0     .  22 72 -50
350563 37161 12087  42.99013157894737 0     .  23 72 -49
350563 37161 12088  43.13771186440678 0     .  24 72 -48
350563 37161 12089  42.93614130434783 0     .  25 72 -47
350563 37161 12092            43.1875 0     .  26 72 -46
350563 37161 12093  43.39473684210526 0     .  27 72 -45
350563 37161 12094 43.438380281690144 0     .  28 72 -44
350563 37161 12095 43.343283582089555 0     .  29 72 -43
350563 37161 12096 43.364754098360656 0     .  30 72 -42
350563 37161 12100  43.31505102040816 0     .  31 72 -41
350563 37161 12101   43.1764705882353 0     .  32 72 -40
350563 37161 12102  43.31923076923077 0     .  33 72 -39
350563 37161 12103  43.39715189873418 0     .  34 72 -38
350563 37161 12106  43.85227272727273 0     .  35 72 -37
350563 37161 12107  44.51151315789474 0     .  36 72 -36
350563 37161 12108  44.96683673469388 0     .  37 72 -35
350563 37161 12109  44.88901869158879 0     .  38 72 -34
350563 37161 12110          44.878125 0     .  39 72 -33
350563 37161 12113  45.06849315068493 0     .  40 72 -32
350563 37161 12114  45.51013513513514 0     .  41 72 -31
350563 37161 12115   45.6468023255814 0     .  42 72 -30
350563 37161 12116 45.536144578313255 0     .  43 72 -29
350563 37161 12117 45.364197530864196 0     .  44 72 -28
350563 37161 12120         45.4609375 0     .  45 72 -27
350563 37161 12121             45.625 0     .  46 72 -26
350563 37161 12122         45.4078125 0     .  47 72 -25
350563 37161 12123 45.422348484848484 0     .  48 72 -24
350563 37161 12124  45.30283505154639 0     .  49 72 -23
350563 37161 12127  44.90396341463415 0     .  50 72 -22
350563 37161 12128 44.861301369863014 0     .  51 72 -21
350563 37161 12129            44.9375 0     .  52 72 -20
350563 37161 12130 45.036764705882355 0     .  53 72 -19
350563 37161 12131  44.85339506172839 0     .  54 72 -18
350563 37161 12134  44.57303370786517 0     .  55 72 -17
350563 37161 12135 44.726027397260275 0     .  56 72 -16
350563 37161 12136  44.81707317073171 0     .  57 72 -15
350563 37161 12137  44.76411290322581 0     .  58 72 -14
350563 37161 12138 44.929245283018865 0     .  59 72 -13
350563 37161 12141  45.04094827586207 0     .  60 72 -12
350563 37161 12142  45.27254098360656 0     .  61 72 -11
350563 37161 12143 45.609848484848484 0     .  62 72 -10
350563 37161 12144 45.669444444444444 0     .  63 72  -9
350563 37161 12145  45.29891304347826 0     .  64 72  -8
350563 37161 12148 45.467948717948715 0     .  65 72  -7
350563 37161 12149  45.54967948717949 0     .  66 72  -6
350563 37161 12150  45.71913580246913 0     .  67 72  -5
350563 37161 12151 45.916666666666664 0     .  68 72  -4
350563 37161 12155 46.467171717171716 0     .  69 72  -3
350563 37161 12156  46.66029411764706 0     .  70 72  -2
350563 37161 12157         46.5953125 0     .  71 72  -1
350563 37161 12158 46.702651515151516 1 12158  72 72   0
350563 37161 12159  47.35394736842105 0     .  73 72   1
350563 37161 12162  47.16470588235294 0     .  74 72   2
350563 37161 12163 46.985632183908045 0     .  75 72   3
350563 37161 12164   46.7373595505618 0     .  76 72   4
350563 37161 12165  46.53382352941176 0     .  77 72   5
350563 37161 12166  46.34487951807229 0     .  78 72   6
350563 37161 12169  45.72205882352941 0     .  79 72   7
350563 37161 12170  45.57471264367816 0     .  80 72   8
350563 37161 12171  45.49050632911393 0     .  81 72   9
350563 37161 12172  45.45192307692308 0     .  82 72  10
350563 37161 12173  45.48007246376812 0     .  83 72  11
350563 37161 12176 45.669642857142854 0     .  84 72  12
350563 37161 12177  45.71818181818182 0     .  85 72  13
350563 37161 12178  46.47844827586207 0     .  86 72  14
350563 37161 12179 46.684859154929576 0     .  87 72  15
350563 37161 12180        46.70703125 0     .  88 72  16
350563 37161 12183 46.682870370370374 0     .  89 72  17
350563 37161 12184  46.64558823529412 0     .  90 72  18
350563 37161 12185  46.54049295774648 0     .  91 72  19
350563 37161 12186 46.251543209876544 0     .  92 72  20
350563 37161 12187  45.86693548387097 0     .  93 72  21
350563 37161 12190  45.45684523809524 0     .  94 72  22
350563 37161 12191 44.793432203389834 0     .  95 72  23
350563 37161 12192  44.08522727272727 0     .  96 72  24
350563 37161 12193            44.8125 0     .  97 72  25
350563 37161 12194       44.693359375 0     .  98 72  26
350563 37161 12197 44.582770270270274 0     .  99 72  27
350563 37161 12198 44.926339285714285 0     . 100 72  28
350563 37161 12199  45.58132530120482 0     . 101 72  29
350563 37161 12200  45.92460317460318 0     . 102 72  30
350563 37161 12201  45.68333333333333 0     . 103 72  31
350563 37161 12205 46.354166666666664 0     . 104 72  32
350563 37161 12206 46.791666666666664 0     . 105 72  33
350563 37161 12207  46.62755102040816 0     . 106 72  34
350563 37161 12208  46.55846774193548 0     . 107 72  35
350563 37161 12211 46.525862068965516 0     . 108 72  36
350563 37161 12212  45.89673913043478 0     . 109 72  37
350563 37161 12213 46.110169491525426 0     . 110 72  38
350563 37161 12214  46.22767857142857 0     . 111 72  39
350563 37161 12215 46.099489795918366 0     . 112 72  40
350563 37161 12218  45.77291666666667 0     . 113 72  41
350563 37161 12219  45.45696721311475 0     . 114 72  42
350563 37161 12220  45.51973684210526 0     . 115 72  43
350563 37161 12221 45.933098591549296 0     . 116 72  44
350563 37161 12222  46.06578947368421 0     . 117 72  45
350563 37161 12225  46.19444444444444 0     . 118 72  46
350563 37161 12226 46.150862068965516 0     . 119 72  47
350563 37161 12227  46.28365384615385 0     . 120 72  48
350563 37161 12228 46.424479166666664 0     . 121 72  49
350563 37161 12229   46.3578431372549 0     . 122 72  50
350563 37161 12232  46.80172413793103 0     . 123 72  51
350563 37161 12233  47.28846153846154 0     . 124 72  52
350563 37161 12234  47.28448275862069 0     . 125 72  53
350563 37161 12235 47.301923076923075 0     . 126 72  54
350563 37161 12236 47.432065217391305 0     . 127 72  55
350563 37161 12240  47.61830357142857 0     . 128 72  56
350563 37161 12241 47.690140845070424 0     . 129 72  57
350563 37161 12242  47.55227272727273 0     . 130 72  58
350563 37161 12243 47.666666666666664 0     . 131 72  59
350563 37161 12246 47.639150943396224 0     . 132 72  60
350563 37161 12247  47.66041666666667 0     . 133 72  61
350563 37161 12248  47.69444444444444 0     . 134 72  62
350563 37161 12249            47.8125 0     . 135 72  63
350563 37161 12250  47.97149122807018 1 12250 136 72  64
350563 37161 12253  47.99583333333333 0     . 137 72  65
350563 37161 12254 48.107894736842105 0     . 138 72  66
350563 37161 12255 48.544117647058826 0     . 139 72  67
350563 37161 12256  48.73550724637681 0     . 140 72  68
350563 37161 12257  48.57258064516129 0     . 141 72  69
350563 37161 12260  48.06578947368421 0     . 142 72  70
350563 37161 12261         48.0546875 0     . 143 72  71
350563 37161 12262  48.06702898550725 0     . 144 72  72
350563 37161 12263 48.313291139240505 0     . 145 72  73
350563 37161 12264  48.30288461538461 0     . 146 72  74
350563 37161 12267 48.395270270270274 0     . 147 72  75
350563 37161 12268  48.51315789473684 0     . 148 72  76
350563 37161 12269  48.91235632183908 0     . 149 72  77
350563 37161 12270  49.61918604651163 0     . 150 72  78
350563 37161 12271  49.56349206349206 0     . 151 72  79
350563 37161 12274  49.62797619047619 0     . 152 72  80
350563 37161 12275             49.625 0     . 153 72  81
350563 37161 12276 49.445402298850574 0     . 154 72  82
350563 37161 12277             49.365 0     . 155 72  83
350563 37161 12278 49.441326530612244 0     . 156 72  84
350563 37161 12281         49.1765625 0     . 157 72  85
350563 37161 12282  48.72560975609756 0     . 158 72  86
350563 37161 12283 48.869791666666664 0     . 159 72  87
350563 37161 12284  49.05769230769231 0     . 160 72  88
350563 37161 12285 49.485416666666666 0     . 161 72  89
350563 37161 12288  49.45454545454545 0     . 162 72  90
350563 37161 12289   49.4365671641791 0     . 163 72  91
350563 37161 12290  49.52256944444444 0     . 164 72  92
350563 37161 12291 49.464622641509436 0     . 165 72  93
350563 37161 12292  49.43103448275862 0     . 166 72  94
350563 37161 12295  49.44556451612903 0     . 167 72  95
350563 37161 12296            25.1875 0     . 168 72  96
350563 37161 12297   25.1453488372093 0     . 169 72  97
350563 37161 12298 25.420516304347824 0     . 170 72  98
350563 37161 12299  25.63855421686747 0     . 171 72  99
350563 37161 12303  25.66023489932886 0     . 172 72 100
end
format %td date
format %td earnings_date
label values earningsannouncement earningsannouncementlabel
label def earningsannouncementlabel 0 "NO", modify
label def earningsannouncementlabel 1 "YES", modify

Any recommendations, approaches and solutions are highly appreciated.

Thank you very much in advance Statalist community.

Best regards
Philipp

[Clyde Schechter]

I think you want:

preserve
keep if earningsannouncement
keep date cik permno
tempfile event_dates
save `event_dates'

restore
rangejoin date -21 10 using `event_dates', by(cik permno)
gen diff = date-date_U

Note: -rangejoin- is written by Robert Picard, and is available from SSC.

I assume that it is the combination of cik and permno that uniquely identifies firms. If I have that wrong, change the -by()- option accordingly. I also assume that by 10 days before and 21 days after you mean including the 10th day before and the 21st day after. If I have that wrong, adjust the numbers between date and using accordingly.

Note: If a firm has event dates sufficiently close that these intervals from 10 days before to 21 days after overlap, you will get some peculiar results here that may or may not be useful for you. I'm not sure how you want to handle a situation like that (or maybe that can't happen?), and if it is an issue, you need to give some guidance about that.

[Robert Picard]

You are not following the instructions from the data preparation page. You need to form all pairwise combinations of events with your trading data.

The Princeton event study instructions are old and overly complicated. You can follow the instructions I posted last summer instead. Unfortunately, the example datasets do not appear to be available anymore.

[Clyde Schechter]

Clarification on #2. I did not check the Princeton event study instructions. My response shows how to calculate the variable that Phllipp Rossmanith described. Whether that variable is appropriate for his larger goals, I do not know.

[Robert Picard]

As I said, the Princeton pages are dated. I think your intuition is spot on that rangejoin can be used instead of joinby, especially if the trading data covers a much larger window than the one of interest around the event date.

[Philipp Rossmanith]

Thank you Clyde and Robert for your fast reply.

The code provided by Clyde in #2 fulfills (nearly) completely what I was after, so a big "Thank You" for providing it.
However, as you can see in the code below, your code counts the calendar days around the event date. What I was actually referring to, was a variable diff that counts the trading days around the event date, ignoring weekends or public holidays.
In the below example this would mean for example that for observation 72 (date = 15apr1993 / first earning announcement) the diff variable would just tag the ten observations before and the twenty observations after the earnings announcement on 15apr1993, such that the variable diff takes values form -1 to -10 from observation 71 to observation 62 backwards, and values from 1 to 20 for observations 73 to 92.
How can I achieve that? It would be probably even easier, as we do not need the diff variable but only a variable that tags the previous ten observations before the event date and the following twenty observations after the day?

The reason for that is that I always want to look at the same event windows, for example by taking the observations of the previous 5 and the following 5 trading days around an earnings announcement.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(cik permno) float date double prmean float(earningsannouncement date_U diff)
350563 37161 12057 41.546542553191486 0     .   .
350563 37161 12058 41.458333333333336 0     .   .
350563 37161 12059  41.44202898550725 0     .   .
350563 37161 12060  41.18541666666667 0     .   .
350563 37161 12061       40.783203125 0     .   .
350563 37161 12064  40.72222222222222 0     .   .
350563 37161 12065  40.94763513513514 0     .   .
350563 37161 12066 41.213235294117645 0     .   .
350563 37161 12067  41.26865671641791 0     .   .
350563 37161 12068  41.38257575757576 0     .   .
350563 37161 12071 41.685267857142854 0     .   .
350563 37161 12072 41.934859154929576 0     .   .
350563 37161 12073  42.15573770491803 0     .   .
350563 37161 12074  41.96082089552239 0     .   .
350563 37161 12075  42.23134328358209 0     .   .
350563 37161 12078 41.823113207547166 0     .   .
350563 37161 12079 41.933035714285715 0     .   .
350563 37161 12080   41.8235294117647 0     .   .
350563 37161 12081   41.8445945945946 0     .   .
350563 37161 12082 41.816287878787875 0     .   .
350563 37161 12085  42.30357142857143 0     .   .
350563 37161 12086  42.57258064516129 0     .   .
350563 37161 12087  42.99013157894737 0     .   .
350563 37161 12088  43.13771186440678 0     .   .
350563 37161 12089  42.93614130434783 0     .   .
350563 37161 12092            43.1875 0     .   .
350563 37161 12093  43.39473684210526 0     .   .
350563 37161 12094 43.438380281690144 0     .   .
350563 37161 12095 43.343283582089555 0     .   .
350563 37161 12096 43.364754098360656 0     .   .
350563 37161 12100  43.31505102040816 0     .   .
350563 37161 12101   43.1764705882353 0     .   .
350563 37161 12102  43.31923076923077 0     .   .
350563 37161 12103  43.39715189873418 0     .   .
350563 37161 12106  43.85227272727273 0     .   .
350563 37161 12107  44.51151315789474 0     .   .
350563 37161 12108  44.96683673469388 0     .   .
350563 37161 12109  44.88901869158879 0     .   .
350563 37161 12110          44.878125 0     .   .
350563 37161 12113  45.06849315068493 0     .   .
350563 37161 12114  45.51013513513514 0     .   .
350563 37161 12115   45.6468023255814 0     .   .
350563 37161 12116 45.536144578313255 0     .   .
350563 37161 12117 45.364197530864196 0     .   .
350563 37161 12120         45.4609375 0     .   .
350563 37161 12121             45.625 0     .   .
350563 37161 12122         45.4078125 0     .   .
350563 37161 12123 45.422348484848484 0     .   .
350563 37161 12124  45.30283505154639 0     .   .
350563 37161 12127  44.90396341463415 0     .   .
350563 37161 12128 44.861301369863014 0     .   .
350563 37161 12129            44.9375 0     .   .
350563 37161 12130 45.036764705882355 0     .   .
350563 37161 12131  44.85339506172839 0     .   .
350563 37161 12134  44.57303370786517 0     .   .
350563 37161 12135 44.726027397260275 0     .   .
350563 37161 12136  44.81707317073171 0     .   .
350563 37161 12137  44.76411290322581 0     .   .
350563 37161 12138 44.929245283018865 0     .   .
350563 37161 12141  45.04094827586207 0     .   .
350563 37161 12142  45.27254098360656 0     .   .
350563 37161 12143 45.609848484848484 0     .   .
350563 37161 12144 45.669444444444444 0     .   .
350563 37161 12145  45.29891304347826 0     .   .
350563 37161 12148 45.467948717948715 0 12158 -10
350563 37161 12149  45.54967948717949 0 12158  -9
350563 37161 12150  45.71913580246913 0 12158  -8
350563 37161 12151 45.916666666666664 0 12158  -7
350563 37161 12155 46.467171717171716 0 12158  -3
350563 37161 12156  46.66029411764706 0 12158  -2
350563 37161 12157         46.5953125 0 12158  -1
350563 37161 12158 46.702651515151516 1 12158   0
350563 37161 12159  47.35394736842105 0 12158   1
350563 37161 12162  47.16470588235294 0 12158   4
350563 37161 12163 46.985632183908045 0 12158   5
350563 37161 12164   46.7373595505618 0 12158   6
350563 37161 12165  46.53382352941176 0 12158   7
350563 37161 12166  46.34487951807229 0 12158   8
350563 37161 12169  45.72205882352941 0 12158  11
350563 37161 12170  45.57471264367816 0 12158  12
350563 37161 12171  45.49050632911393 0 12158  13
350563 37161 12172  45.45192307692308 0 12158  14
350563 37161 12173  45.48007246376812 0 12158  15
350563 37161 12176 45.669642857142854 0 12158  18
350563 37161 12177  45.71818181818182 0 12158  19
350563 37161 12178  46.47844827586207 0 12158  20
350563 37161 12179 46.684859154929576 0 12158  21
350563 37161 12180        46.70703125 0     .   .
350563 37161 12183 46.682870370370374 0     .   .
350563 37161 12184  46.64558823529412 0     .   .
350563 37161 12185  46.54049295774648 0     .   .
350563 37161 12186 46.251543209876544 0     .   .
350563 37161 12187  45.86693548387097 0     .   .
350563 37161 12190  45.45684523809524 0     .   .
350563 37161 12191 44.793432203389834 0     .   .
350563 37161 12192  44.08522727272727 0     .   .
350563 37161 12193            44.8125 0     .   .
350563 37161 12194       44.693359375 0     .   .
350563 37161 12197 44.582770270270274 0     .   .
350563 37161 12198 44.926339285714285 0     .   .
350563 37161 12199  45.58132530120482 0     .   .
350563 37161 12200  45.92460317460318 0     .   .
350563 37161 12201  45.68333333333333 0     .   .
350563 37161 12205 46.354166666666664 0     .   .
350563 37161 12206 46.791666666666664 0     .   .
350563 37161 12207  46.62755102040816 0     .   .
350563 37161 12208  46.55846774193548 0     .   .
350563 37161 12211 46.525862068965516 0     .   .
350563 37161 12212  45.89673913043478 0     .   .
350563 37161 12213 46.110169491525426 0     .   .
350563 37161 12214  46.22767857142857 0     .   .
350563 37161 12215 46.099489795918366 0     .   .
350563 37161 12218  45.77291666666667 0     .   .
350563 37161 12219  45.45696721311475 0     .   .
350563 37161 12220  45.51973684210526 0     .   .
350563 37161 12221 45.933098591549296 0     .   .
350563 37161 12222  46.06578947368421 0     .   .
350563 37161 12225  46.19444444444444 0     .   .
350563 37161 12226 46.150862068965516 0     .   .
350563 37161 12227  46.28365384615385 0     .   .
350563 37161 12228 46.424479166666664 0     .   .
350563 37161 12229   46.3578431372549 0     .   .
350563 37161 12232  46.80172413793103 0     .   .
350563 37161 12233  47.28846153846154 0     .   .
350563 37161 12234  47.28448275862069 0     .   .
350563 37161 12235 47.301923076923075 0     .   .
350563 37161 12236 47.432065217391305 0     .   .
350563 37161 12240  47.61830357142857 0 12250 -10
350563 37161 12241 47.690140845070424 0 12250  -9
350563 37161 12242  47.55227272727273 0 12250  -8
350563 37161 12243 47.666666666666664 0 12250  -7
350563 37161 12246 47.639150943396224 0 12250  -4
350563 37161 12247  47.66041666666667 0 12250  -3
350563 37161 12248  47.69444444444444 0 12250  -2
350563 37161 12249            47.8125 0 12250  -1
350563 37161 12250  47.97149122807018 1 12250   0
350563 37161 12253  47.99583333333333 0 12250   3
350563 37161 12254 48.107894736842105 0 12250   4
350563 37161 12255 48.544117647058826 0 12250   5
350563 37161 12256  48.73550724637681 0 12250   6
350563 37161 12257  48.57258064516129 0 12250   7
350563 37161 12260  48.06578947368421 0 12250  10
350563 37161 12261         48.0546875 0 12250  11
350563 37161 12262  48.06702898550725 0 12250  12
350563 37161 12263 48.313291139240505 0 12250  13
350563 37161 12264  48.30288461538461 0 12250  14
350563 37161 12267 48.395270270270274 0 12250  17
350563 37161 12268  48.51315789473684 0 12250  18
350563 37161 12269  48.91235632183908 0 12250  19
350563 37161 12270  49.61918604651163 0 12250  20
350563 37161 12271  49.56349206349206 0 12250  21
350563 37161 12274  49.62797619047619 0     .   .
350563 37161 12275             49.625 0     .   .
350563 37161 12276 49.445402298850574 0     .   .
350563 37161 12277             49.365 0     .   .
350563 37161 12278 49.441326530612244 0     .   .
350563 37161 12281         49.1765625 0     .   .
350563 37161 12282  48.72560975609756 0     .   .
350563 37161 12283 48.869791666666664 0     .   .
350563 37161 12284  49.05769230769231 0     .   .
350563 37161 12285 49.485416666666666 0     .   .
350563 37161 12288  49.45454545454545 0     .   .
350563 37161 12289   49.4365671641791 0     .   .
350563 37161 12290  49.52256944444444 0     .   .
350563 37161 12291 49.464622641509436 0     .   .
350563 37161 12292  49.43103448275862 0     .   .
350563 37161 12295  49.44556451612903 0     .   .
350563 37161 12296            25.1875 0     .   .
350563 37161 12297   25.1453488372093 0     .   .
350563 37161 12298 25.420516304347824 0     .   .
350563 37161 12299  25.63855421686747 0     .   .
350563 37161 12303  25.66023489932886 0     .   .
350563 37161 12304 25.480132450331126 0     .   .
350563 37161 12305 25.389084507042252 0     .   .
350563 37161 12306 25.591216216216218 0     .   .
350563 37161 12309 25.780133928571427 0     .   .
350563 37161 12310 25.610491071428573 0     .   .
350563 37161 12311 25.355978260869566 0     .   .
350563 37161 12312 25.498062015503876 0     .   .
350563 37161 12313  25.31030701754386 0     .   .
350563 37161 12316 25.259146341463413 0     .   .
350563 37161 12317 25.177455357142858 0     .   .
350563 37161 12318  25.11697247706422 0     .   .
350563 37161 12319 25.272959183673468 0     .   .
350563 37161 12320 25.235759493670887 0     .   .
350563 37161 12323 25.623809523809523 0     .   .
350563 37161 12324             25.825 0     .   .
350563 37161 12325 25.741935483870968 0     .   .
350563 37161 12326 25.558673469387756 0     .   .
350563 37161 12327 25.533088235294116 0     .   .
350563 37161 12330 25.571691176470587 0     .   .
350563 37161 12331 25.508223684210527 0 12341 -10
350563 37161 12332 25.362068965517242 0 12341  -9
350563 37161 12333  25.00978260869565 0 12341  -8
350563 37161 12334 24.968373493975903 0 12341  -7
350563 37161 12337       25.099609375 0 12341  -4
350563 37161 12338 25.180059523809526 0 12341  -3
350563 37161 12339 25.098333333333333 0 12341  -2
350563 37161 12340  25.32528409090909 0 12341  -1
350563 37161 12341 25.413194444444443 1 12341   0
350563 37161 12344 25.193055555555556 0 12341   3
350563 37161 12345 25.190384615384616 0 12341   4
350563 37161 12346 25.214285714285715 0 12341   5
350563 37161 12347  24.99418604651163 0 12341   6
350563 37161 12348  24.78188775510204 0 12341   7
350563 37161 12351  24.05827067669173 0 12341  10
350563 37161 12352  23.95420792079208 0 12341  11
350563 37161 12353  24.37087912087912 0 12341  12
350563 37161 12354 24.544303797468356 0 12341  13
350563 37161 12355 24.545833333333334 0 12341  14
350563 37161 12358  24.63653846153846 0 12341  17
350563 37161 12359 24.348863636363635 0 12341  18
350563 37161 12360  22.91764018691589 0 12341  19
350563 37161 12361         22.7734375 0 12341  20
350563 37161 12362  22.53611111111111 0 12341  21
350563 37161 12365 22.761607142857144 0     .   .
350563 37161 12366   22.8640873015873 0     .   .
350563 37161 12367 23.049778761061948 0     .   .
350563 37161 12368 23.011494252873565 0     .   .
350563 37161 12369 22.851136363636364 0     .   .
350563 37161 12372 22.593023255813954 0     .   .
350563 37161 12373 22.569166666666668 0     .   .
350563 37161 12374 23.225235849056602 0     .   .
350563 37161 12375 23.166666666666668 0     .   .
350563 37161 12376  22.96276595744681 0     .   .
350563 37161 12379             22.875 0     .   .
350563 37161 12380  22.96638655462185 0     .   .
350563 37161 12381  23.32894736842105 0     .   .
350563 37161 12383 23.404761904761905 0     .   .
350563 37161 12386 23.287760416666668 0     .   .
350563 37161 12387 23.327272727272728 0     .   .
350563 37161 12388 22.951923076923077 0     .   .
350563 37161 12389             22.845 0     .   .
350563 37161 12390  22.81140350877193 0     .   .
350563 37161 12393 22.904605263157894 0     .   .
350563 37161 12394  22.77195945945946 0     .   .
350563 37161 12395  22.76909722222222 0     .   .
350563 37161 12396 22.734154929577464 0     .   .
350563 37161 12397 22.567028985507246 0     .   .
350563 37161 12400  22.55034722222222 0     .   .
350563 37161 12401 22.291208791208792 0     .   .
350563 37161 12402 22.361280487804876 0     .   .
350563 37161 12403 22.573943661971832 0     .   .
350563 37161 12404 22.741438356164384 0     .   .
350563 37161 12407  22.83776595744681 0     .   .
350563 37161 12408 22.681318681318682 0     .   .
350563 37161 12409 22.776785714285715 0     .   .
350563 37161 12410 22.963541666666668 0     .   .
350563 37161 12414        22.91796875 0     .   .
350563 37161 12415 22.865384615384617 0     .   .
350563 37161 12416  22.91231343283582 0     .   .
350563 37161 12417 22.819444444444443 0     .   .
350563 37161 12418  22.78409090909091 0     .   .
350563 37161 12421 22.404605263157894 0     .   .
350563 37161 12422  22.28030303030303 0     .   .
350563 37161 12423 22.107905982905983 0     .   .
350563 37161 12424  22.03429203539823 0     .   .
350563 37161 12425 21.727272727272727 0 12435 -10
350563 37161 12428 21.625954198473284 0 12435  -7
350563 37161 12429 21.439732142857142 0 12435  -6
350563 37161 12430 21.308189655172413 0 12435  -5
350563 37161 12431 21.194318181818183 0 12435  -4
350563 37161 12432  20.95612582781457 0 12435  -3
350563 37161 12435 20.734110169491526 1 12435   0
350563 37161 12436 20.787852112676056 0 12435   1
350563 37161 12437 20.712786259541986 0 12435   2
350563 37161 12438  20.68846153846154 0 12435   3
350563 37161 12439 20.524590163934427 0 12435   4
350563 37161 12442 20.209033613445378 0 12435   7
350563 37161 12443 20.112723214285715 0 12435   8
350563 37161 12444   20.1706008583691 0 12435   9
350563 37161 12445   20.9779792746114 0 12435  10
350563 37161 12446          20.921875 0 12435  11
350563 37161 12449 20.755172413793105 0 12435  14
350563 37161 12450 20.549212598425196 0 12435  15
350563 37161 12451 20.198426573426573 0 12435  16
350563 37161 12452              20.45 0 12435  17
350563 37161 12453 20.887218045112782 0 12435  18
350563 37161 12456              20.65 0 12435  21
350563 37161 12457  20.61413043478261 0     .   .
350563 37161 12458 20.716535433070867 0     .   .
350563 37161 12459 20.721217105263158 0     .   .
350563 37161 12460 20.698529411764707 0     .   .
350563 37161 12463 20.645833333333332 0     .   .
350563 37161 12464 20.796677215189874 0     .   .
350563 37161 12465 20.652397260273972 0     .   .
350563 37161 12466 20.377777777777776 0     .   .
350563 37161 12467          20.159375 0     .   .
350563 37161 12471 20.113065326633166 0     .   .
350563 37161 12472 20.013392857142858 0     .   .
350563 37161 12473  19.84452736318408 0     .   .
350563 37161 12474 20.104545454545455 0     .   .
350563 37161 12477 20.220833333333335 0     .   .
350563 37161 12478 20.214139344262296 0     .   .
350563 37161 12479 19.833896396396398 0     .   .
350563 37161 12480  19.85763888888889 0     .   .
350563 37161 12481 20.074380165289256 0     .   .
350563 37161 12484 20.403508771929825 0     .   .
350563 37161 12485 20.285194174757283 0     .   .
350563 37161 12486  20.30909090909091 0     .   .
end
format %td date
format %td date_U
label values earningsannouncement earningsannouncementlabel
label def earningsannouncementlabel 0 "NO", modify
label def earningsannouncementlabel 1 "YES", modify

Note: If a firm has event dates sufficiently close that these intervals from 10 days before to 21 days after overlap, you will get some peculiar results here that may or may not be useful for you. I'm not sure how you want to handle a situation like that (or maybe that can't happen?), and if it is an issue, you need to give some guidance about that.

Regarding your concern of overlapping windows arising from sufficiently close event dates, I can probably say that something like that should not happen. As I'm looking at regular quarterly earnings announcements, there should be always around 90 calendar days between those events, so probably also around 60 trading days in between.

The advice from Robert Picard is very helpful and I was not aware that the Princeton pages are outdates. It was the first I think that popped into my eyes in the google search, but your instructions under the posted link seem to be pretty helpful.

Thank you very much in advance.