keep observation before and after CEO turnover event

[Mohamed Mahmoud]

Dear Statalist members,




I want to keep observations for firms experiencing CEO turnovers, I need to do it using 2 steps

Step (1): I created a dummy variable "CEOTURNOVER" that capture this event,

Step (2) but then I need to keep the firms that have CEO turnovers and the CEOs have CEOSCORE (i.e. not equal zero) and I need to keep the observations for the year prior to the CEO turnover event (year t−1) and the year immediately after (year t+1)


I am currently struggle with step (2), any help ?

* Example generated by -dataex-. For more info, type help dataex
clear
input double fyear str8 firm_ID str6 CEO_ID float(CEOSCORE CEOTURNOVER)
2012 "00105510" "00013"   .14142193 0
2013 "00105510" "00013"   .15438877 0
2014 "00105510" "00013"     .308319 0
2016 "00105510" "00013"   .29377583 0
2017 "00105510" "00013"    .3656804 0
2018 "00105510" "00013"    .3880878 0
2019 "00105510" "00013"    .3113263 0
2015 "00130H10" "31153"   .23111297 0
2016 "00130H10" "31153"    .3418293 0
2017 "00130H10" "31153"    .3526376 0
2018 "00130H10" "31153"    .2074241 0
2019 "00130H10" "31153"    .1775206 0
2020 "00130H10" "31153"   .22762604 0
2012 "00206R10" "27541"    .9680871 0
2013 "00206R10" "27541"    .9861779 0
2014 "00206R10" "27541"   1.0536314 0
2015 "00206R10" "27541"   1.0198449 0
2016 "00206R10" "27541"    .8875941 0
2017 "00206R10" "27541"           0 0
2018 "00206R10" "27541"    .9113615 0
2019 "00206R10" "27541"    .6843244 0
2020 "00206R10" "32506"    1.315769 1
2012 "00282410" "14300"   .23474362 0
2013 "00282410" "14300"   .21548906 0
2014 "00282410" "14300"    .2256253 0
2015 "00282410" "14300"   .24286446 0
2016 "00282410" "14300"   .21370874 0
2017 "00282410" "14300"   .12845814 0
2018 "00282410" "14300"    .0838633 0
2019 "00282410" "14300"  .072604604 0
2014 "00287Y10" "18569"    .2965506 0
2015 "00287Y10" "18569"    .4581614 0
2016 "00287Y10" "18569"    .5451314 0
2017 "00287Y10" "18569"    .4895166 0
2018 "00287Y10" "18569"    .3997352 0
2019 "00287Y10" "18569"    .4576221 0
2020 "00287Y10" "18569"    .2941901 0
2019 "00365410" "43706"           0 0
2020 "00365410" "43706"           0 0
2016 "00507V10" "22978"           0 0
2017 "00507V10" "22978"           0 0
2018 "00507V10" "22978"           0 0
2019 "00507V10" "22978"           0 0
2020 "00507V10" "22978"           0 0
2017 "00508Y10" "11057"    .3098575 0
2018 "00508Y10" "11057"   .35310835 0
2014 "00724F10" "20790"           0 0
2015 "00724F10" "20790"   .05206392 0
2016 "00724F10" "20790"   .04747958 0
2017 "00724F10" "20790"  .031469774 0
2018 "00724F10" "20790"    .0373066 0
2019 "00724F10" "20790"    .0397674 0
2020 "00724F10" "20790"   .03825681 0
2016 "00751Y10" "48641"           0 0
2017 "00751Y10" "48641"           0 0
2018 "00751Y10" "48641"           0 0
2019 "00751Y10" "48641"           0 0
2021 "00751Y10" "48641"           0 0
2017 "00790310" "37809"           0 0
2018 "00790310" "37809"           0 0
2019 "00790310" "37809"           0 0
2020 "00790310" "37809"           0 0
2015 "00825210" "32736"           0 0
2016 "00825210" "32736"           0 0
2017 "00825210" "32736"           0 0
2018 "00825210" "32738"           0 1
2019 "00825210" "32739"           0 1
2014 "00846U10" "18350"    .3281893 0
2015 "00846U10" "37687"    .1077487 1
2016 "00846U10" "37687"    .3349986 0
2017 "00846U10" "37687"   .15857346 0
2018 "00846U10" "37687"   .09373965 0
2019 "00846U10" "37687"   .07556123 0
2020 "00846U10" "37687"   .07256007 0
2012 "00915810" "27315"    .4386546 0
2013 "00915810" "27315"     .325033 0
2015 "00915810" "46680" .0046068914 1
2016 "00915810" "46680"  .009098773 0
2017 "00915810" "46680"  .012773963 0
2018 "00915810" "46680"   .01336649 0
2019 "00915810" "46680"  .009777775 0
2020 "00915810" "46680"  .007338918 0
2015 "00971T10" "46050"           0 0
2016 "00971T10" "46050"           0 0
2017 "00971T10" "46050"           0 0
2018 "00971T10" "46050"           0 0
2019 "00971T10" "46050"           0 0
2020 "00971T10" "46050"           0 0
2017 "01165910" "21308"   .19934425 0
2018 "01165910" "21308"    .2287871 0
2019 "01165910" "21308"    .2365956 0
2020 "01165910" "21308"   .07926353 0
2017 "01265310" "29189"   .10692305 0
2018 "01265310" "29189"   .16795014 0
2019 "01265310" "29189"   .25165454 0
2020 "01265310" "42495"  .023588965 1
2017 "01527110" "32750"   .09867172 0
2018 "01527110" "43282"   .03911152 1
2019 "01527110" "43283"   .02508002 1
2020 "01527110" "43283"  .022963444 0
end

