generating tenure variable

[Jozef Patrnciak]

Hi, I would like to create a variable that would track a tenure of executives (number of years with the company), the problem is that sometimes executives rejoin the company so it is not as easy as just calculating the difference between date joined and date left. The dates are recorded in 4 separate variables joined company (joined_co), rejoined company (rejoin), left company (leftco) and left company after rejoining (releft). Here is also example of the dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(leftco releft joined_co rejoin) double year str6 gvkey long execid
    .     .     .     . 2000 "015208"  9684
15341     .     .     . 2000 "006169" 24339
    .     . 12570     . 2000 "030059" 25599
17166     . 14092     . 2000 "010411" 19399
    .     . 13163     . 2000 "002938" 13437
    .     . 13939     . 2000 "005330" 19275
    .     . 14884     . 2000 "001998" 23010
    .     .     .     . 2000 "065351" 14666
16842     . 13149     . 2000 "027794" 18582
    .     .     .     . 2000 "013397"  6170
15430     . 14641     . 2000 "008745" 22396
    .     .     .     . 2000 "122077" 20312
16933     . 13970     . 2000 "014895" 25419
    .     . 13118     . 2000 "030059" 18060
15371     .  3439     . 2000 "007922" 12751
    .     .     .     . 2000 "013041" 23053
    .     .     .     . 2000 "062814" 21483
    .     .     .     . 2000 "009772" 24534
    .     .     .     . 2000 "030870" 22742
    .     .     .     . 2000 "025434" 22926
15218     .     .     . 2000 "006379" 15002
16892     . 14457     . 2000 "001111" 22982
    .     . 10166     . 2000 "030870" 17713
    .     . 14291     . 2000 "005301" 21791
    .     .     .     . 2000 "005961" 19446
15043     .     .     . 2000 "024315" 22252
15043     . 13939     . 2000 "028700" 21889
16405     . 14457     . 2000 "008446" 19233
    .     .     .     . 2000 "063621" 21806
    .     . 14823     . 2000 "028700" 24019
13230     .     . 14223 2000 "012631"  2372
    .     . 14884     . 2000 "144396" 25537
    .     . 14647     . 2000 "007401" 23620
    .     .     .     . 2000 "006116" 18211
17333     .     .     . 2000 "007692" 21373
    .     . 13911     . 2000 "008214" 18690
    .     .  9497     . 2000 "022612" 18020
15340     . 14854     . 2000 "029345" 23311
17166     .  3653     . 2000 "001920" 14572
    .     . 11323     . 2000 "122778" 22720
14867 16883 13975 15048 2000 "011669" 17924
    .     .     .     . 2000 "001837" 17966
    .     .     .     . 2000 "009155" 10314
    .     .     .     . 2000 "011300" 12659
15312     .     .     . 2000 "060797" 19713
15303     .     .     . 2000 "009217" 18882
    .     .     .     . 2000 "008007" 14368
    .     .     .     . 2000 "008402" 22473
    .     .     .     . 2000 "014820" 22768
    .     .     .     . 2000 "024531" 22139
16832     . 12600     . 2000 "011669" 15428
    .     .     .     . 2000 "005791" 18012
    .     .     .     . 2000 "007824" 19382
    .     . 13880     . 2000 "026021" 19795
    .     .     .     . 2000 "004737" 24432
16861     .     .     . 2000 "009299" 23251
    .     . 14488     . 2000 "001686" 21665
14844     .     .     . 2000 "024704" 21759
    .     .     .     . 2000 "112178" 18740
    .     .     .     . 2000 "063690" 13526
    .     .     .     . 2000 "024608" 20681
17044     .     .     . 2000 "003126" 17690
    .     . 11139     . 2000 "061153" 23655
15350     . 14619     . 2000 "024621" 19479
14641     .  6940     . 2000 "014412"  4510
    .     .     .     . 2000 "009563" 18672
    .     .     .     . 2000 "007409" 24506
