Screen Panel Data for an Event Study

[Edward Wu]

Hey statalist,

I am doing an event study for a panel data, from 1997 to 2017. The event happened on 2005, so I need companies to be active at least 5 years before and after 2005, that is 2000 to 2010. How could I get rid of the companies that does not meet this crateria?

Thanks for your time!

[Clyde Schechter]

Have you ever tried going to an on-line map site to ask for driving directions to a specific destination, but not specified a starting point? That's, in effect, what you're doing here. Without knowing what data you have and how they are organized, there are many possible different answers to your questions.

To get a helpful response, please describe your data and post an example of them. Please use the -dataex- command to do that. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Edward Wu]

Thanks for pointing out!

permno is the company ID, ym (year and month) is the time series variable, POST60 is the dependent variable and (pf POSTPF POSTT PFT POSTPFT year) are the independent variables.

my panel data regression is:
xtset permno ym
xtreg POST60 pf POSTPF i.year, fe
xtreg POST60 pf POSTPF POSTT PFT POSTPFT i.year, fe

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno float(ym pf POSTPF POSTT PFT POSTPFT year POST60)
10223 635 .1 .1 0 0 0 2013  -.18860766
10223 638 .1 .1 0 0 0 2013  -.06012352
10223 641 .1 .1 0 0 0 2013 .0020511001
10223 644  1  1 0 0 0 2013    .0976818
12097 635 .9 .9 0 0 0 2013  -.14380184
12097 638 .2 .2 0 0 0 2013  -.02268785
12097 641 .2 .2 0 0 0 2013    .3213981
12097 644 .4 .4 0 0 0 2013   .04921219
12097 647 .4 .4 0 0 0 2014   -.2455008
12097 650 .9 .9 0 0 0 2014   -.1805815
12097 653 .3 .3 0 0 0 2014   -.2065947
12097 656 .2 .2 0 0 0 2014    -.151715
12097 659 .6 .6 0 0 0 2015   .05087651
12097 662 .4 .4 0 0 0 2015  -.08957732
12097 665 .4 .4 0 0 0 2015 -.002440933
12097 668 .4 .4 0 0 0 2015    .2014109
12269 575  1  1 0 0 0 2008    .0376116
12269 581 .1 .1 0 0 0 2008 .0025080144
12269 587 .3 .3 0 0 0 2009     .228875
12269 593 .1 .1 0 0 0 2009  -.14615127
12269 599 .2 .2 0 0 0 2010   .13704923
12269 605  1  1 0 0 0 2010           .
12293 635 .9 .9 0 0 0 2013           .
12293 638 .9 .9 0 0 0 2013   .24307445
12293 641 .9 .9 0 0 0 2013    .4203271
12293 644 .9 .9 0 0 0 2013    .4784073
12293 647 .8 .8 0 0 0 2014   -.4561827
12293 650 .7 .7 0 0 0 2014   .03538087
12293 653 .1 .1 0 0 0 2014   -.1870986
12293 656 .1 .1 0 0 0 2014  -.17056295
12293 659 .1 .1 0 0 0 2015    .1725235
12293 662 .1 .1 0 0 0 2015   .02305491
12293 665 .2 .2 0 0 0 2015   .06527369
12293 668 .2 .2 0 0 0 2015  -.13837591
12293 671 .2 .2 0 0 0 2016  -.01589024
12293 674 .1 .1 0 0 0 2016   -.2190113
12293 677 .2 .2 0 0 0 2016  -.31725925
12293 680 .3 .3 0 0 0 2016 -.024525404
12293 683 .9 .9 0 0 0 2017  .017095301
12293 686  1  1 0 0 0 2017    .3667759
12293 689 .9 .9 0 0 0 2017   .21536216
12293 692 .9 .9 0 0 0 2017    .8294297
12324 635 .1 .1 0 0 0 2013   .14343348
12324 638  1  1 0 0 0 2013   -.1646143
12324 641 .1 .1 0 0 0 2013 -.010914424
12324 644 .1 .1 0 0 0 2013    .3399825
12324 647 .1 .1 0 0 0 2014   .27933937
12324 650  1  1 0 0 0 2014   -.3240484
12324 653  1  1 0 0 0 2014   -.1926155
12324 656  1  1 0 0 0 2014   -.1949908
12324 659  1  1 0 0 0 2015   -.0502122
12324 662 .1 .1 0 0 0 2015    -.370761
12324 665  1  1 0 0 0 2015  -.07876363
12324 668 .9 .9 0 0 0 2015    .7796136
12325 635 .1 .1 0 0 0 2013    .2803592
12325 638 .1 .1 0 0 0 2013   .20184797
12325 641 .1 .1 0 0 0 2013    2.443892
12325 644 .1 .1 0 0 0 2013  -.44592595
12325 647  1  1 0 0 0 2014  -.39535785
12325 650  1  1 0 0 0 2014   -.2539289
12325 653  1  1 0 0 0 2014   .10112414
12325 656  1  1 0 0 0 2014    -.362195
12325 659  1  1 0 0 0 2015   -.4405292
12325 662 .2 .2 0 0 0 2015   -.3377163
12325 665 .2 .2 0 0 0 2015    .0423111
12325 668 .2 .2 0 0 0 2015    .4186866
12325 671  1  1 0 0 0 2016   .06897269
12325 674  1  1 0 0 0 2016   -.1851774
12325 677  1  1 0 0 0 2016   .02222076
12325 680 .9 .9 0 0 0 2016 -.017440662
12325 683 .2 .2 0 0 0 2017   -.1601793
12325 686  1  1 0 0 0 2017   .09755898
12325 689 .1 .1 0 0 0 2017      .48415
12325 692  1  1 0 0 0 2017   .25757122
12339 634 .8 .8 0 0 0 2013   .08090702
12339 637 .5 .5 0 0 0 2013 -.069624454
12339 640 .8 .8 0 0 0 2013    .3295352
12339 643 .8 .8 0 0 0 2013    .4320228
12339 646 .9 .9 0 0 0 2014  -.18464278
12339 649 .9 .9 0 0 0 2014    .1811164
12339 652 .7 .7 0 0 0 2014   .35120285
12339 655 .6 .6 0 0 0 2014   -.1499379
12339 658 .4 .4 0 0 0 2015   .14598866
12339 661 .4 .4 0 0 0 2015  -.03094221
12339 664 .6 .6 0 0 0 2015   .04764733
12339 667  1  1 0 0 0 2015    .4520797
12339 670 .5 .5 0 0 0 2016   .05045348
12339 673 .4 .4 0 0 0 2016   .08661167
12339 676 .4 .4 0 0 0 2016   .10197318
12339 679 .3 .3 0 0 0 2016  -.23363557
12339 682 .6 .6 0 0 0 2017   .29356682
12339 685 .7 .7 0 0 0 2017    .3042504
12339 688 .7 .7 0 0 0 2017   .38934475
12339 691 .1 .1 0 0 0 2017   .23165545
12365 635 .9 .9 0 0 0 2013   .04888651
12365 638 .7 .7 0 0 0 2013   .02644509
12365 641 .9 .9 0 0 0 2013     .705583
12365 644 .9 .9 0 0 0 2013    .3579336
12365 647 .7 .7 0 0 0 2014    .0348545
12365 650 .7 .7 0 0 0 2014    .7327781
end
format %tm ym

