Tracking changes in dataset

[Rens Martens]

Hi,

I tracked executives across firms and retained those executives who were employed for at least three years in each of at least two different firms. In addition, I breakdown executives by their title (e.g. CEO, CFO, OTHER), however i want to include two variables which provide the current title (i.e. the last title listed for the executive in dataset) and their prior title. For instance (see dataset), execid 28 moved from company 9563 with title OTHER (= prior title) to 28349 with title CEO (=current title). I am searching for a code that track these executive changes from companies and provide their current en prior title.

gvkey	execid	fyear	CEO	CFO	OTHER
1246	19	1992	1	0	0
1246	19	1993	1	0	0
1246	19	1994	1	0	0
64117	19	1996	1	0	0
64117	19	1997	1	0	0
64117	19	1998	1	0	0
9563	28	1992	0	0	1
9563	28	1993	0	0	1
9563	28	1994	0	0	1
28349	28	1992	1	0	0
28349	28	1993	1	0	0
28349	28	1994	1	0	0
13099	523	1992	0	0	1
13099	523	1993	0	0	1
13099	523	1994	0	0	1
113419	523	2003	0	1	0
113419	523	2004	0	1	0
113419	523	2005	0	1	0
113419	523	2006	0	1	0

[Clyde Schechter]

This is less complicated if you combine the CEO, CFO, and OTHER variables into a single categorical variable with three levels.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey int(execid fyear) byte(ceo cfo other)
  1246  19 1992 1 0 0
  1246  19 1993 1 0 0
  1246  19 1994 1 0 0
 64117  19 1996 1 0 0
 64117  19 1997 1 0 0
 64117  19 1998 1 0 0
  9563  28 1992 0 0 1
  9563  28 1993 0 0 1
  9563  28 1994 0 0 1
 28349  28 1992 1 0 0
 28349  28 1993 1 0 0
 28349  28 1994 1 0 0
 13099 523 1992 0 0 1
 13099 523 1993 0 0 1
 13099 523 1994 0 0 1
113419 523 2003 0 1 0
113419 523 2004 0 1 0
113419 523 2005 0 1 0
113419 523 2006 0 1 0
end

label define title  1   "CEO"   ///
                    2   "CFO"   ///
                    3   "OTHER"
gen title:title = 1 if ceo
replace title = 2 if cfo
replace title = 3 if other

by execid (fyear), sort: gen original_title:title = title[1]
by execid (fyear): gen current_title:title = title[_N]

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- 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.

[Rens Martens]

Thank you very much!

How does this work when there is 1 executive who has worked for 3 different companies and has for instance has 3 different titles? How interpret the command the current and prior title then? Because in my dataset there are executives that are changed 4 times (this is maximum) from companies.

[Clyde Schechter]

The code will give you the first as the original title and the last as the current title. The ones in between are ignored. That is what I understood you wanted. If not, please clarify how you want to handle this situation.

[Rens Martens]

Yes that make sense. I think i have an additional question than!

In the case of executives who were employed by more than two firms for at least three years, i only want to have the last change only (most recent change). So, if for instance executive 1 has worked in company A between 1992-1995, B between 1996-1999 and C 2000-2003 with the titles Other, CEO and CEO respectively. Than i want to present the Prior title, which is CEO of company B and the current title, which is CEO of company C instead of original title which is now the case. Is this possible?

I hope you understand what i mean now? Excuses for the misunderstanding from me.

[Clyde Schechter]

Your previous example data does not contain any execid's with more than two employers. So I added on an additional employer at the end for execid 523 to demonstrate the code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey int(execid fyear) byte(ceo cfo other)
  1246  19 1992 1 0 0
  1246  19 1993 1 0 0
  1246  19 1994 1 0 0
 64117  19 1996 1 0 0
 64117  19 1997 1 0 0
 64117  19 1998 1 0 0
  9563  28 1992 0 0 1
  9563  28 1993 0 0 1
  9563  28 1994 0 0 1
 13099 523 1992 0 0 1
 13099 523 1993 0 0 1
 13099 523 1994 0 0 1
113419 523 2003 0 1 0
113419 523 2004 0 1 0
113419 523 2005 0 1 0
113419 523 2006 0 1 0
123456 523 2007 1 0 0
123456 523 2008 1 0 0
end

isid execid fyear, sort

label define title  1   "CEO"   ///
                    2   "CFO"   ///
                    3   "OTHER"
gen title:title = 1 if ceo
replace title = 2 if cfo
replace title = 3 if other

//  IDENTIFY SPELLS OF EMPLOYMENT AT THE SAME FIRM
by execid (fyear), sort: gen job_num = sum(gvkey != gvkey[_n-1])
by execid (fyear job_num), sort: gen last_job_num = job_num[_N]