15743     .     .     . 2000 "010420" 20896
17410     .     .     . 2000 "009466" 11878
    .     .  1096     . 2000 "005216"   481
    .     .     .     . 2000 "007536" 16410
    .     .     .     . 2000 "011313" 19528
    .     .     .     . 2000 "007180" 20861
    .     .     .     . 2000 "004990" 12730
    .     .     .     . 2000 "001773" 14957
    .     . 12904     . 2000 "014897" 11431
    .     .  7305     . 2000 "028758" 20161
    .     .     .     . 2000 "003373" 19078
    .     .     .     . 2000 "026012" 17800
    .     .     .     . 2000 "011925"  9002
    .     . 13605     . 2000 "028018" 25212
    .     .  9132     . 2000 "010121" 15659
    .     . 13779     . 2000 "010190" 10913
    .     .     .     . 2000 "006475" 10489
    .     .     .     . 2000 "011584" 25353
    .     .     .     . 2000 "013365" 15879
    .     .     .     . 2000 "119417" 21140
    .     .  8766     . 2000 "001678" 21162
    .     .     .     . 2000 "001243" 25370
16919     . 13737     . 2000 "006733" 15017
    .     .  7579     . 2000 "024291"  6883
    .     .     .     . 2000 "118121" 22201
    .     .     .     . 2000 "003221" 24737
    .     .     .     . 2000 "007127" 21451
    .     .     .     . 2000 "024856" 21349
    .     .     .     . 2000 "002518" 25023
    .     .     .     . 2000 "002002" 24475
    .     .     .     . 2000 "010507" 24708
    .     .     .     . 2000 "029392" 24571
    .     . 12297     . 2000 "065215" 20114
end
format %d leftco
format %d releft
format %d joined_co
format %d rejoin

Thanks in advance for any help.

[Clyde Schechter]

You need to clarify how you want to go about this. There are people in your data set how joined but never left. So, as of what date do you want to calculate their tenure? Today? End of last year? Some other reference date?

A similar question applies to those who rejoin but never releft.

Perhaps more difficult: you have people for whom there is a left date but no joined date. When do you want to assume they started? The analgous question applies to the instance where somebody releft, but there is no rejoin date.

Finally, the whole concept of tenure can be defined in different ways if a person leaves and returns. One possibility is to consider them to have lost tenure entirely once they leave and count only the time spent in their most recent engagement. The other possibility is to "restart the clock from where it left off" when they rejoin--which would correspond to adding up the time in both stints. Please clarify which you want (or, perhaps, something different still.)

[Jozef Patrnciak]

Hi, thanks for the response and sorry for not clarifying that before. I would like the old tenure to be just added to the old one if person rejoins. If people dont have join date I would like to drop them and same for the cases that re-left but dont have rejoin cases but if there are people who have rejoin and re left case I would like to leave them.

[Clyde Schechter]

Thanks. That still leave unanswered the question of people who have joined (or rejoined) but not left (resp. releft): as of what date do you want to reckon their tenure?

[Jozef Patrnciak]

Hi, oh sorry.I would like to keep those as well. I only want to drop people for whom there is no way of knowing when they joined or rejoined but if someone stays with the company and never leaves that is okay - their tenure should just cumulate till the end of dataset

[Clyde Schechter]

This should do it:

//    VERIFY DATES ARE IN PROPER ORDER
assert joined_co < leftco if !missing(joined_co)
assert leftco < rejoin if !missing(leftco)
assert rejoin < releft if !missing(rejoin)

//    FIND LAST DEPARTURE DATE IN THE DATA SET
egen later = rowmax(leftco releft joined_co rejoin)
summ later, meanonly
local last_date = r(max)

//    DROP OBSERVATIONS WHERE START DATE OF AN EPISODE IS INDETERMINATE
drop if missing(joined_co)
drop if missing(rejoin) & !missing(releft)

