Count change in variable in panel data set

[Holger Reimsbach]

Hello statalist users,

please find below my dataset. It list the top 4 executives per company per year. I would like to create a variable for change in those executives that counts how many new executives exist per year that did not show up in the previous year.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 GVKEY double year str50 EXEC_FULLNAME double CO_PER_ROL str50 CONAME
"001045" 2009 "Thomas W. Horton"        33126 "AMERICAN AIRLINES GROUP INC"
"001045" 2009 "Daniel P. Garton"        16477 "AMERICAN AIRLINES GROUP INC"
"001045" 2009 "Robert William Reding"   29349 "AMERICAN AIRLINES GROUP INC"
"001045" 2009 "Gary F. Kennedy"         29348 "AMERICAN AIRLINES GROUP INC"
"001045" 2010 "Thomas W. Horton"        33126 "AMERICAN AIRLINES GROUP INC"
"001045" 2010 "Daniel P. Garton"        16477 "AMERICAN AIRLINES GROUP INC"
"001045" 2010 "Robert William Reding"   29349 "AMERICAN AIRLINES GROUP INC"
"001045" 2010 "Gary F. Kennedy"         29348 "AMERICAN AIRLINES GROUP INC"
"001045" 2011 "Thomas W. Horton"        33126 "AMERICAN AIRLINES GROUP INC"
"001045" 2011 "Robert William Reding"   29349 "AMERICAN AIRLINES GROUP INC"
"001045" 2011 "Daniel P. Garton"        16477 "AMERICAN AIRLINES GROUP INC"
"001045" 2011 "Gary F. Kennedy"         29348 "AMERICAN AIRLINES GROUP INC"
"001045" 2012 "Daniel P. Garton"        16477 "AMERICAN AIRLINES GROUP INC"
"001045" 2012 "Gary F. Kennedy"         29348 "AMERICAN AIRLINES GROUP INC"
"001045" 2012 "Isabella Davidov Goren"  43629 "AMERICAN AIRLINES GROUP INC"
"001045" 2012 "James B. Ream"           46951 "AMERICAN AIRLINES GROUP INC"
"001045" 2013 "William Douglas Parker"  49256 "AMERICAN AIRLINES GROUP INC"
"001045" 2013 "J. Scott Kirby"          49257 "AMERICAN AIRLINES GROUP INC"
"001045" 2013 "James B. Ream"           46951 "AMERICAN AIRLINES GROUP INC"
"001045" 2013 "Daniel P. Garton"        16477 "AMERICAN AIRLINES GROUP INC"
"001045" 2014 "J. Scott Kirby"          49257 "AMERICAN AIRLINES GROUP INC"
"001045" 2014 "Robert D. Isom, Jr."     49258 "AMERICAN AIRLINES GROUP INC"
"001045" 2014 "Stephen L. Johnson"      49255 "AMERICAN AIRLINES GROUP INC"
"001045" 2014 "Derek J. Kerr"           49254 "AMERICAN AIRLINES GROUP INC"
"001045" 2015 "J. Scott Kirby"          49257 "AMERICAN AIRLINES GROUP INC"
"001045" 2015 "Robert D. Isom, Jr."     49258 "AMERICAN AIRLINES GROUP INC"
"001045" 2015 "Derek J. Kerr"           49254 "AMERICAN AIRLINES GROUP INC"
"001045" 2015 "Stephen L. Johnson"      49255 "AMERICAN AIRLINES GROUP INC"
"001075" 2009 "William Joseph Post"      9411 "PINNACLE WEST CAPITAL CORP" 
"001075" 2009 "Donald G. Robinson"      42212 "PINNACLE WEST CAPITAL CORP" 
"001075" 2009 "Randall K. Edington"     34831 "PINNACLE WEST CAPITAL CORP" 
"001075" 2009 "David P. Falck"          42478 "PINNACLE WEST CAPITAL CORP" 
"001075" 2010 "Randall K. Edington"     34831 "PINNACLE WEST CAPITAL CORP" 
"001075" 2010 "Donald G. Robinson"      42212 "PINNACLE WEST CAPITAL CORP" 
"001075" 2010 "Steven M. Wheeler"       25367 "PINNACLE WEST CAPITAL CORP" 
"001075" 2010 "David P. Falck"          42478 "PINNACLE WEST CAPITAL CORP" 
"001075" 2011 "Donald G. Robinson"      42212 "PINNACLE WEST CAPITAL CORP" 
"001075" 2011 "Randall K. Edington"     34831 "PINNACLE WEST CAPITAL CORP" 
"001075" 2011 "David P. Falck"          42478 "PINNACLE WEST CAPITAL CORP" 
"001075" 2011 "James R. Hatfield"       37250 "PINNACLE WEST CAPITAL CORP" 
"001075" 2012 "Randall K. Edington"     34831 "PINNACLE WEST CAPITAL CORP" 
"001075" 2012 "Donald G. Robinson"      42212 "PINNACLE WEST CAPITAL CORP" 
"001075" 2012 "David P. Falck"          42478 "PINNACLE WEST CAPITAL CORP" 
"001075" 2012 "James R. Hatfield"       37250 "PINNACLE WEST CAPITAL CORP" 
"001075" 2013 "Randall K. Edington"     34831 "PINNACLE WEST CAPITAL CORP" 
"001075" 2013 "James R. Hatfield"       37250 "PINNACLE WEST CAPITAL CORP" 
"001075" 2013 "David P. Falck"          42478 "PINNACLE WEST CAPITAL CORP" 
"001075" 2013 "Mark A. Schiavoni"       48682 "PINNACLE WEST CAPITAL CORP" 
"001075" 2014 "Randall K. Edington"     34831 "PINNACLE WEST CAPITAL CORP" 
"001075" 2014 "David P. Falck"          42478 "PINNACLE WEST CAPITAL CORP" 
"001075" 2014 "Mark A. Schiavoni"       48682 "PINNACLE WEST CAPITAL CORP" 
"001075" 2014 "James R. Hatfield"       37250 "PINNACLE WEST CAPITAL CORP" 
"001075" 2015 "Randall K. Edington"     34831 "PINNACLE WEST CAPITAL CORP" 
"001075" 2015 "Mark A. Schiavoni"       48682 "PINNACLE WEST CAPITAL CORP" 
"001075" 2015 "James R. Hatfield"       37250 "PINNACLE WEST CAPITAL CORP" 
"001075" 2015 "David P. Falck"          42478 "PINNACLE WEST CAPITAL CORP" 
"001078" 2009 "James V. Mazzo"          40086 "ABBOTT LABORATORIES"        
"001078" 2009 "Thomas Craig Freyman"    24994 "ABBOTT LABORATORIES"        
"001078" 2009 "Laura J. Schumacher"     37037 "ABBOTT LABORATORIES"        
"001078" 2009 "Olivier Bohuon"          40087 "ABBOTT LABORATORIES"        
"001078" 2010 "James V. Mazzo"          40086 "ABBOTT LABORATORIES"        
"001078" 2010 "Thomas Craig Freyman"    24994 "ABBOTT LABORATORIES"        
"001078" 2010 "Richard A. Gonzalez"     18545 "ABBOTT LABORATORIES"        
"001078" 2010 "Laura J. Schumacher"     37037 "ABBOTT LABORATORIES"        
"001078" 2011 "Thomas Craig Freyman"    24994 "ABBOTT LABORATORIES"        
"001078" 2011 "Laura J. Schumacher"     37037 "ABBOTT LABORATORIES"        
"001078" 2011 "Richard A. Gonzalez"     18545 "ABBOTT LABORATORIES"        
"001078" 2011 "Edward L. Michael, J.D." 45025 "ABBOTT LABORATORIES"        
"001078" 2012 "Laura J. Schumacher"     37037 "ABBOTT LABORATORIES"        
"001078" 2012 "Thomas Craig Freyman"    24994 "ABBOTT LABORATORIES"        
"001078" 2012 "Richard A. Gonzalez"     18545 "ABBOTT LABORATORIES"        
"001078" 2012 "Carlos Alban"            46615 "ABBOTT LABORATORIES"        
"001078" 2013 "Thomas Craig Freyman"    24994 "ABBOTT LABORATORIES"        
"001078" 2013 "Hubert L. Allen"         48320 "ABBOTT LABORATORIES"        
"001078" 2013 "Richard W. Ashley"       48319 "ABBOTT LABORATORIES"        
"001078" 2013 "Brian J. Blaser"         48321 "ABBOTT LABORATORIES"        
"001078" 2014 "Thomas Craig Freyman"    24994 "ABBOTT LABORATORIES"        
"001078" 2014 "John C. Landgraf"        49996 "ABBOTT LABORATORIES"        
"001078" 2014 "Brian J. Blaser"         48321 "ABBOTT LABORATORIES"        
"001078" 2014 "John M. Capek"           37036 "ABBOTT LABORATORIES"        
"001078" 2015 "Thomas Craig Freyman"    24994 "ABBOTT LABORATORIES"        
"001078" 2015 "Michael J. Warmuth"      51944 "ABBOTT LABORATORIES"        
"001078" 2015 "Brian J. Blaser"         48321 "ABBOTT LABORATORIES"        
"001078" 2015 "John M. Capek"           37036 "ABBOTT LABORATORIES"        
"001161" 2009 "Robert J. Rivet"         25084 "ADVANCED MICRO DEVICES"     
"001161" 2009 "Hector de Jesus Ruiz"    21115 "ADVANCED MICRO DEVICES"     
"001161" 2009 "Emilio Ghilardi"         40088 "ADVANCED MICRO DEVICES"     
"001161" 2009 "Thomas Josef Seifert"    40089 "ADVANCED MICRO DEVICES"     
"001161" 2010 "Thomas M. McCoy"         18718 "ADVANCED MICRO DEVICES"     
"001161" 2010 "Richard A. Bergman"      43251 "ADVANCED MICRO DEVICES"     
"001161" 2010 "Emilio Ghilardi"         40088 "ADVANCED MICRO DEVICES"     
"001161" 2010 "Thomas Josef Seifert"    40089 "ADVANCED MICRO DEVICES"     
"001161" 2011 "Rory P. Read"            45026 "ADVANCED MICRO DEVICES"     
"001161" 2011 "Derrick R. Meyer"        26736 "ADVANCED MICRO DEVICES"     
"001161" 2011 "Mark D. Papermaster"     45027 "ADVANCED MICRO DEVICES"     
"001161" 2011 "Richard A. Bergman"      43251 "ADVANCED MICRO DEVICES"     
"001161" 2012 "Lisa T. Su, Ph.D."       46953 "ADVANCED MICRO DEVICES"     
"001161" 2012 "Thomas Josef Seifert"    40089 "ADVANCED MICRO DEVICES"     
"001161" 2012 "John Byrne"              46955 "ADVANCED MICRO DEVICES"     
"001161" 2012 "Mark D. Papermaster"     45027 "ADVANCED MICRO DEVICES"     
end

