Individual name retention

[Kwadwo Boateng]

Hi , I have a data of CEO's (eg A, B, C, D etc) who have worked at different firms. I am looking to keep only the CEO's who have worked for at least 2 firms for a minimum of three years each. Note that once a CEO qualifies by this criteria, all other firms this CEO has worked before whether for a year or two must be maintained in my data set. Any help please?

[William Lisowski]

Your question leaves it to the reader to invent and explain variable names and sample data before being able to describe and test a solution. That's asking a lot of someone whose advice you are seeking, when you could provide sample data.

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post, looking especially at sections 9-12 on how to best pose your question. In particular, please read FAQ #12 and help those whose help you seek by posting example data using the dataex command. If you are running Stata 15.1 or later, it is already installed. For earlier versions of Stata, install dataex by typing ssc install dataex. Type help dataex to read the simple instructions for using it. Using dataex will enable those who want to help you to quickly and easily create a 100% faithful replica of your situation to test their ideas and code on.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

[Phil Bromiley]

In addition to William's insightful recommendations, you can calculate durations at firms by bysort firm CEO: egen yrsatfirm=count(year)

To work across firms, you may find ti helpful to collapse the data so you have firm-CEO variables [This would also do the count noted above.]. Then you can again use egen to count number of firms, etc. things and generate to create the final indicators. If you need the original data also, you can then merge the collapsed data back with the original data.

