Drop variables

[Kim Kandelaars]

Hello,

In the research I am replicating I have to track the performance of a CEO in his old firm.
Therefore, in my data, I have to drop CEO's that do not have an 'old firm'

In Stata, I want to drop EXECID (the executives) if they are only 'matched' with one GVKEY (company).

So:
EXECID GVKEY
1234. 1111
1234. 1000



Can you help me finding a command for this?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6(GVKEY EXECID) double YEAR
"001004" "09249" 2012
"001004" "09252" 2012
"001004" "19999" 2012
"001004" "33979" 2012
"001004" "41787" 2012
"001004" "44858" 2012
"001004" "09249" 2013
"001004" "09252" 2013
"001004" "19999" 2013
"001004" "41787" 2013
"001004" "44858" 2013
"001004" "46404" 2013
"001004" "09249" 2014
"001004" "09252" 2014
"001004" "41787" 2014
"001004" "46404" 2014
"001004" "48195" 2014
"001004" "09249" 2015
"001004" "09252" 2015
"001004" "19999" 2015
"001004" "41787" 2015
"001004" "46404" 2015
"001004" "48195" 2015
"001004" "09249" 2016
"001004" "09252" 2016
"001004" "19999" 2016
"001004" "41787" 2016
"001004" "48195" 2016
"001004" "51706" 2016
"001004" "09249" 2017
"001004" "09252" 2017
"001004" "41787" 2017
"001004" "48195" 2017
"001004" "51706" 2017
"001004" "55405" 2017
"001004" "41787" 2018
"001004" "48195" 2018
"001004" "51706" 2018
"001004" "55405" 2018
"001004" "57007" 2018
"001004" "41787" 2019
"001004" "48195" 2019
"001004" "51706" 2019
"001004" "57007" 2019
"001004" "58967" 2019
"001004" "58968" 2019
"001045" "16654" 2012
"001045" "28279" 2012
"001045" "26059" 2012
"001045" "41119" 2012
"001045" "44119" 2012
"001045" "16654" 2013
"001045" "26059" 2013
"001045" "41119" 2013
"001045" "44119" 2013
"001045" "46189" 2013
"001045" "46190" 2013
"001045" "46191" 2013
"001045" "46192" 2013
"001045" "46193" 2013
"001045" "46189" 2014
"001045" "46190" 2014
"001045" "46191" 2014
"001045" "46192" 2014
"001045" "46193" 2014
"001045" "46189" 2015
"001045" "46190" 2015
"001045" "46191" 2015
"001045" "46192" 2015
"001045" "46193" 2015
"001045" "46189" 2016
"001045" "46190" 2016
"001045" "46191" 2016
"001045" "46192" 2016
"001045" "46193" 2016
"001045" "51368" 2016
"001045" "46189" 2017
"001045" "46190" 2017
"001045" "46191" 2017
"001045" "46193" 2017
"001045" "51368" 2017
"001045" "46189" 2018
"001045" "46190" 2018
"001045" "46191" 2018
"001045" "46193" 2018
"001045" "51368" 2018
"001045" "46189" 2019
"001045" "46190" 2019
"001045" "46191" 2019
"001045" "46193" 2019
"001045" "51368" 2019
"001072" "23168" 2012
"001072" "28509" 2012
"001072" "28510" 2012
"001072" "30078" 2012
"001072" "44860" 2012
"001072" "45158" 2012
"001072" "23168" 2013
"001072" "28510" 2013
"001072" "30078" 2013
end

[Andrew Musau]

bys EXECID (GVKEY): drop if GVKEY[1]==GVKEY[_N]

[Kim Kandelaars]