//    WHERE END DATE OF AN EPISODE IS INDETERMINATE
//    ASSIGN THE LAST DATE IN THE DATA SET
gen end1 = cond(missing(leftco), `last_date', leftco)
gen end2 = cond(missing(releft), `last_date', releft)

//    CALCULATE TENURE OF EACH EPISODE OF AFFILIATION SEPARATELY
gen tenure1 = end1 - joined_co
gen tenure2 = end2 - rejoin

//    AND ADD THEM UP
egen tenure_in_days = rowtotal(tenure1 tenure2)
gen tenure_in_years = tenure_in_days/365.25

[Jozef Patrnciak]

Thanks worked like charm

[Sara Ahmed]

Thank you so much Schechter.

That is really great.

I have one question, what about if we do not have the dates of left, re-left, join, and region, but instead, we have the current year and the ID of the executive. So, when that ID changed, then it means that the executive has left the company?

Thanks a lot again,

Sara

[Clyde Schechter]

Well, this is a different, and fairly unsatisfactory situation. So let's say that ID1 is present in 2000, 2001, and 2002, but a new ID is found in 2003. The problem is that you don't really know when the changeover occurred. ID1 might have left in the very beginning of 2002, or at the very end. And we don't know if the new ID began at some point in 2002, or maybe there was a gap with an unfilled position extending some part of the way (perhaps a long way) into 2003. The data is just too coarse grained. In more technical terms, you have data that is both left and right censored. That's very difficult to work with in a satisfactory way.

[Sara Ahmed]

Thank you for your kind reply.

What about if I would like to compute the auditor tenure, where the auditor is assigned once every year. Therefore, we should not worry about the time that they join the company as when we are dealing with the executives?

Thank you very much again,

[Clyde Schechter]

Yes, that is doable. Please provide example data, using the -dataex- command.
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.



When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Sara Ahmed]

Thank you very much in advance.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double fyear str8 au
"001000" 1974 "1"
"001000" 1975 "1"
"001000" 1976 "1"
"001000" 1977 "6"
"001001" 1983 "6"
"001001" 1984 "6"
"001001" 1985 "6"
"001003" 1982 "8"
"001003" 1983 "8"
"001003" 1984 "8"
"001003" 1985 "8"
"001003" 1986 "8"
"001003" 1987 "8"
"001003" 1988 "5"
"001003" 1989 "5"
"001004" 1974 "1"
"001004" 1975 "1"
"001004" 1976 "1"
"001004" 1977 "1"
"001004" 1978 "1"
"001004" 1979 "1"
"001004" 1980 "1"
"001004" 1981 "1"
"001004" 1982 "1"
"001004" 1983 "1"
"001004" 1984 "6"
"001004" 1985 "6"
"001004" 1986 "6"
"001004" 1987 "6"
"001004" 1988 "6"
"001004" 1989 "6"
"001004" 1990 "6"
"001004" 1991 "6"
"001004" 1992 "6"
"001004" 1993 "6"
"001004" 1994 "6"
"001004" 1995 "6"
"001004" 1996 "6"
"001004" 1997 "6"
"001004" 1998 "6"
"001004" 1999 "6"
"001004" 2000 "6"
"001004" 2001 "6"
"001004" 2002 "6"
"001004" 2003 "6"
"001004" 2004 "6"
"001004" 2005 "6"
"001004" 2006 "6"
"001004" 2007 "6"
"001004" 2008 "6"
"001004" 2009 "6"
"001004" 2010 "6"
"001004" 2011 "6"
"001004" 2012 "6"
"001004" 2013 "6"
"001004" 2014 "6"
"001004" 2015 "6"
"001004" 2016 "6"
"001004" 2017 "6"
"001005" 1974 "9"
"001005" 1975 "9"
"001005" 1976 "9"
"001005" 1977 "9"
"001005" 1978 "9"
"001005" 1979 "9"
"001005" 1980 "9"
"001005" 1981 "9"
"001006" 1974 "9"
"001006" 1975 "9"
"001006" 1976 "9"
"001006" 1977 "8"
"001006" 1978 "8"
"001006" 1979 "8"
"001006" 1980 "8"
"001006" 1981 "8"
"001006" 1982 "8"
"001007" 1974 "1"
"001007" 1975 "1"
"001007" 1976 "1"
"001007" 1977 "1"
"001007" 1978 "1"
"001007" 1979 "1"
"001007" 1980 "1"
"001007" 1981 "1"
"001007" 1982 "1"
"001007" 1983 "1"
"001007" 1984 "1"
"001008" 1983 "5"
"001008" 1984 "5"
"001008" 1985 "5"
"001009" 1981 "4"
"001009" 1982 "4"
"001009" 1983 "4"
"001009" 1984 "4"
"001009" 1985 "4"
"001009" 1986 "4"
"001009" 1987 "4"
"001009" 1988 "4"
"001009" 1989 "4"
"001009" 1990 "4"
end

[Clyde Schechter]

I assume that au is a variable that identifies the auditor.