[Clyde Schechter]

I need to keep the firms that have CEO turnovers and the CEOs have CEOSCORE (i.e. not equal zero) and I need to keep the observations for the year prior to the CEO turnover event (year t−1) and the year immediately after (year t+1)

This is a start:

encode firm_ID, gen(firm)
xtset firm fyear

by firm (fyear): keep if inlist(1, CEOTURNOVER, L1.CEOTURNOVER, F1.CEOTURNOVER)

However, I don't understand the part about "have CEOSCORE (i.e. not equal zero)", so I have ignored that bit. I don't understand it, because the same firm can have CEOSCORE = 0 in one year and != 0 in another year. So I don't know what you mean by "have CEOSCORE" not equal to zero. Do you mean that it ever has CEOSCORE != 0, or always has CEOSCORE != 0, or has CEOSCORE != 0 in some particular year that you have not disclosed, or perhaps in the year of the turnover? Anyway, perhaps you can see your way clear to modifying the code to deal with whatever you intend about CEOSCORE. If not, post back with a clear explanation of the intended use of the CEOSCORE variable in this question.

[Mohamed Mahmoud]

Many thanks for your help,


Firstly, , With regard to "CEOscore" , I mean that the firms that have turnover event and have CEOScore in any of its years. In other words, I need to exclude firms that have turnover events and have ZERO CEOSCORE along its years, What should I add to the previous command ?




Secondly, with regard to the your command, I want to end up with three observations for each firm ( pre -event , event , post-event) to be able to run analysis around the CEO turnover event , however after running your command, - for some cases - it might end up with 2 observations ( as sometimes in my sample period the year after turnover is not available, OR the year after turnover is available and the before turnover event is not available). So Do you suggest any help to restrict my analysis for firms that have year before and year after so end with 3 observations for each firm?


Again , Many thanks for your help.

[Clyde Schechter]

encode firm_ID, gen(firm)
xtset firm fyear

by firm (fyear), sort: egen has_ceoscore = max(CEOSCORE > 0)
drop if !has_ceoscore

by firm (fyear), sort: keep if inlist(1, CEOTURNOVER, L1.CEOTURNOVER, F1.CEOTURNOVER)
by firm (fyear): drop if _N < 3

Added: On second thought, the above code could give incorrect results if, for example, the firm had a turnover in both its first and last year of data but none of the years in between. In that case, the first, second, penultimate, and last year would be retained. That would be 4 observations, but neither turnover will have three observations associated with it. So, instead:

encode firm_ID, gen(firm)
xtset firm fyear

by firm (fyear), sort: egen has_ceoscore = max(CEOSCORE > 0)
drop if !has_ceoscore

by firm (fyear), sort: keep if inlist(1, CEOTURNOVER, L1.CEOTURNOVER, F1.CEOTURNOVER) ///
    & !missing(CEOTURNOVER, L1.CEOTURNOVER, F1.CEOTURNOVER)

This will keep the observations for the year of the turnover, and the immediately preceding and following years, but only if all of those are present in the data.