Tag last and first two observations by group

[Marc Pelow]

Hi,

please consider the following data example:

* Example generated by -dataex-. For more info, type help dataex
clear
input double Firm_ID int FY long Manag_ID float tag
4295899290 2010 18101 1
4295899290 2010 18101 1
4295899290 2010 18101 1
4295899290 2012 35166 1
4295899290 2012 35166 1
4295899290 2014 35166 1
4295899290 2014 35166 1
4295899290 2015 35166 .
4295899290 2015 35166 .
4295899290 2016 35166 .
4295899290 2016 35166 .
4295899290 2016 35166 .
4295899290 2016 35166 .
4295899290 2017 35166 .
4295899290 2017 35166 .
4295899323 2012 12345 1
4295899323 2012 12345 1
4295899323 2012 12345 1
4295899323 2014 88888 1
4295899323 2014 88888 1
4295899323 2015 88888 1
4295899323 2016     . .
end

For each firm (Firm_ID), I want to tag the last two years (FY) of a manager's tenure (Manag_ID), as well as the first two years (FY) of the new manager's tenure, with a value of 1. Note that there might be gaps in the FY variable, meaning that for a specific Firm_ID-Manag_ID combination, there could be missing FYs, as seen in observations 5 (FY = 2012) and 6 (FY=2014) of the sample data.

Any help is much appreciated.

[George Ford]

bys Firm_ID (FY): g first = FY==FY[1]
bys Firm_ID (FY): g last = FY==FY[_N]
bys Firm_ID (FY): g firstlast = inlist(FY,FY[1],FY[_N])

[Marc Pelow]

Thanks, George. I appreciate your response. However, it appears that the code overlooks the Manag_ID, if I'm not mistaken. It seems to be tagging only the first and last observations for a firm, rather than the first and last observations for the managers themselves(?)

[George Ford]

change Firm_ID to Manag_ID

[Marc Pelow]

Thanks for your response. I realize my initial post might have been somewhat unclear. The current code successfully tags the observations for the first and last year of a manager's tenure. However, I would like to modify it to tag the last two years of the outgoing manager's tenure, as well as the first two years of the incoming manager's tenure.

[George Ford]

See if this works:

bys Firm_ID (FY): g first = inlist(FY,FY[1],FY[2])
bys Firm_ID (FY): g last = inlist(FY,FY[_N-1],FY[_N])

[Nick Cox]

The technique of George Ford can be extended or varied in several ways. Here is one:

webuse grunfeld, clear

bysort company (year) : gen first2 = inlist(_n, 1, 2)

[George Ford]

Manage_ID has multiple observations per FY, so I don't think Nick's code works (something rarely said).

[George Ford]

You can see the difference with your dataex.

bys Manag_ID (FY): g first = inlist(FY,FY[1],FY[2])
bys Manag_ID (FY): g last = inlist(FY,FY[_N-1],FY[_N])
bys Manag_ID (FY): g firstlast = inlist(FY,FY[1],FY[2],FY[_N-1],FY[_N])

bysort Manag_ID (FY) : g first2 = inlist(_n, 1, 2)
bysort Manag_ID (FY) : g last2 = inlist(_n, _N-1, _N)
bysort Manag_ID (FY) : g firstlast2 = inlist(_n, 1,2, _N-1, _N)

[Nick Cox]

Indeed. I skimmed through the real question far too quickly

[Marc Pelow]

Thank you. The code is still missing the first two and last two years for each manager. Specifically:

* Example generated by -dataex-. For more info, type help dataex
clear
input double Firm_ID int FY long Manag_ID str48 Manag_Name float(first last firstlast)
4295903627 2010 13499 "Thomas M. Ryan" 1 1 1
4295903627 2010 13499 "Thomas M. Ryan" 1 1 1
4295903627 2010 13499 "Thomas M. Ryan" 1 1 1
4295903627 2012 13502 "Larry J. Merlo" 1 0 1
4295903627 2012 13502 "Larry J. Merlo" 1 0 1
4295903627 2012 13502 "Larry J. Merlo" 1 0 1
4295903627 2012 13502 "Larry J. Merlo" 1 0 1
4295903627 2013 13502 "Larry J. Merlo" 0 0 0
4295903627 2013 13502 "Larry J. Merlo" 0 0 0
4295903627 2013 13502 "Larry J. Merlo" 0 0 0
4295903627 2013 13502 "Larry J. Merlo" 0 0 0
4295903627 2014 13502 "Larry J. Merlo" 0 0 0
4295903627 2014 13502 "Larry J. Merlo" 0 0 0
4295903627 2014 13502 "Larry J. Merlo" 0 0 0
4295903627 2014 13502 "Larry J. Merlo" 0 0 0
4295903627 2015 13502 "Larry J. Merlo" 0 0 0
4295903627 2015 13502 "Larry J. Merlo" 0 0 0
4295903627 2015 13502 "Larry J. Merlo" 0 0 0
4295903627 2015 13502 "Larry J. Merlo" 0 0 0
4295903627 2016 13502 "Larry J. Merlo" 0 0 0
4295903627 2016 13502 "Larry J. Merlo" 0 0 0
4295903627 2016 13502 "Larry J. Merlo" 0 0 0
4295903627 2016 13502 "Larry J. Merlo" 0 0 0
4295903627 2017 13502 "Larry J. Merlo" 0 0 0
4295903627 2017 13502 "Larry J. Merlo" 0 0 0
4295903627 2017 13502 "Larry J. Merlo" 0 0 0
4295903627 2017 13502 "Larry J. Merlo" 0 0 0
4295903627 2018 13502 "Larry J. Merlo" 0 1 1
4295903627 2018 13502 "Larry J. Merlo" 0 1 1
4295903627 2018 13502 "Larry J. Merlo" 0 1 1
4295903627 2018 13502 "Larry J. Merlo" 0 1 1
end