//  VERIFY THAT YEARS IN DATA SET FOR A GIVEN FIRM ARE CONSECUTIVE
by gvkey (fyear), sort: assert fyear == fyear[_n-1] + 1 if _n > 1

//  CALCULATE SPELLS OF TENURE FOR AUDITOR
by gvkey (fyear), sort: gen spell_num = sum(au != au[_n-1])
by gvkey spell_num, sort: gen tenure = _N

In your data, there is no instance of an auditor workin for a firm, being replaced, and subsequently returning. This code, should it encounter such an instance, will separately calculate the tenure in each spell of work for the firm. If you want the tenure to "pick up where it left off" after a gap, the code would be different. Post back if that's the case.

[Sara Ahmed]

Greats, thank you so much.

That what I want, when the auditor return to the company, then the auditor's tenure starts from the zero again.

[Sara Ahmed]

Kindly, I have another question, how can we calculate the standard deviation for the return on assets over the past three years?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double fyear float ROA
"001000" 1974   .07142529
"001000" 1976   .14365195
"001000" 1977   .04996631
"001000" 1975    .0890865
"001001" 1984   .08082386
"001001" 1983           .
"001001" 1985    .1583574
"001003" 1982           .
"001003" 1986   .05668335
"001003" 1984    .0453746
"001003" 1988  -.48859245
"001003" 1987 -.035993416
"001003" 1985    .0286373
"001003" 1983   .18643466
"001003" 1989   -.1568796
"001004" 1980   .04888961
"001004" 1976   .04136521
"001004" 2010   .04872549
"001004" 2003  .005103252
"001004" 1990   .03809575
"001004" 2015  .026732674
"001004" 1974   .05908055
"001004" 2016  .034810346
"001004" 1988   .08708256
"001004" 2012  .025049495
"001004" 1983   .04031881
"001004" 2002 -.017473977
"001004" 2017    .0489994
"001004" 1977    .0442371
"001004" 2007   .07094666
"001004" 1998   .06214367
"001004" 2006    .0607334
"001004" 2005    .0480218
"001004" 2014  -.02477836
"001004" 2004  .026183857
"001004" 1994  .025053516
"001004" 1997   .06733021
"001004" 1995   .03760327
"001004" 1991  .026371336
"001004" 1999   .04839189
"001004" 1981  .014745712
"001004" 1987   .09031822
"001004" 2009  .032397565
"001004" 2013   .03411484
"001004" 2000  .025008164
"001004" 1986   .07746851
"001004" 1992 .0007158196
"001004" 1993  .025972817
"001004" 1985    .0740388
"001004" 2011   .03974991
"001004" 2008   .05917725
"001004" 1975   .04290361
"001004" 1978   .05318396
"001004" 1984   .06610167
"001004" 1989   .07198554
"001004" 1979   .05796492
"001004" 1982    .0245924
"001004" 2001  -.08397616
"001004" 1996   .05258698
"001005" 1974           .
"001005" 1977   .06142402
"001005" 1978   .04109024
"001005" 1979  .063093245
"001005" 1976   .09095172
"001005" 1981   .09642673
"001005" 1980   .10009728
"001005" 1975   .08962985
"001006" 1977   .12686816
"001006" 1978  .030352304
"001006" 1980   -.2410489
"001006" 1979  -.11266832
"001006" 1976   .11314879
"001006" 1974           .
"001006" 1975  .001901864
"001006" 1981   .04044944
"001006" 1982 .0032928064
"001007" 1982   .02333523
"001007" 1980  .002647604
"001007" 1979   -.0110595
"001007" 1976  -.05278198
"001007" 1983    -.213861
"001007" 1975  .002716256
"001007" 1977  -.04134367
"001007" 1978 .0036381676
"001007" 1981  -.14349215
"001007" 1974           .
"001007" 1984    -.156769
"001008" 1983           .
"001008" 1984   -.3525974
"001008" 1985   -.8160329
"001009" 1989   .08121108
"001009" 1982  -.20010307
"001009" 1987   .05988701
"001009" 1994   .07067519
"001009" 1981           .
"001009" 1990    .0548507
"001009" 1985   .19551244
"001009" 1993   .08009338
"001009" 1992  .067155994
"001009" 1991   .02650379
end
format %ty fyear