//  NOW CALCULATE WANTED VARIABLES
by execid (fyear job_num), sort: egen previous_title ///
    = max(cond(job_num == last_job_num-1, title, .))
by execid (fyear job_num): egen final_title ///
    = max(cond(job_num == last_job_num, title, .))
label values *_title title
sort execid fyear

I also modified your data example in another way. In your original example, execid 28 works for two employers in each of years 1992 through 1994. I have eliminated one of those employers. First, the code above will not work properly if an exec has more than one employer in the same year. And your request actually is incoherent in that condition because it is impossible to tell from the data which of those employers is to be considered "last" and which "second to last" or "before second to last." (In fact in the original request, it is still incoherent because there is no way to know which is prior and which is current.) And while I do not live in the world of commerce, I have the sense that it would not be possible for the same person to hold these C-suite positions in two firms at the same time. So I'm assuming those examples were errors. The modified code checks for this and will halt with an error message if it finds a violation of this condition.

[Rens Martens]

No that is correct, i only paste a subsample of my dataset in the first post. My dataset contains approximately 11.000 observations. But you are correct that it does not make sense that executive 28 has 2 employers in the same years. So i consider to delete these observation if this is the case.

Thank you for the help!

[Esther Buttner]

Hi all,
I was wondering if someone could help me with my problem. I was trying to do the same as Rens Martens described earlier. However, if I run the code of Clyde, which I appreciated very much, it is not correct for every company. My problem is shown below, for example gvkey1 1246, should be previous_title 1 and final_title should be 3, but the code shows previous_title 3 and final_title 3.

Thank you in advance!

 * Example generated by -dataex-. To install: ssc install dataex
clear
input double(CO_PER_ROL fyear) byte(others ceoann1 cfoann1) str38 EXEC_FULLNAME long(gvkey1 CompanyID) int EXECID float(title job_num last_job_num previous_title final_title)
 4977 1992 1 0 0 "Eric A. Benhamou"      10553  10553   2 3 1 6 1 3
 4977 1993 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 1 6 1 3
 4977 1994 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 1 6 1 3
 4977 1995 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 1 6 1 3
 4977 1996 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 1 6 1 3
 4977 1997 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 1 6 1 3
20172 1998 1 0 0 "Eric A. Benhamou"     132502 132502   2 3 2 6 1 3
 4977 1998 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 3 6 1 3
20172 1999 0 1 0 "Eric A. Benhamou"     132502 132502   2 1 4 6 1 3
 4977 1999 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 5 6 1 3
 4977 2000 0 1 0 "Eric A. Benhamou"      10553  10553   2 1 5 6 1 3
20172 2001 0 1 0 "Eric A. Benhamou"     132502 132502   2 1 6 6 1 3
20172 2002 0 1 0 "Eric A. Benhamou"     132502 132502   2 1 6 6 1 3
20172 2003 1 0 0 "Eric A. Benhamou"     132502 132502   2 3 6 6 1 3
   50 1992 0 1 0 "Ray B. Mundt"           1246   1246  19 1 1 2 3 3
   50 1993 0 1 0 "Ray B. Mundt"           1246   1246  19 1 1 2 3 3
   50 1994 1 0 0 "Ray B. Mundt"           1246   1246  19 3 1 2 3 3
11648 1996 1 0 0 "Ray B. Mundt"          64117  64117  19 3 2 2 3 3
11648 1997 0 1 0 "Ray B. Mundt"          64117  64117  19 1 2 2 3 3
11648 1998 0 1 0 "Ray B. Mundt"          64117  64117  19 1 2 2 3 3
 4387 1992 1 0 0 "Robert P. Bozzone"     13708  13708  23 3 1 2 3 3
 4387 1993 0 1 0 "Robert P. Bozzone"     13708  13708  23 1 1 2 3 3
 4387 1994 0 1 0 "Robert P. Bozzone"     13708  13708  23 1 1 2 3 3
21107 2000 1 0 0 "Robert P. Bozzone"     10405  10405  23 3 2 2 3 3
21107 2001 1 0 0 "Robert P. Bozzone"     10405  10405  23 3 2 2 3 3
 5069 1992 1 0 0 "Wayne E. Hedien"       28349  28349  28 3 1 4 1 3
 2012 1992 1 0 0 "Wayne E. Hedien"        9563   9563  28 3 2 4 1 3
 2012 1993 1 0 0 "Wayne E. Hedien"        9563   9563  28 3 2 4 1 3
 5069 1993 0 1 0 "Wayne E. Hedien"       28349  28349  28 1 3 4 1 3
 5069 1994 0 1 0 "Wayne E. Hedien"       28349  28349  28 1 3 4 1 3
 2012 1994 1 0 0 "Wayne E. Hedien"        9563   9563  28 3 4 4 1 3
 2658 1992 1 0 0 "John M. Lillie"         1543   1543  47 3 1 2 3 3
 2658 1993 0 1 0 "John M. Lillie"         1543   1543  47 1 1 2 3 3
 2658 1994 0 1 0 "John M. Lillie"         1543   1543  47 1 1 2 3 3
 2658 1995 0 1 0 "John M. Lillie"         1543   1543  47 1 1 2 3 3