The code successfully identifies the last year (2010) for Thomas M. Ryan as manager. However, it misses tagging the two subsequent years for the new manager, Larry J. Merlo. Specifically, it only assigns a tag for 2012 (first year), while 2013 (seconed year) should also be included. Similarly, it only tags 2018 (last year) for Larry J. Merlo, neglecting the previous year (2017).

[Nick Cox]

Try

 
 bys Manag_ID (FY): g firstlast = inlist(FY,FY[1],FY[1]+1, FY[_N], FY[_N]-1)

[Marc Pelow]

Thanks Nick. This is getting close to what I am trying to do.

The current tagging logic seems to target the first and last two years for every CEO, irrespective of old or new CEO. Instead, I want the code to focus on transitions between CEOs. Please refer to the data sample below.

For example, for James Muller (new CEO) taking over from Paul Furgeson (old CEO) in Company A, only the first two years of Muller's tenure (2017 and 2018) should be tagged but not the ones after (2019).

Similarly, for Donald E. Washkewicz in Company B, only his final two years before T. Williams takes over should be captured rather than the first two years of Donald E. Washkewicz.
The variable "desired" tags the observations according to what I am trying to achieve.
Sorry for not being very clear on that.

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 Firm_Name double Firm_ID long Manag_ID str48 Manag_Name int CC_FY float(firstlast desired)
"A" 1234567890 29308 "Paul L. Furgeson"     2015 1 1
"A" 1234567890 29308 "Paul L. Furgeson"     2015 1 1
"A" 1234567890 29308 "Paul L. Furgeson"     2016 1 1
"A" 1234567890 10405 "James Muller"         2017 1 1
"A" 1234567890 10405 "James Muller"         2017 1 1
"A" 1234567890 10405 "James Muller"         2018 1 1
"A" 1234567890 10405 "James Muller"         2019 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2010 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2010 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2010 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2014 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2014 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2014 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
end

[Nick Cox]

I think I can get closer to what you want. I don't see why the 2 year gap for B should be ignored.

*

 Example generated by -dataex-. For more info, type help dataex
clear
input str1 Firm_Name double Firm_ID long Manag_ID str48 Manag_Name int CC_FY float(firstlast desired)
"A" 1234567890 29308 "Paul L. Furgeson"     2015 1 1
"A" 1234567890 29308 "Paul L. Furgeson"     2015 1 1
"A" 1234567890 29308 "Paul L. Furgeson"     2016 1 1
"A" 1234567890 10405 "James Muller"         2017 1 1
"A" 1234567890 10405 "James Muller"         2017 1 1
"A" 1234567890 10405 "James Muller"         2018 1 1
"A" 1234567890 10405 "James Muller"         2019 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2010 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2010 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2010 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2011 1 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2012 0 0
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2013 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2014 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2014 1 1
"B" 4295903206 23195 "Donald E. Washkewicz" 2014 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2017 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
"B" 4295903206 40465 "Thomas L. Williams"   2018 1 1
end

* bys Manag_ID (FY): g firstlast = inlist(FY,FY[1],FY[1]+1, FY[_N], FY[_N]-1)

bysort Firm_ID (CC_FY) : gen counter = sum(Manag_ID != Manag_ID[_n-1]) 
bysort Firm_ID (CC_FY) : gen last = counter[_N]
bysort Firm_ID Manag_ID (CC_FY) : gen firstY = CC_FY[1]
bysort Firm_ID Manag_ID (CC_FY) : gen lastY = CC_FY[_N]