I appreciate your time!

[Clyde Schechter]

OK. I realize now that I also should have asked you to clarify what you mean by "active... 2000 to 2010."

The strictest definition would be that they must have data for every month from Jan 2000 through Dec 2010. The code for that would be:

xtset permno ym, monthly

local first = tm(2000m1)
local last = tm(2010m12)

local width = `last' - `first' + 1

by permno, sort: egen months_in_window = total(inrange(ym, `first', `last'))
by permno: keep if months_in_window == `width'

The loosest possibility is that there must be at least one observation from Jan 2000 or before, and at least one observation from Dec 2010 or after. The code for that would be:

by permno: egen b4_2000 = max(year(dofm(ym)) <= 2000)
by permno: egen aft_2010 = max(year(dofm(ym)) >= 2010)
by permno: keep if b4_2000 & aft_2010

You might have something else in mind than either of those. If so, post back.

Note that your example data runs only from Dec 2007 through Sep 2017, so no permno in your example data would be kept under either of these definitions. Consequently, this code isn't fully tested. If neither of these codes is what you are looking for, when you post back, please give another data example that includes at least one permno that you would keep and another that you would trim out.

[Edward Wu]

Oh I am so sorry, I forgot to mention that ym (year month) is actually quarterly, most companies report at the end of each quarter, 2005m3, 2005m6, 2005m9, 2005m12; but there are also a few companies report like 2005m1, 2005m4, 2005m7, 2005m10. Like this:
(I restricted the sample from 2003 to 2007)
2003m1 3
2003m3 48
2003m4 3
2003m5 1
2003m6 66
2003m7 3
2003m8 1
2003m9 50
2003m10 3
2003m11 1
2003m12 96
2004m1 2
2004m2 1
2004m3 70
2004m4 2
2004m5 1
2004m6 96
2004m7 2
2004m8 1
2004m9 73
2004m10 2
2004m11 1
2004m12 100
2005m1 3
2005m2 1
2005m3 76
2005m4 3
2005m5 1
2005m6 102
2005m7 3
2005m8 1
2005m9 79
2005m10 3
2005m11 1
2005m12 110
2006m1 2
2006m3 86
2006m4 3
2006m6 113
2006m7 3
2006m9 85
2006m10 3
2006m12 136
2007m1 2
2007m3 101
2007m4 2
2007m6 133
2007m7 2
2007m9 92
2007m10 2
2007m12 138

And your first code is fit to this case, that they need to have data in every quarter. (all observations were deleted since it's monthly)

Thanks a lot!!

[Clyde Schechter]

So it's a slight change. Basically, we just have to extract the quarter from the monthly date, and then do all the calculations the same way, but based on that.

gen quarterly_date = qofd(dofm(ym))
format quarterly_date %tq

local first = tq(2000q1)
local last = tq(2010q4)

local width = `last' - `first' + 1

by permno, sort: egen quarters_in_window = total(inrange(quarterly_date, `first', `last'))
by permno: keep if quarters_in_window == `width'

Again, in the example data shown, there are no companies that qualify to be retained. So this code is only partially tested.

[Edward Wu]

Thanks a lot! It totally worked! Have a nice weekend~~~

[Edward Wu]

Hmmmm I got so confused I applied the exact same code to another file and got wired results.

The CRSP daily stock dataset,

I tried:


format date %td
gen ym=mofd(date)
format ym %tm

local first = tm(2004m1)
local last = tm(2006m12)
local width = `last' - `first' + 1
by permno, sort: egen window = total(inrange(ym, `first', `last'))
by permno: keep if window == `width'

then got almost all data deleted and none in 2005 left

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date double(shrcd exchcd prc ret shrout) float ym
10001 10959 11 3   10.125                     0 1022 360
10001 10960 11 3       10  -.012345679104328156 1022 360
10001 10961 11 3       10                     0 1022 360
10001 10962 11 3 -10.0625  .0062500000931322575 1022 360
10001 10965 11 3   10.125   .006211180239915848 1022 360
10001 10966 11 3   10.125                     0 1022 360
10001 10967 11 3   10.125                     0 1022 360
10001 10968 11 3   10.125                     0 1022 360
10001 10969 11 3   10.125                     0 1022 360
10001 10972 11 3    9.875   -.02469135820865631 1022 360
10001 10973 11 3   10.125   .025316456332802773 1022 360
10001 10974 11 3   10.125                     0 1022 360
10001 10975 11 3       10  -.012345679104328156 1022 360
10001 10976 11 3       10                     0 1022 360
10001 10979 11 3       10                     0 1022 360
10001 10980 11 3    9.875  -.012500000186264515 1022 360
10001 10981 11 3       10   .012658228166401386 1022 360
10001 10982 11 3       10                     0 1022 360
10001 10983 11 3    9.875  -.012500000186264515 1022 360
10001 10986 11 3       10   .012658228166401386 1022 360
10001 10987 11 3    9.875  -.012500000186264515 1022 360
10001 10988 11 3  -9.9375   .006329114083200693 1022 360
10001 10989 11 3    9.875  -.006289307959377766 1022 361
10001 10990 11 3       10   .012658228166401386 1022 361
10001 10993 11 3    9.875  -.012500000186264515 1022 361
10001 10994 11 3       10   .012658228166401386 1022 361
10001 10995 11 3  -9.9375 -.0062500000931322575 1022 361
10001 10996 11 3  -9.9375                     0 1022 361
10001 10997 11 3    9.875  -.006289307959377766 1022 361
10001 11000 11 3    9.875                     0 1022 361
10001 11001 11 3       10   .012658228166401386 1022 361
10001 11002 11 3   -9.875  -.012500000186264515 1022 361
10001 11003 11 3       10   .012658228166401386 1022 361
10001 11004 11 3       10                     0 1022 361
10001 11008 11 3     9.75   -.02500000037252903 1022 361
10001 11009 11 3       10   .025641025975346565 1022 361
10001 11010 11 3       10                     0 1022 361
10001 11011 11 3     9.75   -.02500000037252903 1022 361
10001 11014 11 3     9.75                     0 1022 361
10001 11015 11 3       10   .025641025975346565 1022 361
10001 11016 11 3   -9.875  -.012500000186264515 1022 361
10001 11017 11 3       10   .012658228166401386 1022 362
10001 11018 11 3    9.875  -.012500000186264515 1022 362
10001 11021 11 3   10.125   .025316456332802773 1022 362
10001 11022 11 3  -9.9375  -.018518518656492233 1022 362
10001 11023 11 3   10.125    .01886792480945587 1022 362
10001 11024 11 3   10.125                     0 1022 362
10001 11025 11 3     9.75   -.03703703731298447 1022 362
10001 11028 11 3     9.75   .012820512987673283 1022 362
10001 11029 11 3   10.125    .03846153989434242 1022 362
10001 11030 11 3     9.75   -.03703703731298447 1022 362
10001 11031 11 3       10   .025641025975346565 1022 362
10001 11032 11 3  -9.9375 -.0062500000931322575 1022 362
10001 11035 11 3   10.125    .01886792480945587 1022 362
10001 11036 11 3     9.75   -.03703703731298447 1022 362
10001 11037 11 3   10.125    .03846153989434242 1022 362
10001 11038 11 3  -9.9375  -.018518518656492233 1022 362
10001 11039 11 3     9.75   -.01886792480945587 1022 362
10001 11042 11 3   10.125    .03846153989434242 1022 362
10001 11043 11 3   10.125                     0 1022 362
10001 11044 11 3   10.125                     0 1022 362
10001 11045 11 3  -9.9375  -.018518518656492233 1022 362
10001 11046 11 3   -9.875  -.006289307959377766 1027 362
10001 11049 11 3   -9.875                     0 1027 363
10001 11050 11 3   -9.875                     0 1027 363
10001 11051 11 3     9.75  -.012658228166401386 1027 363
10001 11052 11 3       10   .025641025975346565 1027 363
10001 11053 11 3       10                     0 1027 363
10001 11056 11 3   -9.875  -.012500000186264515 1027 363
10001 11057 11 3   -9.875                     0 1027 363
10001 11058 11 3       10   .012658228166401386 1027 363
10001 11059 11 3       10                     0 1027 363
10001 11063 11 3     9.75   -.02500000037252903 1027 363
10001 11064 11 3       10   .025641025975346565 1027 363
10001 11065 11 3       10                     0 1027 363
10001 11066 11 3   -9.875  -.012500000186264515 1027 363
10001 11067 11 3     9.75  -.012658228166401386 1027 363
10001 11070 11 3       10   .025641025975346565 1027 363
10001 11071 11 3   -9.875  -.012500000186264515 1027 363
10001 11072 11 3   -9.875                     0 1027 363
10001 11073 11 3   -9.875                     0 1027 363
10001 11074 11 3       10   .012658228166401386 1027 363
10001 11077 11 3   -9.875  -.012500000186264515 1027 363
10001 11078 11 3     9.75  -.012658228166401386 1027 364
10001 11079 11 3   -9.875   .012820512987673283 1027 364
10001 11080 11 3       10   .012658228166401386 1027 364
10001 11081 11 3     9.75   -.02500000037252903 1027 364
10001 11084 11 3   -9.875   .012820512987673283 1027 364
10001 11085 11 3   -9.875                     0 1027 364
10001 11086 11 3   -9.875                     0 1027 364
10001 11087 11 3       10   .012658228166401386 1027 364
10001 11088 11 3    9.875  -.012500000186264515 1027 364
10001 11091 11 3     9.75  -.012658228166401386 1027 364
10001 11092 11 3       10   .025641025975346565 1027 364
10001 11093 11 3     9.75   -.02500000037252903 1027 364
10001 11094 11 3     9.75                     0 1027 364
10001 11095 11 3       10   .025641025975346565 1027 364
10001 11098 11 3     9.75   -.02500000037252903 1027 364
10001 11099 11 3       10   .025641025975346565 1027 364
10001 11100 11 3     9.75   -.02500000037252903 1027 364
end
format %td date
format %tm ym

Could you see what's wrong?

[Edward Wu]

I tried daily:
local first = td(02jan2004)
local last = td(09dec2006)
local width = `last' - `first' + 1
by permno, sort: egen window = total(inrange(date, `first', `last'))
by permno: keep if window == `width'
and got all data deleted....

[Clyde Schechter]

This data set is radically different from your original one and is not suitable for the code developed earlier.

In this data set, the date variable is basically daily, not monthly, although there is the additional complication that it appears to skip weekends. The code developed earlier worked by counting the number of observations that fell between the first and last date of the window and then comparing that to the total number of possible dates in that window. Here, the total number of possible dates can't just be calculated by subtracting first from last and adding 1 because it is expected that weekends and holidays will be skipped. So this requires the use of a business calendar. I don't work with business calendars, so I can't help you with that beyond referring you to -help business calendar-. Read that and the associated chapter in the PDF documentation to learn how to translate these regular dates into business dates. Then, using the business dates, the logic of the code will work if you set up the business calendar correctly.

I should also point out that your example data is all in year 1990, so none of that would be within the window you are interested in anyway.

[Edward Wu]

Sure thanks!