23802 2000 1 0 0 "John M. Lillie"         4990   4990  47 3 2 2 3 3
23802 2001 1 0 0 "John M. Lillie"         4990   4990  47 3 2 2 3 3
23802 2002 1 0 0 "John M. Lillie"         4990   4990  47 3 2 2 3 3
 2707 1992 1 0 0 "Byron O. Pond"          1786   1786  75 3 1 2 3 1
 2707 1993 0 1 0 "Byron O. Pond"          1786   1786  75 1 1 2 3 1
 2707 1994 0 1 0 "Byron O. Pond"          1786   1786  75 1 1 2 3 1
 2707 1995 0 1 0 "Byron O. Pond"          1786   1786  75 1 1 2 3 1
 2707 1996 0 1 0 "Byron O. Pond"          1786   1786  75 1 1 2 3 1
 2707 1997 0 1 0 "Byron O. Pond"          1786   1786  75 1 1 2 3 1
 2707 1998 1 0 0 "Byron O. Pond"          1786   1786  75 3 1 2 3 1
23175 2001 0 1 0 "Byron O. Pond"          1372   1372  75 1 2 2 3 1
23175 2002 0 1 0 "Byron O. Pond"          1372   1372  75 1 2 2 3 1
23175 2003 0 1 0 "Byron O. Pond"          1372   1372  75 1 2 2 3 1
  216 1992 0 1 0 "Lodwrick Monroe Cook"   1848   1848  83 1 1 2 3 3
  216 1993 0 1 0 "Lodwrick Monroe Cook"   1848   1848  83 1 1 2 3 3
  216 1994 0 1 0 "Lodwrick Monroe Cook"   1848   1848  83 1 1 2 3 3
  216 1995 1 0 0 "Lodwrick Monroe Cook"   1848   1848  83 3 1 2 3 3
21792 1998 1 0 0 "Lodwrick Monroe Cook" 113491 113491  83 3 2 2 3 3
21792 1999 1 0 0 "Lodwrick Monroe Cook" 113491 113491  83 3 2 2 3 3
21792 2000 1 0 0 "Lodwrick Monroe Cook" 113491 113491  83 3 2 2 3 3
  319 1992 0 1 0 "Raymond V. Gilmartin"   2111   2111 116 1 1 4 1 3
  319 1993 0 1 0 "Raymond V. Gilmartin"   2111   2111 116 1 1 4 1 3
 4856 1994 1 0 0 "Raymond V. Gilmartin"   7257   7257 116 3 2 4 1 3
  319 1994 0 1 0 "Raymond V. Gilmartin"   2111   2111 116 1 3 4 1 3
 4856 1995 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 1996 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 1997 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 1998 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 1999 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 2000 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 2001 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 2002 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 2003 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 2004 0 1 0 "Raymond V. Gilmartin"   7257   7257 116 1 4 4 1 3
 4856 2005 1 0 0 "Raymond V. Gilmartin"   7257   7257 116 3 4 4 1 3
 5133 1992 1 0 0 "Clefton D. Vaughan"     7620   7620 119 3 1 4 3 3
 5133 1993 1 0 0 "Clefton D. Vaughan"     7620   7620 119 3 1 4 3 3
 5133 1994 1 0 0 "Clefton D. Vaughan"     7620   7620 119 3 1 4 3 3
 5133 1995 1 0 0 "Clefton D. Vaughan"     7620   7620 119 3 1 4 3 3
18443 1996 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 2 4 3 3
 5133 1996 1 0 0 "Clefton D. Vaughan"     7620   7620 119 3 3 4 3 3
18443 1997 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 1998 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 1999 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 2000 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 2001 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 2002 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 2003 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 2004 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 2005 1 0 0 "Clefton D. Vaughan"    64135  64135 119 3 4 4 3 3
18443 2006 0 0 1 "Clefton D. Vaughan"    64135  64135 119 2 4 4 3 3
  652 1992 0 1 0 "H. Wayne Huizenga"      3491   3491 137 1 1 2 1 3
  652 1993 0 1 0 "H. Wayne Huizenga"      3491   3491 137 1 1 2 1 3