[Kwadwo Boateng]

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str16 ceo str27 firm
2013 "Anderson, Craig"  "3 Holdings"                 
2014 "Bussey, William"  "4com PLC"                   
2015 "Bussey, William"  "4com PLC"                   
2016 "Bussey, William"  "4com PLC"                   
2015 "Caiaphas Leo"     "PP PLC"                     
2016 "Caiaphas Leo"     "PP PLC"                     
2017 "Caiaphas Leo"     "PP PLC"                     
2013 "Caiaphas Leo"     "RT Oil PLC"                 
2014 "Caiaphas Leo"     "RT Oil PLC"                 
2015 "Caiaphas Leo"     "RT Oil PLC"                 
2016 "Caiaphas Leo"     "RT Oil PLC"                 
2009 "Caiaphas Leo"     "1spatial PLC"               
2010 "Doddy, Jonathan"  "Post GPS PLC"               
2011 "Doddy, Jonathan"  "Post GPS PLC"               
2012 "Doddy, Jonathan"  "Post GPS PLC"               
2009 "Fenner Anna"      "Post GPS PLC"               
2016 "Froom, Joe"       "Post GPS PLC"               
2009 "Froom, Joe"       "3 Holdings"                 
2010 "Froom, Joe"       "3 Holdings"                 
2011 "Froom, Joe"       "3 Holdings"                 
2012 "Froom, Joe"       "3 Holdings"                 
2009 "Gledhill, Jeremy" "Royal Bank"                 
2010 "Kyiu David"       "Royal Bank"                 
2011 "Kyiu David"       "Royal Bank"                 
2012 "Kyiu David"       "Royal Bank"                 
2015 "Kyiu David"       "Coca Cola"                  
2016 "Kyiu David"       "Coca Cola"                  
2017 "Kyiu David"       "Coca Cola"                  
2014 "Kyiu David"       "MTN "                       
2009 "Pong, Graham"     "Coca Cola"                  
2009 "Lenton, Kevin"    "PP PLC"                     
2010 "Lenton, Kevin"    "PP PLC"                     
2011 "Lenton, Kevin"    "PP PLC"                     
2017 "Lenton, Kevin"    "RT Oil PLC"                 
2010 "Lenton, Kevin"    "Baco Capital PLC"           
2014 "Pong, Graham"     "4D Pharma PLC"              
2015 "Pong, Graham"     "4D Pharma PLC"              
2016 "Pong, Graham"     "4D Pharma PLC"              
2009 "Pong, Graham"     "Genesis Group PLC"          
2010 "Pong, Graham"     "Genesis Group PLC"          
2011 "Pong, Graham"     "Genesis Group PLC"          
2016 "Pong, Graham"     "Royal Bank"                 
2012 "Senior, James"    "PP PLC"                     
2013 "Senior, James"    "PP PLC"                     
2014 "Senior, James"    "PP PLC"                     
2009 "Sun Paul"         "MTN "                       
2010 "Sun Paul"         "MTN "                       
2013 "Sun Paul"         "MTN "                       
2013 "Sun Paul"         "Post GPS PLC"               
2014 "Sun Paul"         "Post GPS PLC"               
2015 "Sun Paul"         "Post GPS PLC"               
2014 "Sun Paul"         "Baco Capital PLC"           
2015 "Sun Paul"         "Baco Capital PLC"           
2016 "Sun Paul"         "Baco Capital PLC"           
2010 "Sunders, David"   "1spatial PLC"               
2011 "Sunders, David"   "1spatial PLC"               
2012 "Sunders, David"   "1spatial PLC"               
2013 "Sunders, David"   "Barclays PLC"               
2014 "Sunders, David"   "Barclays PLC"               
2015 "Sunders, David"   "Barclays PLC"               
2016 "Sunders, David"   "1spatial PLC"               
2009 "Sunders, David"   "21st Century Technology PLC"
2010 "Sunders, David"   "Vodafone PLC"               
2012 "Sunders, David"   "Genesis Group PLC"          
2016 "Sunders, David"   "Genesis Group PLC"          
2014 "Tax Alex"         "3 Holdings"                 
2015 "Tax Alex"         "3 Holdings"                 
2016 "Tax Alex"         "3 Holdings"                 
2011 "Tax Alex"         "Baco Capital PLC"           
2012 "Tax Alex"         "Baco Capital PLC"           
2013 "Tax Alex"         "Baco Capital PLC"           
2012 "Taylor Scott"     "Coca Cola"                  
2013 "Taylor Scott"     "Coca Cola"                  
2014 "Taylor Scott"     "Coca Cola"                  
2011 "Taylor, Ian"      "Vodafone PLC"               
2012 "Taylor, Ian"      "Vodafone PLC"               
2013 "Taylor, Ian"      "4com PLC"                   
2013 "Tomsun David"     "Royal Bank"                 
2014 "Tomsun David"     "Royal Bank"                 
2015 "Tomsun David"     "Royal Bank"                 
2013 "Twum Nicholas"    "Genesis Group PLC"          
2014 "Twum Nicholas"    "Genesis Group PLC"          
2015 "Twum Nicholas"    "Genesis Group PLC"          
2010 "Waseja James"     "Coca Cola"                  
2011 "Waseja James"     "Coca Cola"                  
   . ""                 ""                           
   . ""                 ""                           
   . ""                 ""                           
   . ""                 ""                           
   . ""                 ""                           
   . ""                 ""                           
end

Hi William, my apologies, i have posted an example of my data as requested. I am looking to keep only the CEO's who have worked for at least 2 firms, each for a minimum of three years . Note that once a CEO qualifies by this criteria, all other firms this CEO has worked before whether for a year or two must be maintained in my data set.

Take David Kyiu in my data for example, he has worked at Royal bank and Coca Cola each for three years, therefore he meets the criteria. He has also worked for MTN for a year. Now due to the fact that he meets the criteria, the data on MTN which he has worked for a year must also be retained. I hope I am clear enough.

[Andrew Musau]

First determine tenure by CEO and firm and then count firms satisfying the condition by CEO

egen ceofirm= group(ceo firm), label
bys ceofirm (year): gen tenure= _N
bys ceofirm (year): gen tag= tenure>2 &_n==1 &!missing(firm)
bys ceo(year): replace tag= sum(tag)
bys ceo(year): egen tokeep= max(tag)
keep if tokeep>1
drop ceofirm tenure tag tokeep

[Romalpa Akzo]

The same logic with Andrew, but jumping right into the final target.

