Dummy for recording change

[Jozef Patrnciak]

Hi, I would like to create a dummy which would record if any executive changes between two years and zero otherwise. Normally I would not have problem with this I would first generate the first differences of executive identifier and afterwards replace any non-zero values by one. However, in my case I have also multiple executives per firm in a given year and I dont really know how to handle it.

Here is example of the dataset, thanks in advance for any help:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int fyear long execno str5 tickersymbol
2007 45344 "FF"   
2007 45341 "FF"   
2007 45348 "FF"   
2007 45349 "FF"   
2007 45340 "FF"   
2007 45347 "FF"   
2007 45343 "FF"   
2007 45345 "FF"   
2008 45340 "FF"   
2008 45348 "FF"   
2008 45344 "FF"   
2008 45349 "FF"   
2008 45339 "FF"   
2008 45345 "FF"   
2008 45343 "FF"   
2008 45341 "FF"   
2009 45341 "FF"   
2009 45345 "FF"   
2009 45349 "FF"   
2009 45344 "FF"   
2009 45340 "FF"   
2009 45343 "FF"   
2009 45348 "FF"   
2009 45339 "FF"   
2010 45340 "FF"   
2010 45344 "FF"   
2010 45348 "FF"   
2010 45349 "FF"   
2010 45345 "FF"   
2010 45341 "FF"   
2011 45340 "FF"   
2011 45341 "FF"   
2011 45349 "FF"   
2011 45339 "FF"   
2011 45344 "FF"   
2011 45348 "FF"   
2011 45346 "FF"   
2011 45343 "FF"   
2012 45343 "FF"   
2012 45342 "FF"   
2012 45340 "FF"   
2012 45349 "FF"   
2012 45339 "FF"   
2012 45344 "FF"   
2012 45341 "FF"   
2012 45346 "FF"   
2013 45339 "FF"   
2013 45343 "FF"   
2013 45344 "FF"   
2013 45349 "FF"   
2013 45346 "FF"   
2013 45340 "FF"   
2013 45342 "FF"   
2014 45340 "FF"   
2014 47215 "FF"   
2014 45349 "FF"   
2014 45339 "FF"   
2014 45342 "FF"   
2014 45343 "FF"   
2015 45340 "FF"   
2015 47215 "FF"   
2015 45339 "FF"   
2015 45349 "FF"   
2015 45342 "FF"   
2015 45343 "FF"   
2016 45343 "FF"   
2016 45340 "FF"   
2016 45342 "FF"   
2016 47215 "FF"   
2017 45340 "FF"   
2017 45343 "FF"   
2017 42577 "FF"   
2017 47215 "FF"   
2017 45342 "FF"   
2007 43361 "GSM.3"
2007 43356 "GSM.3"
2007 22272 "GSM.3"
2007 43358 "GSM.3"
2008 43358 "GSM.3"
2008 43355 "GSM.3"
2008 43356 "GSM.3"
2008 43361 "GSM.3"
2008 22272 "GSM.3"
2008 43359 "GSM.3"
2009 43361 "GSM.3"
2009 43356 "GSM.3"
2009 43359 "GSM.3"
2009 43360 "GSM.3"
2009 43358 "GSM.3"
2009 43355 "GSM.3"
2009 22272 "GSM.3"
2010 43356 "GSM.3"
2010 43361 "GSM.3"
2010 43355 "GSM.3"
2010 43360 "GSM.3"
2010 43359 "GSM.3"
2011 43361 "GSM.3"
2011 43359 "GSM.3"
2011 43360 "GSM.3"
2011 43355 "GSM.3"
end

[Joro Kolev]

Without knowing the logic behind this data, I think it is impossible to answer the question.

For example for ticker FF, execno 42577 appears only for year 2017. What does this mean? That the executive was hired in 2017 and fired in 2018?

Then for the same ticker, execno 45339 appears years 2008-2009, disappears in year 2010, then appears 2011 to 2015. What is the meaning of this? Etc.

[William Lisowski]

Here is some code that may start you in a helpful direction, if I have understood your objective correctly. I have interpreted it as wanting to create the variable "changed" which for a given combination of tickersymbol and fyear takes the value 1 if the collection of executives in that fyear is different than the collection in the previous fyear.

generate changed = 0
bysort tickersymbol fyear (execno): generate exec_seq = _n
bysort tickersymbol exec_seq (fyear): replace changed = 1 if _n>1 & execno != execno[_n-1]
drop exec_seq
bysort tickersymbol fyear (changed): replace changed = changed[_N]
sort tickersymbol fyear execno
list, sepby(tickersymbol fyear) noobs abbreviate(12)