12219 1995 1 0 0 "H. Wayne Huizenga"      9063   9063 137 3 2 2 1 3
12219 1996 0 1 0 "H. Wayne Huizenga"      9063   9063 137 1 2 2 1 3
12219 1997 0 1 0 "H. Wayne Huizenga"      9063   9063 137 1 2 2 1 3
12219 1998 0 1 0 "H. Wayne Huizenga"      9063   9063 137 1 2 2 1 3
12219 1999 0 1 0 "H. Wayne Huizenga"      9063   9063 137 1 2 2 1 3
12219 2000 1 0 0 "H. Wayne Huizenga"      9063   9063 137 3 2 2 1 3
12219 2001 1 0 0 "H. Wayne Huizenga"      9063   9063 137 3 2 2 1 3
 2777 1992 1 0 0 "Robert B. Catell"       2424   2424 152 3 1 6 1 1
 2777 1993 0 1 0 "Robert B. Catell"       2424   2424 152 1 1 6 1 1
 2777 1994 0 1 0 "Robert B. Catell"       2424   2424 152 1 1 6 1 1
 2777 1995 0 1 0 "Robert B. Catell"       2424   2424 152 1 1 6 1 1
 2777 1996 0 1 0 "Robert B. Catell"       2424   2424 152 1 1 6 1 1
end
label values title title
label values previous_title title
label values final_title title
label def title 1 "CEO", modify
label def title 2 "CFO", modify
label def title 3 "OTHER", modify

[Clyde Schechter]

Rens Martens posted a few different clarifications of what he wanted, and I posted a few different code solutions in response to the changing understanding. Without knowing exactly what you want to do, and which code you used to do it, I can't help you, nor do I see how anyone else can.

Please post back with a clear statement of what you want to accomplish, and showing the exact code you used.

[Esther Buttner]

First of all, thank you for the quick response! I'm sorry that I wrote my question incomplete. I hope, it is clear now.

I tracked executives across firms and retained those executives who were employed for at least two years in each of at least two different firms. In addition, I breakdown executives by their title (e.g. CEO, CFO, OTHER). I want to create two variables which provide the current title (i.e. the last title listed for the executive in dataset) and their prior title. For instance (see dataset), execid 19 moved from company 1246 with title CEO (= prior title) to 64117 with title OTHERS (=current title). I am searching for a code that track these executive changes from companies and provide their current en prior title. In my dataset some executives have worked at three companies in that case I want the first company as prior title and the last company as current title. If the executive worked at the same time at two different companies, one company can be deleted. The dataex I have is shown below.

I tried to get the two variables prior title and current title using the code shown below. However if I use this code, the prior title and final title are not always correct. Unfortunetely, it sometimes shows the correct prior and final title and sometimes it doesn't and I have no idea why it is sometimes incorrect.

I hope I made myself clear, if not please tell me!

Thank you in advance!

ps: if I need to post my data after using the code, please let me know. It is also shown in my previous post #8.


The code I used:
*make byte van de others ceoann1 cfoann1*
recast byte others
recast byte ceoann1
recast byte cfoann1

*make from gvkey1 a int*
recast int gvkey1


label define title 1 "CEO" ///
2 "CFO" ///
3 "OTHER"
gen title:title = 1 if ceo
replace title = 2 if cfo
replace title = 3 if other

// IDENTIFY SPELLS OF EMPLOYMENT AT THE SAME FIRM
by EXECID (fyear), sort: gen job_num = sum(gvkey1 != gvkey1[_n-1])
by EXECID (fyear job_num), sort: gen last_job_num = job_num[_N]

// NOW CALCULATE WANTED VARIABLES
by EXECID (fyear job_num), sort: egen previous_title ///
= max(cond(job_num == last_job_num-1, title, .))
by EXECID (fyear job_num): egen final_title ///
= max(cond(job_num == last_job_num, title, .))
label values *_title title
sort EXECID fyear

sort EXECID
quietly by EXECID : gen dup2 = cond(_N==1,0,_n)
tabulate dup2
drop if dup2>1

tostring (previous_title), gen (pt)
tostring (final_title), gen (ft)
tabulate pt ft