Thank you very much. In addition I have another question.
I have dropped the executives that only were matched to 1 firm. But now I need to generate a variable that contains the ROE of the 'old firm' of the EXECID. Is there a way to do this?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 GVKEY double YEAR float(ROE ROE1) str6 EXECID
"021487" 2013   .14011958   .10725596 "01644"
"021487" 2012   .10725596    .3156338 "01644"
"021487" 2016   1.4369515   .14011958 "01644"
"034055" 2018   .04226592   .02247191 "01644"
"003708" 2013   .02371253 .0028068514 "10773"
"003708" 2012 .0028068514   .36013535 "10773"
"014624" 2018   .04656632   .06886792 "10773"
"024171" 2018 -.024587417  .070819855 "18057"
"024171" 2016   .13283904   .15032643 "18057"
"024171" 2017  .070819855   .13283904 "18057"
"030059" 2015   .20286843    .0779007 "18057"
"030059" 2012   .21626335   .05693323 "18057"
"030059" 2013   .07115809   .21626335 "18057"
"030059" 2014    .0779007   .07115809 "18057"
"006565" 2013   .02536918   .03287806 "25688"
"007343" 2018    .4376974   .27329358 "25688"
"029887" 2015   .13130169   .11406312 "25711"
"029887" 2014   .11406312   .11701696 "25711"
"029887" 2013   .11701696    .1069726 "25711"
"029887" 2012    .1069726  .071520135 "25711"
"144009" 2019    .1018636   .07054405 "25711"
"010115" 2012    .1509829    .3042339 "25904"
"020904" 2019  -.09623293   -.0458251 "25904"
"002290" 2012    .4139001   .07380759 "32922"
"010190" 2016   .06117021   -.3854875 "32922"
"010190" 2017    .0950495   .06117021 "32922"
"006025" 2012   -.8626045   .27157307 "33078"
"062655" 2018  -1.0951017   .07229547 "33078"
"179819" 2012  -.03919616    .4390045 "34850"
"179819" 2013   -.0281191  -.03919616 "34850"
"316056" 2019    .5304991    .6680492 "34850"
"316056" 2018    .6680492    .6805279 "34850"
"316056" 2017    .6805279    .4223023 "34850"
"017928" 2019  .031817425   .03857611 "35286"
"063800" 2015   .26526716   -.1225681 "35286"
"001860" 2015   .14677572   .13336678 "40585"
"001860" 2013   .15866114   .14033614 "40585"
"001860" 2014   .13336678   .15866114 "40585"
"028338" 2019  -.10580003  -.15228504 "40585"
"137232" 2016   .08960991   .09843937 "41044"
"137232" 2014   .10020769   .07663015 "41044"
"137232" 2015   .09843937   .10020769 "41044"
"137232" 2013   .07663015   .07214896 "41044"
"259518" 2019    .0875064    .1082388 "41044"
"005568" 2018  -.19730143    .1665657 "41083"
"005568" 2015  .010990726  -.05882648 "41083"
"005568" 2016    .0633216  .010990726 "41083"
"005568" 2017    .1665657    .0633216 "41083"
"005568" 2019   .03748329  -.19730143 "41083"
"164471" 2012    .1001702   .08291937 "41083"
"020344" 2014   .21548544   .05778679 "42926"
"020344" 2015  -.08416764   .21548544 "42926"
"027800" 2017   -.1706739  .026738206 "42926"
"027800" 2016  .026738206  -.02843532 "42926"
"029565" 2017   -.6462247    .7432463 "46820"
"029565" 2018   .17454296   -.6462247 "46820"
"187698" 2012   .07704341   .10866522 "46820"
"187698" 2013    .1060656   .07704341 "46820"
"187698" 2014  .022946447    .1060656 "46820"
"146648" 2012 -.072135195   .25373268 "48926"
"187653" 2015    .3362939   .02171046 "48926"
"187653" 2014   .02171046  -.30850485 "48926"
end

[Andrew Musau]

But now I need to generate a variable that contains the ROE of the 'old firm' of the EXECID.

What if there are several old firms (more than one)? I assume that the current firm is the last observation of EXECID if sorted by year.

[Kim Kandelaars]

Then I have to pick the most recent old firm. Yes the current firm is the last observation.

[Andrew Musau]

EDITED:

gsort EXECID -YEAR GVKEY
by EXECID: gen old= sum(sum(GVKEY!=GVKEY[_n-1] & _n>1))==1
bys EXECID (YEAR): egen wanted= max(cond(old, ROE, .))

Res.:

. l, sepby( EXECID )

     +------------------------------------------------------------------+
     |  GVKEY   YEAR         ROE        ROE1   EXECID   old      wanted |
     |------------------------------------------------------------------|
  1. | 021487   2012     .107256    .3156338    01644     0    1.436952 |
  2. | 021487   2013    .1401196     .107256    01644     0    1.436952 |
  3. | 021487   2016    1.436952    .1401196    01644     1    1.436952 |
  4. | 034055   2018    .0422659    .0224719    01644     0    1.436952 |
     |------------------------------------------------------------------|
  5. | 003708   2012    .0028069    .3601353    10773     0    .0237125 |
  6. | 003708   2013    .0237125    .0028069    10773     1    .0237125 |
  7. | 014624   2018    .0465663    .0688679    10773     0    .0237125 |
     |------------------------------------------------------------------|
  8. | 030059   2012    .2162634    .0569332    18057     0    .2028684 |
  9. | 030059   2013    .0711581    .2162634    18057     0    .2028684 |
 10. | 030059   2014    .0779007    .0711581    18057     0    .2028684 |
 11. | 030059   2015    .2028684    .0779007    18057     1    .2028684 |
 12. | 024171   2016     .132839    .1503264    18057     0    .2028684 |
 13. | 024171   2017    .0708199     .132839    18057     0    .2028684 |
 14. | 024171   2018   -.0245874    .0708199    18057     0    .2028684 |
     |------------------------------------------------------------------|
 15. | 006565   2013    .0253692    .0328781    25688     1    .0253692 |
 16. | 007343   2018    .4376974    .2732936    25688     0    .0253692 |
     |------------------------------------------------------------------|
 17. | 029887   2012    .1069726    .0715201    25711     0    .1313017 |
 18. | 029887   2013     .117017    .1069726    25711     0    .1313017 |
 19. | 029887   2014    .1140631     .117017    25711     0    .1313017 |
 20. | 029887   2015    .1313017    .1140631    25711     1    .1313017 |
 21. | 144009   2019    .1018636     .070544    25711     0    .1313017 |
     |------------------------------------------------------------------|
 22. | 010115   2012    .1509829    .3042339    25904     1    .1509829 |
 23. | 020904   2019   -.0962329   -.0458251    25904     0    .1509829 |
     |------------------------------------------------------------------|
 24. | 002290   2012    .4139001    .0738076    32922     1    .4139001 |
 25. | 010190   2016    .0611702   -.3854875    32922     0    .4139001 |
 26. | 010190   2017    .0950495    .0611702    32922     0    .4139001 |
     |------------------------------------------------------------------|
 27. | 006025   2012   -.8626045    .2715731    33078     1   -.8626045 |
 28. | 062655   2018   -1.095102    .0722955    33078     0   -.8626045 |
     |------------------------------------------------------------------|
 29. | 179819   2012   -.0391962    .4390045    34850     0   -.0281191 |
 30. | 179819   2013   -.0281191   -.0391962    34850     1   -.0281191 |
 31. | 316056   2017    .6805279    .4223023    34850     0   -.0281191 |
 32. | 316056   2018    .6680492    .6805279    34850     0   -.0281191 |
 33. | 316056   2019    .5304991    .6680492    34850     0   -.0281191 |
     |------------------------------------------------------------------|
 34. | 063800   2015    .2652672   -.1225681    35286     1    .2652672 |
 35. | 017928   2019    .0318174    .0385761    35286     0    .2652672 |
     |------------------------------------------------------------------|
 36. | 001860   2013    .1586611    .1403361    40585     0    .1467757 |
 37. | 001860   2014    .1333668    .1586611    40585     0    .1467757 |
 38. | 001860   2015    .1467757    .1333668    40585     1    .1467757 |
 39. | 028338   2019      -.1058    -.152285    40585     0    .1467757 |
     |------------------------------------------------------------------|
 40. | 137232   2013    .0766302     .072149    41044     0    .0896099 |
 41. | 137232   2014    .1002077    .0766302    41044     0    .0896099 |
 42. | 137232   2015    .0984394    .1002077    41044     0    .0896099 |
 43. | 137232   2016    .0896099    .0984394    41044     1    .0896099 |
 44. | 259518   2019    .0875064    .1082388    41044     0    .0896099 |
     |------------------------------------------------------------------|
 45. | 164471   2012    .1001702    .0829194    41083     1    .1001702 |
 46. | 005568   2015    .0109907   -.0588265    41083     0    .1001702 |
 47. | 005568   2016    .0633216    .0109907    41083     0    .1001702 |
 48. | 005568   2017    .1665657    .0633216    41083     0    .1001702 |
 49. | 005568   2018   -.1973014    .1665657    41083     0    .1001702 |
 50. | 005568   2019    .0374833   -.1973014    41083     0    .1001702 |
     |------------------------------------------------------------------|
 51. | 020344   2014    .2154854    .0577868    42926     0   -.0841676 |
 52. | 020344   2015   -.0841676    .2154854    42926     1   -.0841676 |
 53. | 027800   2016    .0267382   -.0284353    42926     0   -.0841676 |
 54. | 027800   2017   -.1706739    .0267382    42926     0   -.0841676 |
     |------------------------------------------------------------------|
 55. | 187698   2012    .0770434    .1086652    46820     0    .0229464 |
 56. | 187698   2013    .1060656    .0770434    46820     0    .0229464 |
 57. | 187698   2014    .0229464    .1060656    46820     1    .0229464 |
 58. | 029565   2017   -.6462247    .7432463    46820     0    .0229464 |
 59. | 029565   2018     .174543   -.6462247    46820     0    .0229464 |
     |------------------------------------------------------------------|
 60. | 146648   2012   -.0721352    .2537327    48926     1   -.0721352 |
 61. | 187653   2014    .0217105   -.3085048    48926     0   -.0721352 |
 62. | 187653   2015    .3362939    .0217105    48926     0   -.0721352 |
     +------------------------------------------------------------------+

[Kim Kandelaars]

Maybe I was not clear because in this case the wanted variable is the ROE of the most recent year and thus the new firm. What I need is the ROE of the year that is not the most recent year.

[Andrew Musau]

I made a correction to the code. See my edit in # 6.

[Kim Kandelaars]

Thank you!

[Nick Cox]

Good that you seem to have solved your problem, but let's be clear: your question is about dropping observations (rows, cases or records in your dataset), not variables (columns or fields in your dataset).