bys ceo (firm): egen tag=total(firm!=firm[_n-1] & firm==firm[_n+2])
keep if tag>1

Notice that this logic goes along with the assumption that “coming back” cases are also counted. For example, if a CEO works for company A for 2 years, then moves to other(s) for some years, then comes back to A for another 2 years, he is still considered as having worked totally 4 years for A. If such assumption is not what you want, the solution would be different.

[Kwadwo Boateng]

Hello Andrew and Romalpa, incredible coding. It worked guys. Thanks a million.

following up using the same data, I would like to generate tenure for ceos such that year 1 for company is 1, year 2 for company 2 is 2 and so on instead of having the total number of years for each year ie that if CEO Leo worked three years for company between 2015 to 2017, tenure for each year is 3 which is not correct. thus 2015 should be 1, 2016 = 2, 2017 = 3.

Cheers.

[Andrew Musau]

It takes some time to get the hang of _n and _N, see

help system variables

Anyway, the fix is simple

bys ceofirm (year): gen tenurevar= _n

[Kwadwo Boateng]

Thank you Andrew.

[Kwadwo Boateng]

Hi Andrew and Romalpa, there's a little twist to the code you suggested earlier for me on CEO retention. After much consultation, I actually need CEO's who have worked for at least 3 years for a minimum of two clients, however contrary to my earlier assertion, any retention of this CEO in my data should be for clients in which they have worked for a minimum of three years.

In a nutshell, I require(want to keep) each CEO to work for at least two clients and have an average work experience at each client of at least three years. If there's a third client thereafter, only keep if CEO has worked there for at least three years.

Many thanks and sorry for the misexplanation.

[Romalpa Akzo]

* Total Number of Firms each CEO working for
bys ceo (firm): egen tag1=total(firm!=firm[_n-1])

* Total Number of Firms each CEO working for at least 3 year
bys ceo (firm): egen tag2=total(firm!=firm[_n-1] & firm==firm[_n+2])

keep if tag1==tag2 & tag1 >1

[Kwadwo Boateng]

Hi Romalpa, I tried the code but seems not to give the results I wanted. I cross checked your code by first running the earlier one you provided

which leaves me with CEO who have worked for at least two clients for a minimum of 3 years each.

bys ceo (firm): egen tag=total(firm!=firm[_n-1] & firm==firm[_n+2])
keep if tag>1

Following on I run the below code to ensure that these CEO's have worked for a minimum of three years in each of the firms which satisfies the second criteria.

egen ceofirm= group(ceo firm), label
bys ceofirm (year): gen tenure= _N

keep if tenure>2

This gives different results to what you gave.

[Romalpa Akzo]

My code in #11 keep CEOs, who have worked for at least 2 firms, and at any firm, they have worked there for at least 3 years. It is what I understood as you wanted in #10.

Meanwhile, your code in #12 keep CEOs, who have worked for at least 3 years for at least 2 firms (and might also work for other firm(s) for less than 3 years). And then for each these CEOs, you only keep the firms with at least 3 years of working.

So, what is the output that you wanted?

[Kwadwo Boateng]

Ok let me put it this way cos both explanations are the same.

First criteria is too keep CEO's who have worked for at least 3 years in a firm. After, keep only CEO's who have worked for at least two firms.

I think this should be easier.

[Romalpa Akzo]

Well, for my understanding, your description in #14 is still not clearer, given the fact that it seems a bit different with your previous description in # 10 and/or your code explanation in #12. Then, a better clarification is still needed.

In #14, you said

First criteria is too keep CEO's who have worked for at least 3 years in a firm. After, keep only CEO's who have worked for at least two firms.

What do you mean by "a firm", whether it means "every firm" or "at least one firm"? The phrase "keep only CEO" should also be rechecked since it is contrast with your code in #12, which tends to drop unsatisfied firms rather than unwanted CEO.

It would be more effective if you could relook at your example and give out here a good illustration of the info that you need... Or, maybe, someone could give you the better advise.