The data from STATA before using the code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double fyear float(others ceoann1 cfoann1) str28 C long gvkey1 int EXECID
1992 1 0 0 "3COM CORP"                   10553   2
1993 0 1 0 "3COM CORP"                   10553   2
1994 0 1 0 "3COM CORP"                   10553   2
1995 0 1 0 "3COM CORP"                   10553   2
1996 0 1 0 "3COM CORP"                   10553   2
1997 0 1 0 "3COM CORP"                   10553   2
1998 1 0 0 "PALM INC"                   132502   2
1998 0 1 0 "3COM CORP"                   10553   2
1999 0 1 0 "3COM CORP"                   10553   2
1999 0 1 0 "PALM INC"                   132502   2
2000 0 1 0 "3COM CORP"                   10553   2
2001 0 1 0 "PALM INC"                   132502   2
2002 0 1 0 "PALM INC"                   132502   2
2003 1 0 0 "PALM INC"                   132502   2
1992 0 1 0 "IKON OFFICE SOLUTIONS"        1246  19
1993 0 1 0 "IKON OFFICE SOLUTIONS"        1246  19
1994 1 0 0 "IKON OFFICE SOLUTIONS"        1246  19
1996 1 0 0 "UNISOURCE WORLDWIDE INC"     64117  19
1997 0 1 0 "UNISOURCE WORLDWIDE INC"     64117  19
1998 0 1 0 "UNISOURCE WORLDWIDE INC"     64117  19
1992 1 0 0 "ALLEGHENY LUDLUM CORP"       13708  23
1993 0 1 0 "ALLEGHENY LUDLUM CORP"       13708  23
1994 0 1 0 "ALLEGHENY LUDLUM CORP"       13708  23
2000 1 0 0 "ALLEGHENY TECHNOLOGIES INC"  10405  23
2001 1 0 0 "ALLEGHENY TECHNOLOGIES INC"  10405  23
1992 1 0 0 "SEARS ROEBUCK & CO"           9563  28
1992 1 0 0 "ALLSTATE CORP"               28349  28
1993 0 1 0 "ALLSTATE CORP"               28349  28
1993 1 0 0 "SEARS ROEBUCK & CO"           9563  28
1994 0 1 0 "ALLSTATE CORP"               28349  28
1994 1 0 0 "SEARS ROEBUCK & CO"           9563  28
1992 1 0 0 "APL LTD"                      1543  47
1993 0 1 0 "APL LTD"                      1543  47
1994 0 1 0 "APL LTD"                      1543  47
1995 0 1 0 "APL LTD"                      1543  47
2000 1 0 0 "GAP INC"                      4990  47
2001 1 0 0 "GAP INC"                      4990  47
2002 1 0 0 "GAP INC"                      4990  47
1992 1 0 0 "ARVIN INDUSTRIES INC"         1786  75
1993 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1994 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1995 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1996 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1997 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1998 1 0 0 "ARVIN INDUSTRIES INC"         1786  75
2001 0 1 0 "AMCAST INDUSTRIAL CORP"       1372  75
2002 0 1 0 "AMCAST INDUSTRIAL CORP"       1372  75
2003 0 1 0 "AMCAST INDUSTRIAL CORP"       1372  75
1992 0 1 0 "ATLANTIC RICHFIELD CO"        1848  83
1993 0 1 0 "ATLANTIC RICHFIELD CO"        1848  83
1994 0 1 0 "ATLANTIC RICHFIELD CO"        1848  83
1995 1 0 0 "ATLANTIC RICHFIELD CO"        1848  83
1998 1 0 0 "GLOBAL CROSSING LTD"        113491  83
1999 1 0 0 "GLOBAL CROSSING LTD"        113491  83
2000 1 0 0 "GLOBAL CROSSING LTD"        113491  83
1992 0 1 0 "BECTON DICKINSON & CO"        2111 116
1993 0 1 0 "BECTON DICKINSON & CO"        2111 116
1994 1 0 0 "MERCK & CO"                   7257 116
1994 0 1 0 "BECTON DICKINSON & CO"        2111 116
1995 0 1 0 "MERCK & CO"                   7257 116
1996 0 1 0 "MERCK & CO"                   7257 116
1997 0 1 0 "MERCK & CO"                   7257 116
1998 0 1 0 "MERCK & CO"                   7257 116
1999 0 1 0 "MERCK & CO"                   7257 116
2000 0 1 0 "MERCK & CO"                   7257 116
2001 0 1 0 "MERCK & CO"                   7257 116
2002 0 1 0 "MERCK & CO"                   7257 116
2003 0 1 0 "MERCK & CO"                   7257 116
2004 0 1 0 "MERCK & CO"                   7257 116
2005 1 0 0 "MERCK & CO"                   7257 116
1992 1 0 0 "MURPHY OIL CORP"              7620 119
1993 1 0 0 "MURPHY OIL CORP"              7620 119
1994 1 0 0 "MURPHY OIL CORP"              7620 119
1995 1 0 0 "MURPHY OIL CORP"              7620 119
1996 1 0 0 "MURPHY OIL CORP"              7620 119
1996 1 0 0 "DELTIC TIMBER CORP"          64135 119
1997 1 0 0 "DELTIC TIMBER CORP"          64135 119
1998 1 0 0 "DELTIC TIMBER CORP"          64135 119
1999 1 0 0 "DELTIC TIMBER CORP"          64135 119
2000 1 0 0 "DELTIC TIMBER CORP"          64135 119
2001 1 0 0 "DELTIC TIMBER CORP"          64135 119
2002 1 0 0 "DELTIC TIMBER CORP"          64135 119
2003 1 0 0 "DELTIC TIMBER CORP"          64135 119
2004 1 0 0 "DELTIC TIMBER CORP"          64135 119
2005 1 0 0 "DELTIC TIMBER CORP"          64135 119
2006 0 0 1 "DELTIC TIMBER CORP"          64135 119
1992 0 1 0 "BLOCKBUSTER ENMNT CORP"       3491 137
1993 0 1 0 "BLOCKBUSTER ENMNT CORP"       3491 137
1995 1 0 0 "AUTONATION INC"               9063 137
1996 0 1 0 "AUTONATION INC"               9063 137
1997 0 1 0 "AUTONATION INC"               9063 137
1998 0 1 0 "AUTONATION INC"               9063 137
1999 0 1 0 "AUTONATION INC"               9063 137
2000 1 0 0 "AUTONATION INC"               9063 137
2001 1 0 0 "AUTONATION INC"               9063 137
1992 1 0 0 "KEYSPAN ENERGY CORP"          2424 152
1993 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
1994 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
1995 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
1996 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
end

