Detecting a change in the (executive) identifier number between specific years in a panel dataset

[Nicole Zander]

Dear reader,

My dataset is a panel dataset with years: 2002-2009 and firms (cusips) and executive id's (execid).
I am trying to create a variable that adopts value 1 when the execid number changes between 2007 and 2009. This means the executive was replaced between those years.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double fyear str12 cusip str6 execid
2002 "001055" "00013"
2003 "001055" "00013"
2004 "001055" "00013"
2005 "001055" "00013"
2006 "001055" "00013"
2007 "001055" "00013"
2008 "001055" "00013"
2009 "001055" "00013"
2004 "004239" "28561"
2005 "004239" "28561"
2006 "004239" "28561"
2007 "004239" "28561"
2008 "004239" "28561"
2009 "004239" "28561"
2002 "00817Y" "20970"
2003 "00817Y" "20970"
2004 "00817Y" "20970"
2005 "00817Y" "20970"
2006 "00817Y" "14660"
2007 "00817Y" "14660"
2008 "00817Y" "14660"
2009 "00817Y" "14660"

Currently I have the following:

*3.CEO turnover (2007 to 2009 not summed)
by execid cusip, sort: gen nvals = _n == 1 if inrange(fyear,2007,2009)
count if nvals 
rename nvals CEOturnover_0709_nosum

But the problem is that if I have a firm that only has data over 2007 to 2009 the variable also gives a number 1. This is incorrect because this does not mean 1 executive has taken over from antoher.

Does someone know the answer?

Regards,
Nicole

[Ariel Karlinsky]

I would tsfill the data, thereby having all years for all firms. when you don't really have data for firm XXXX in year 2006, the execid should be missing (tsfill will default to that if the panel is cusips-year anyway). than add a conditon of != .
that should solve the issue your'e having with your command.

[Nicole Zander]

This unfortunately does not work as there are repeated time values in the panel

[Nicole Zander]

I however like your way of thinking about the lag execid and execid now. I am now thinking about something like this:

gen CEOturnover = 1 if L1.execid!=execid

but I need it for inrange 2007 to 2009
and stata tells me it is not sorted even when I type

sort cusip execid fyear

Has anyone got any ideas?

[Jorrit Gosens]

gen ceochange=0
sort cusip fyear
by cusip: replace ceochange = 1 if execid!=execid[_n-1] & !missing(execid[_n-1]) & inrange(fyear,2002,2009)

You were basically nearly there already. Used 2002 instead of 2007 here as the example provided did not have a ceo change occuring in the intervals 2007-2009

edit: added a condition that execid for lagged 1 period should not be missing, otherwise the first observations for each cusip would be flagged as having a ceochange

[Nicole Zander]

Yes, super! This works but not completely how I like it. I still get a 1 for ceochange for the firm's data starting date. In other words, if the data for the firm starts at fyear=2007 then ceochange for that year is 1. I somehow now need to build in that when data is missing for any of the years: 2002 to 2009 then the CEOchange variable CANNOT be 1.

[Nick Cox]

Note that Jorrit's code can be condensed to the first statement below. It's arguable that the second form is better as distinguishing 0 (false) and . (not relevant):

 
bysort cusip (fyear): gen ceochange = execid != execid[_n-1] & inrange(fyear,2002,2009)

bysort cusip (fyear): gen ceochange = execid != execid[_n-1] if inrange(fyear,2002,2009)

[Nicole Zander]

Thank you Nick, I however still have the problem that the ceochange variable is 1 for all first fyear of data. How do I stop this?

So, I would like to say: if the data for firm x starts between 2002 and 2009, for this date ceochange = 0

[Nick Cox]

 
& _n > 1

[Jorrit Gosens]

What Nick says, or see what I wrote when I edited the message after. Didnt think of that the first time around.