. list, sepby(tickersymbol fyear) noobs abbreviate(12)

  +-----------------------------------------+
  | fyear   execno   tickersymbol   changed |
  |-----------------------------------------|
  |  2007    45340             FF         0 |
  |  2007    45341             FF         0 |
  |  2007    45343             FF         0 |
  |  2007    45344             FF         0 |
  |  2007    45345             FF         0 |
  |  2007    45347             FF         0 |
  |  2007    45348             FF         0 |
  |  2007    45349             FF         0 |
  |-----------------------------------------|
  |  2008    45339             FF         1 |
  |  2008    45340             FF         1 |
  |  2008    45341             FF         1 |
  |  2008    45343             FF         1 |
  |  2008    45344             FF         1 |
  |  2008    45345             FF         1 |
  |  2008    45348             FF         1 |
  |  2008    45349             FF         1 |
  |-----------------------------------------|
  |  2009    45339             FF         0 |
  |  2009    45340             FF         0 |
  |  2009    45341             FF         0 |
  |  2009    45343             FF         0 |
  |  2009    45344             FF         0 |
  |  2009    45345             FF         0 |
  |  2009    45348             FF         0 |
  |  2009    45349             FF         0 |
  |-----------------------------------------|
  |  2010    45340             FF         1 |
  |  2010    45341             FF         1 |
  |  2010    45344             FF         1 |
  |  2010    45345             FF         1 |
  |  2010    45348             FF         1 |
  |  2010    45349             FF         1 |
  |-----------------------------------------|
  |  2011    45339             FF         1 |
  |  2011    45340             FF         1 |
  |  2011    45341             FF         1 |
  |  2011    45343             FF         1 |
  |  2011    45344             FF         1 |
  |  2011    45346             FF         1 |
  |  2011    45348             FF         1 |
  |  2011    45349             FF         1 |
  |-----------------------------------------|
  |  2012    45339             FF         1 |
  |  2012    45340             FF         1 |
  |  2012    45341             FF         1 |
  |  2012    45342             FF         1 |
  |  2012    45343             FF         1 |
  |  2012    45344             FF         1 |
  |  2012    45346             FF         1 |
  |  2012    45349             FF         1 |
  |-----------------------------------------|
  |  2013    45339             FF         1 |
  |  2013    45340             FF         1 |
  |  2013    45342             FF         1 |
  |  2013    45343             FF         1 |
  |  2013    45344             FF         1 |
  |  2013    45346             FF         1 |
  |  2013    45349             FF         1 |
  |-----------------------------------------|
  |  2014    45339             FF         1 |
  |  2014    45340             FF         1 |
  |  2014    45342             FF         1 |
  |  2014    45343             FF         1 |
  |  2014    45349             FF         1 |
  |  2014    47215             FF         1 |
  |-----------------------------------------|
  |  2015    45339             FF         0 |
  |  2015    45340             FF         0 |
  |  2015    45342             FF         0 |
  |  2015    45343             FF         0 |
  |  2015    45349             FF         0 |
  |  2015    47215             FF         0 |
  |-----------------------------------------|
  |  2016    45340             FF         1 |
  |  2016    45342             FF         1 |
  |  2016    45343             FF         1 |
  |  2016    47215             FF         1 |
  |-----------------------------------------|
  |  2017    42577             FF         1 |
  |  2017    45340             FF         1 |
  |  2017    45342             FF         1 |
  |  2017    45343             FF         1 |
  |  2017    47215             FF         1 |
  |-----------------------------------------|
  |  2007    22272          GSM.3         0 |
  |  2007    43356          GSM.3         0 |
  |  2007    43358          GSM.3         0 |
  |  2007    43361          GSM.3         0 |
  |-----------------------------------------|
  |  2008    22272          GSM.3         1 |
  |  2008    43355          GSM.3         1 |
  |  2008    43356          GSM.3         1 |
  |  2008    43358          GSM.3         1 |
  |  2008    43359          GSM.3         1 |
  |  2008    43361          GSM.3         1 |
  |-----------------------------------------|
  |  2009    22272          GSM.3         1 |
  |  2009    43355          GSM.3         1 |
  |  2009    43356          GSM.3         1 |
  |  2009    43358          GSM.3         1 |
  |  2009    43359          GSM.3         1 |
  |  2009    43360          GSM.3         1 |
  |  2009    43361          GSM.3         1 |
  |-----------------------------------------|
  |  2010    43355          GSM.3         1 |
  |  2010    43356          GSM.3         1 |
  |  2010    43359          GSM.3         1 |
  |  2010    43360          GSM.3         1 |
  |  2010    43361          GSM.3         1 |
  |-----------------------------------------|
  |  2011    43355          GSM.3         1 |
  |  2011    43359          GSM.3         1 |
  |  2011    43360          GSM.3         1 |
  |  2011    43361          GSM.3         1 |
  +-----------------------------------------+