[Clyde Schechter]

This data set is much more complicated than the one Rens Martens had. You have some executive who held down multiple jobs concurrently. You have some executives who have gaps in their years of employment for the same firm (is that one job or multiple jobs?) If the executive is in two different jobs in the same final year, which one is the "final" job? And you have some executives whose title changes within the course of one job. If this job happens to be the one for the "final" or "previous"year, which title is wanted?

Part of the difficulty is that the code you have used was not designed for data with these complications and, accordingly, it produces some incorrect results. For one thing, if an exec has two jobs concurrently, the observations for those jobs get scrambled among each other randomly when you calculate job_number, so that the code concludes there are a lot of different jobs instead of only 1, 2, or 3. That's fixable.

Another problem in your code is -by EXECID (fyear job_num), sort: egen previous_title = max(cond(job_num == last_job_num-1, title, .))-, which will always select a title from the penultimate job, which, for somebody with 3 jobs, is not what you say you want. But there is another subtler problem here: notice I say a title from the penultimate job. Because the exec may have several titles in that job, this code selects the one that is coded with the highest number according to your label definition. If that's what you want, that's fine--but I suspect it isn't really what you had in mind. But I also don't know what you do want here: perhaps you want the chronologically earliest, or the latest?

Then there is

sort EXECID
quietly by EXECID : gen dup2 = cond(_N==1,0,_n)
tabulate dup2
drop if dup2>1

This leaves any execid with a single observation alone, and for any exec with more than one observation it selects one at random to retain. Is that what you want? If not, which one do you want to keep? Or, perhaps you don't care which one is kept because if you are only interested in the previous and final titles, those should be the same for all observations of the executive.

Perhaps with these explanations of what is going wrong, you may be able to fix the code yourself. If not, write back clarifying the remaining ambiguities and I'll try to help.

[Esther Buttner]

Thank you so much for those explanations and the help! In the meanwhile, I tried to fix the code myself. Unfortunately with no luck. So, it would be wonderful, if you could give it a try.
Below, is the clarifications of the remaining ambiguities:

• You have some executives who have gaps in their years of employment for the same firm (is that one job or multiple jobs?) : This is seen as one job!
• If the executive is in two different jobs in the same final year, which one is the "final" job? : The job where he started the latest, should be the 'final' job. If it is not possible it can be random. And if he started at the same year as well than it can be random.
• And you have some executives whose title changes within the course of one job. If this job happens to be the one for the "final" or "previous"year, which title is wanted? : The first year he/she is active as an executive (CEO, CFO or OTHER) should be the previous title (so the minimum fyear per EXECID) and the last fyear for the EXECID should be the 'final' title. (so the maximum fyear per EXECID).
• Because the exec may have several titles in that job, this code selects the one that is coded with the highest number according to your label definition. If that's what you want, that's fine--but I suspect it isn't really what you had in mind. But I also don't know what you do want here: perhaps you want the chronologically earliest, or the latest? : Indeed, this is not what I had in mind, I want the title that the EXECID has in the earliest fyear and latest fyear in his/her active years.
• This leaves any execid with a single observation alone, and for any exec with more than one observation it selects one at random to retain: This is indeed what I want because as you suggested I don't care which one is kept because if I am only interested in the previous and final titles, those should be the same for all observations of the executive.