gen wanted = (inlist(CC_FY, lastY, lastY - 1) & counter < last) | (inlist(CC_FY, firstY, firstY + 1) & counter > 1) 

format Firm_ID %12.0f 

sort Firm_ID CC_FY 

list, sepby(Manag_ID) 

    +---------------------------------------------------------------------------------------------------------------------------------+
     | Firm_N~e      Firm_ID   Manag_ID             Manag_Name   CC_FY   firstl~t   desired   counter   last   firstY   lastY   wanted |
     |---------------------------------------------------------------------------------------------------------------------------------|
  1. |        A   1234567890      29308       Paul L. Furgeson    2015          1         1         1      2     2015    2016        1 |
  2. |        A   1234567890      29308       Paul L. Furgeson    2015          1         1         1      2     2015    2016        1 |
  3. |        A   1234567890      29308       Paul L. Furgeson    2016          1         1         1      2     2015    2016        1 |
     |---------------------------------------------------------------------------------------------------------------------------------|
  4. |        A   1234567890      10405           James Muller    2017          1         1         2      2     2017    2019        1 |
  5. |        A   1234567890      10405           James Muller    2017          1         1         2      2     2017    2019        1 |
  6. |        A   1234567890      10405           James Muller    2018          1         1         2      2     2017    2019        1 |
  7. |        A   1234567890      10405           James Muller    2019          1         0         2      2     2017    2019        0 |
     |---------------------------------------------------------------------------------------------------------------------------------|
  8. |        B   4295903206      23195   Donald E. Washkewicz    2010          1         0         1      2     2010    2014        0 |
  9. |        B   4295903206      23195   Donald E. Washkewicz    2010          1         0         1      2     2010    2014        0 |
 10. |        B   4295903206      23195   Donald E. Washkewicz    2010          1         0         1      2     2010    2014        0 |
 11. |        B   4295903206      23195   Donald E. Washkewicz    2011          1         0         1      2     2010    2014        0 |
 12. |        B   4295903206      23195   Donald E. Washkewicz    2011          1         0         1      2     2010    2014        0 |
 13. |        B   4295903206      23195   Donald E. Washkewicz    2011          1         0         1      2     2010    2014        0 |
 14. |        B   4295903206      23195   Donald E. Washkewicz    2011          1         0         1      2     2010    2014        0 |
 15. |        B   4295903206      23195   Donald E. Washkewicz    2012          0         0         1      2     2010    2014        0 |
 16. |        B   4295903206      23195   Donald E. Washkewicz    2012          0         0         1      2     2010    2014        0 |
 17. |        B   4295903206      23195   Donald E. Washkewicz    2012          0         0         1      2     2010    2014        0 |
 18. |        B   4295903206      23195   Donald E. Washkewicz    2012          0         0         1      2     2010    2014        0 |
 19. |        B   4295903206      23195   Donald E. Washkewicz    2013          1         1         1      2     2010    2014        1 |
 20. |        B   4295903206      23195   Donald E. Washkewicz    2013          1         1         1      2     2010    2014        1 |
 21. |        B   4295903206      23195   Donald E. Washkewicz    2013          1         1         1      2     2010    2014        1 |
 22. |        B   4295903206      23195   Donald E. Washkewicz    2013          1         1         1      2     2010    2014        1 |
 23. |        B   4295903206      23195   Donald E. Washkewicz    2014          1         1         1      2     2010    2014        1 |
 24. |        B   4295903206      23195   Donald E. Washkewicz    2014          1         1         1      2     2010    2014        1 |
 25. |        B   4295903206      23195   Donald E. Washkewicz    2014          1         1         1      2     2010    2014        1 |
     |---------------------------------------------------------------------------------------------------------------------------------|
 26. |        B   4295903206      40465     Thomas L. Williams    2017          1         1         2      2     2017    2018        1 |
 27. |        B   4295903206      40465     Thomas L. Williams    2017          1         1         2      2     2017    2018        1 |
 28. |        B   4295903206      40465     Thomas L. Williams    2017          1         1         2      2     2017    2018        1 |
 29. |        B   4295903206      40465     Thomas L. Williams    2017          1         1         2      2     2017    2018        1 |
 30. |        B   4295903206      40465     Thomas L. Williams    2018          1         1         2      2     2017    2018        1 |
 31. |        B   4295903206      40465     Thomas L. Williams    2018          1         1         2      2     2017    2018        1 |
 32. |        B   4295903206      40465     Thomas L. Williams    2018          1         1         2      2     2017    2018        1 |
 33. |        B   4295903206      40465     Thomas L. Williams    2018          1         1         2      2     2017    2018        1 |
     +---------------------------------------------------------------------------------------------------------------------------------+

.