I helped myself with the following code:

by GVKEY, sort: gen exec_change = 0 if CO_PER_ROL == CO_PER_ROL[_n-1] | CO_PER_ROL == CO_PER_ROL[_n-2] | CO_PER_ROL == CO_PER_ROL[_n-3] | CO_PER_ROL == CO_PER_ROL[_n-4] | CO_PER_ROL == CO_PER_ROL[_n-5] | CO_PER_ROL == CO_PER_ROL[_n-6] | CO_PER_ROL == CO_PER_ROL[_n-7]
replace exec_change = 1 if exec_change == .

egen TMTChange = sum(exec_change), by (GVKEY year)

I don't feel that this is a very smart way to achieve my goal and it might cause mistakes as well.

How can this be done better?

Thanks and kind regards
Holger

[Clyde Schechter]

I think you want:

by EXEC_FULLNAME GVKEY (year), sort: gen byte is_new = (year != year[_n-1]+1)
by GVKEY (year), sort: replace is_new = . if year == year[1]

Notes: In any company's first year appearing in the data set, we don't know if the executives are new or not. So the variable is_new is set to missing for those observations. In all other years it will be either 0 if the officer was there the preceding year, or 1 if he/she was not. Also, if an executive is present in one year, not in the next, and then re-appears subsequently, the re-appearance is marked as new. Not sure if either of these things is what you want.

[Holger Reimsbach]

Hello Clyde,

thanks a lot. That is exactly what I was looking for.

Thanks for your additional notes as well. These are things I am aware of.