I hope I explained the ambiguities clear and thank you again!

[Clyde Schechter]

I believe the following does it:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double fyear float(others ceoann1 cfoann1) str28 C long gvkey1 int EXECID
1992 1 0 0 "3COM CORP"                   10553   2
1993 0 1 0 "3COM CORP"                   10553   2
1994 0 1 0 "3COM CORP"                   10553   2
1995 0 1 0 "3COM CORP"                   10553   2
1996 0 1 0 "3COM CORP"                   10553   2
1997 0 1 0 "3COM CORP"                   10553   2
1998 1 0 0 "PALM INC"                   132502   2
1998 0 1 0 "3COM CORP"                   10553   2
1999 0 1 0 "3COM CORP"                   10553   2
1999 0 1 0 "PALM INC"                   132502   2
2000 0 1 0 "3COM CORP"                   10553   2
2001 0 1 0 "PALM INC"                   132502   2
2002 0 1 0 "PALM INC"                   132502   2
2003 1 0 0 "PALM INC"                   132502   2
1992 0 1 0 "IKON OFFICE SOLUTIONS"        1246  19
1993 0 1 0 "IKON OFFICE SOLUTIONS"        1246  19
1994 1 0 0 "IKON OFFICE SOLUTIONS"        1246  19
1996 1 0 0 "UNISOURCE WORLDWIDE INC"     64117  19
1997 0 1 0 "UNISOURCE WORLDWIDE INC"     64117  19
1998 0 1 0 "UNISOURCE WORLDWIDE INC"     64117  19
1992 1 0 0 "ALLEGHENY LUDLUM CORP"       13708  23
1993 0 1 0 "ALLEGHENY LUDLUM CORP"       13708  23
1994 0 1 0 "ALLEGHENY LUDLUM CORP"       13708  23
2000 1 0 0 "ALLEGHENY TECHNOLOGIES INC"  10405  23
2001 1 0 0 "ALLEGHENY TECHNOLOGIES INC"  10405  23
1992 1 0 0 "SEARS ROEBUCK & CO"           9563  28
1992 1 0 0 "ALLSTATE CORP"               28349  28
1993 0 1 0 "ALLSTATE CORP"               28349  28
1993 1 0 0 "SEARS ROEBUCK & CO"           9563  28
1994 0 1 0 "ALLSTATE CORP"               28349  28
1994 1 0 0 "SEARS ROEBUCK & CO"           9563  28
1992 1 0 0 "APL LTD"                      1543  47
1993 0 1 0 "APL LTD"                      1543  47
1994 0 1 0 "APL LTD"                      1543  47
1995 0 1 0 "APL LTD"                      1543  47
2000 1 0 0 "GAP INC"                      4990  47
2001 1 0 0 "GAP INC"                      4990  47
2002 1 0 0 "GAP INC"                      4990  47
1992 1 0 0 "ARVIN INDUSTRIES INC"         1786  75
1993 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1994 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1995 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1996 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1997 0 1 0 "ARVIN INDUSTRIES INC"         1786  75
1998 1 0 0 "ARVIN INDUSTRIES INC"         1786  75
2001 0 1 0 "AMCAST INDUSTRIAL CORP"       1372  75
2002 0 1 0 "AMCAST INDUSTRIAL CORP"       1372  75
2003 0 1 0 "AMCAST INDUSTRIAL CORP"       1372  75
1992 0 1 0 "ATLANTIC RICHFIELD CO"        1848  83
1993 0 1 0 "ATLANTIC RICHFIELD CO"        1848  83
1994 0 1 0 "ATLANTIC RICHFIELD CO"        1848  83
1995 1 0 0 "ATLANTIC RICHFIELD CO"        1848  83
1998 1 0 0 "GLOBAL CROSSING LTD"        113491  83
1999 1 0 0 "GLOBAL CROSSING LTD"        113491  83
2000 1 0 0 "GLOBAL CROSSING LTD"        113491  83
1992 0 1 0 "BECTON DICKINSON & CO"        2111 116
1993 0 1 0 "BECTON DICKINSON & CO"        2111 116
1994 1 0 0 "MERCK & CO"                   7257 116
1994 0 1 0 "BECTON DICKINSON & CO"        2111 116
1995 0 1 0 "MERCK & CO"                   7257 116
1996 0 1 0 "MERCK & CO"                   7257 116
1997 0 1 0 "MERCK & CO"                   7257 116
1998 0 1 0 "MERCK & CO"                   7257 116
1999 0 1 0 "MERCK & CO"                   7257 116
2000 0 1 0 "MERCK & CO"                   7257 116
2001 0 1 0 "MERCK & CO"                   7257 116
2002 0 1 0 "MERCK & CO"                   7257 116
2003 0 1 0 "MERCK & CO"                   7257 116
2004 0 1 0 "MERCK & CO"                   7257 116
2005 1 0 0 "MERCK & CO"                   7257 116
1992 1 0 0 "MURPHY OIL CORP"              7620 119
1993 1 0 0 "MURPHY OIL CORP"              7620 119
1994 1 0 0 "MURPHY OIL CORP"              7620 119
1995 1 0 0 "MURPHY OIL CORP"              7620 119
1996 1 0 0 "MURPHY OIL CORP"              7620 119
1996 1 0 0 "DELTIC TIMBER CORP"          64135 119
1997 1 0 0 "DELTIC TIMBER CORP"          64135 119
1998 1 0 0 "DELTIC TIMBER CORP"          64135 119
1999 1 0 0 "DELTIC TIMBER CORP"          64135 119
2000 1 0 0 "DELTIC TIMBER CORP"          64135 119
2001 1 0 0 "DELTIC TIMBER CORP"          64135 119
2002 1 0 0 "DELTIC TIMBER CORP"          64135 119
2003 1 0 0 "DELTIC TIMBER CORP"          64135 119
2004 1 0 0 "DELTIC TIMBER CORP"          64135 119
2005 1 0 0 "DELTIC TIMBER CORP"          64135 119
2006 0 0 1 "DELTIC TIMBER CORP"          64135 119
1992 0 1 0 "BLOCKBUSTER ENMNT CORP"       3491 137
1993 0 1 0 "BLOCKBUSTER ENMNT CORP"       3491 137
1995 1 0 0 "AUTONATION INC"               9063 137
1996 0 1 0 "AUTONATION INC"               9063 137
1997 0 1 0 "AUTONATION INC"               9063 137
1998 0 1 0 "AUTONATION INC"               9063 137
1999 0 1 0 "AUTONATION INC"               9063 137
2000 1 0 0 "AUTONATION INC"               9063 137
2001 1 0 0 "AUTONATION INC"               9063 137
1992 1 0 0 "KEYSPAN ENERGY CORP"          2424 152
1993 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
1994 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
1995 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
1996 0 1 0 "KEYSPAN ENERGY CORP"          2424 152
end

*make byte van de others ceoann1 cfoann1*
recast byte others
recast byte ceoann1
recast byte cfoann1

*make from gvkey1 a int*
recast int gvkey1


label define title 1 "CEO" ///
2 "CFO" ///
3 "OTHER"
gen title:title = 1 if ceo
replace title = 2 if cfo
replace title = 3 if other

// IDENTIFY SPELLS OF EMPLOYMENT AT THE SAME FIRM
by EXECID (gvkey1 fyear), sort: gen spell = sum(gvkey1 != gvkey1[_n-1])
by EXECID spell (fyear), sort: gen start_year = fyear[1]
by EXECID start_year gvkey1 (fyear), sort: gen job_num = ///
    1 if _n == 1
by EXECID (start_year gvkey1 fyear): replace job_num = sum(job_num)
by EXECID (job_num fyear ), sort: gen last_job_num = job_num[_N]
drop spell
assert last_job_num <= 3

// NOW CALCULATE WANTED VARIABLES
by EXECID (job_num fyear), sort: gen previous_title ///
    = title[1]
by EXECID (job_num fyear): gen final_title ///
    = title[_N]
label values *_title title
sort EXECID fyear

by EXECID: keep if _n == 1
keep EXECID previous_title final_title

decode previous_title, gen (pt)
decode final_title, gen (ft)
tabulate pt ft

[Esther Buttner]

Thank you very much, it works! I really appreciate your help!

I do only have one further question if that is not a problem. I was going through the code and I do not really understand this line: assert last_job_num <= 3, because I do not understand why it is a problem if the last_job_num >=3. Also when I run the code this assertion is false. This is because some executives work in 5 different firms, in my dataset. So, is it possible to rewrite this line to assert last_job_num <= 5, or will this be incorrect or is it wiser to drop the observations were assert last_job_num <= 3 is false?

[Clyde Schechter]

In your original post, you said that people worked 1, 2 or 3 jobs. The original code found as many as 6 in the example data because it was not counting jobs properly. So I put that command in there as a check that the job counting was being done properly. It also mattered, because at one point when I was working on it, I wrote some commands that would only produce correct results for 3 or fewer jobs. But that changed later. And I'm now confident the job-counting code is working correctly. At present, the code will work correctly regardless of the maximum number of jobs a person can work. I forgot to remove the -assert-. So, if, in fact, having people work more than 3 jobs is possible and does not represent a data error, then you can just remove this -assert- command.