How to exclude duplicates from calculation of total managers in each year

[Fimi Karimi]

I need to calculate the total number of managers for each company in each year in my panel data. I used this command for 1999:


generate Manager1999 = 1 if 1999>= YearStartRole & 1999<= YearEndRole (I write it for each year)
bysort CompanyID Year : gen cummulativetotal = sum( Manager )
by CompanyID Year : egen totalmanager = max( cummulativetotal )


Since for some of the managers the date of start and end of role is not continuous and their ID repeats in some years more than once (for example they enter the company on January and leave the company after 3 months and again entered the company on July and stayed by the end of the year). Command above calculate manage in the above example 2 person while I want to calculate the manager just onc

STATUS	CompanyID	ManagerID	Gender	DateStartRole	DateEndRole
Continuous	10322	370125	M	1997-03-01	2003-04-01
Continuous	27631	220631	M	1962-01-01	2007-12-04
Continuous	20292	83597	M	1985-01-01	2009-01-26
Continuous	12041	375854	M	1982-01-01	2006-02-24
Continuous	8446	201445	M	1989-01-01	2006-05-05
Continuous	731321	374735	M	2001-03-30	2005-03-31
Continuous	967	203016	M	1985-08-01	2003-11-06
Continuous	32726	450230	M	1998-07-01	2007-07-11
Continuous	20007	445338	M	1999-03-11	2005-11-01
Continuous	33206	445338	M	1997-07-22	2002-12-02
Non-Continuous	13266	377537	M	2008-01-11	2008-04-17
Non-Continuous	13266	377537	M	2008-08-01	2008-12-11

A managers can be the manager of multiple companies, and I just want to exclude them in calculation if their ID repeat in one year for a unique company.

your help is appreciated.

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear*
input str15 status long(companyid managerid) str2 gender str11 datestartrole str10 dateendrole
"Continuous "      10322 370125 "M " "1997-03-01 " "2003-04-01"
"Continuous "      27631 220631 "M " "1962-01-01 " "2007-12-04"
"Continuous "      20292  83597 "M " "1985-01-01 " "2009-01-26"
"Continuous "      12041 375854 "M " "1982-01-01 " "2006-02-24"
"Continuous "       8446 201445 "M " "1989-01-01 " "2006-05-05"
"Continuous "     731321 374735 "M " "2001-03-30 " "2005-03-31"
"Continuous "        967 203016 "M " "1985-08-01 " "2003-11-06"
"Continuous "      32726 450230 "M " "1998-07-01 " "2007-07-11"
"Continuous "      20007 445338 "M " "1999-03-11 " "2005-11-01"
"Continuous "      33206 445338 "M " "1997-07-22 " "2002-12-02"
"Non-Continuous "  13266 377537 "M " "2008-01-11 " "2008-04-17"
"Non-Continuous "  13266 377537 "M " "2008-08-01 " "2008-12-11"
end

//    CLEAN DATA
replace status = trim(itrim(status)) // THIS IS OPTIONAL

foreach v of varlist date* {    // THIS IS REQUIRED
    gen _`v' = daily(`v', "YMD"), after(`v')
    assert missing(`v') == missing(_`v')
    format _`v' %td
    drop `v'
    rename _`v' `v'
}

//    IDENTIFY YEARS
foreach x in start end {
    gen `x'_year = year(date`x'role)
}

//    LOOP OVER YEARS AND CALCULATE NUMBER OF MANAGERS AT EACH FIRM IN GIVEN YEAR
summ start_year, meanonly
local low = r(min)
summ end_year, meanonly
local high = r(max)

forvalues y = `low'/`high' {
    frame put companyid managerid if inrange(`y', start_year, end_year), into(working)
    frame working {
        by companyid (managerid), sort: gen n_managers_`y' = sum(managerid != managerid[_n-1])
        by companyid (managerid): keep if _n == _N
    }
    frlink m:1 companyid, frame(working)
    frget n_managers_`y', from(working)
    drop working
    frame drop working
}

Note: Requires version 16 or later.

Because you displayed your data by showing some kind of table, I cannot tell whether your date variables are actual Stata date variables or just strings that read like variables to the human eye. They must be the former for the code to work, so at the top of the code, a conversion is performed. I said in the comment that it is "required," but if you are starting out with a real Stata numeric date variable, then this part should be skipped over.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 18, 17, 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.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 18, 17, 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.

[Fimi Karimi]

Thank you Clyde. My data is:

* Example generated by -dataex-. For more info, type help dataex
clear
input long(CompanyID DirectorID) str1 Gender int(YearStart YearEnd)
 3   59796 "M" 2005 2007
 3  340769 "M" 1995 2007
 3  340791 "M" 2002 2007
 3  340825 "M" 1995 2007
 3  340842 "M" 2003 2007
 3  341118 "M" 1996 2005
 3  341132 "M" 2000 2007
 3  341153 "M" 2000 2005
 3  341168 "M" 2003 2004
 3  341583 "M" 1997 2003
 3  505279 "M" 2006 2007
 3  547192 "M" 1996 1998
 6   33553 "M" 1995 2012
 6   36503 "M" 2005 2007
 6   37576 "F" 2007 2008
 6   37576 "F" 2008 2010
 6   37576 "F" 2010 2010
 6   86917 "M" 1976 2024
 6   87882 "M" 2002 2013
 6  141620 "M" 1999 2006
 6  327069 "M" 1976 2024
 6  340891 "M" 1993 2005
 6  341596 "M" 2002 2024
 6  341598 "F" 2002 2007
 6  341863 "M" 1999 2003
 6  341863 "M" 2006 2016
 6  370490 "M" 2007 2024
 6  457013 "F" 2012 2021
 6  533773 "F" 2016 2024
 6  747738 "F" 2021 2024
 6  879271 "M" 2009 2024
 6 1057384 "M" 2019 2024
 6 1112878 "M" 2012 2024
 6 1299622 "M" 2001 2002
 6 1366174 "M" 2014 2020
 6 1373247 "M" 1999 2001
 6 1391443 "F" 2017 2024
 6 2408676 "F" 2020 2024
12    8066 "M" 2009 2014
12   32412 "M" 2009 2013
12   36354 "M" 2009 2013
12   40911 "M" 2011 2013
12  140120 "M" 2007 2014
12  140488 "F" 1993 2003
12  201375 "M" 1988 1996
12  310234 "M" 2010 2013
12  320417 "M" 1969 2009
12  324072 "M" 2003 2006
12  341468 "M" 2007 2010
12  341979 "M" 1996 2006
12  371952 "M" 2008 2010
12  371960 "M" 2007 2014
12  374153 "M" 1985 2007
12  374182 "M" 2003 2006
12  374216 "M" 2003 2007
12  374225 "M" 1990 2006
12  374241 "M" 2003 2007
12  374273 "M" 1995 2003
12  374276 "M" 2003 2009
12  374408 "M" 1985 2003
12  374449 "M" 1986 2003
12  374887 "M" 2003 2004
12  374892 "F" 2003 2007
12  493920 "M" 2006 2011
12  551393 "M" 2007 2010
12  552193 "M" 2007 2013
12  555070 "M" 2013 2014
12  593295 "M" 2007 2008
12 1264202 "M" 2013 2014
22  746970 "M" 2001 2024
22  746973 "M" 2000 2011
22  746984 "M" 2001 2012
22 1146025 "F" 2012 2017
26  338835 "M" 2000 2007
26  373892 "M" 1995 2007
26  373894 "M" 1995 2007
26  373897 "M" 1997 2007
26  373899 "M" 2002 2007
26  481237 "M" 1995 2001
28   14242 "M" 1988 2014
28  102642 "M" 2010 2022
28  220968 "M" 2001 2024
28  335966 "M" 1995 2006
28  335970 "M" 1983 2015
28  335975 "M" 2003 2013
28  335980 "M" 2000 2007
28  335983 "M" 2000 2013
28  335986 "M" 1983 2005
28  335987 "F" 1997 2012
28  335989 "M" 2001 2015
28  335990 "M" 1996 2015
28  335993 "M" 2000 2021
28  335994 "M" 2000 2006
28  335996 "M" 1986 2007
28  335999 "M" 1983 2005
28  336002 "F" 2000 2024
28  336004 "M" 1995 2009
28  336006 "M" 1983 1990
28  336006 "M" 1996 2015
28  336025 "F" 2005 2013
end

------------------ copy up to and including the previous line ------------------

I want to calculate the "total number of manager" and "total number of female manager" for each company at each year. But if a manager enter and leave a company more than once in a year, It need to be calculated just once. Each director ID can be the manager of multiple companies, thus I want to calculate it for each of companies once in a year in which he/she enter and leave more than once.

[Fimi Karimi]

The command gave me following error, so I changed the date to year in my data above:
foreach v of varlist date* {
2.
. gen _`v' = daily(`v', "YMD"), after(`v')
3.
. assert missing(`v') == missing(_`v')
4.
. format _`v' %td
5.
. drop `v'
6.
. rename _`v' `v'
7.
. }
variable date* not found
r(111);

. foreach v of varlist MinofDateStartRole MaxofDateEndRole * {
2.
. gen _`v' = daily(`v', "YMD"), after(`v')
3.
. assert missing(`v') == missing(_`v')
4.
. format _`v' %td
5.
. drop `v'
6.
. rename _`v' `v'
7.
. }
type mismatch
r(109);

[Clyde Schechter]

So, with the -dataex- example, we can write the code correctly, reflecting your data structure and variable names.

summ YearStart, meanonly
local low = r(min)
summ YearEnd, meanonly
local high = r(max)

forvalues y = `low'/`high' {
    frame put CompanyID DirectorID Gender if inrange(`y', YearStart, YearEnd), into(working)
    frame working {
        by CompanyID Gender (DirectorID), sort: gen n_managers_`y' = sum(DirectorID != DirectorID[_n-1])
        by CompanyID Gender (DirectorID): keep if _n == _N
    }
    frlink m:1 CompanyID Gender, frame(working)
    frget n_managers_`y', from(working)
    drop working
    frame drop working
    foreach x in M F {
        by CompanyID, sort: egen n_managers_`x'_`y' = max(cond(Gender == "`x'", ///
            n_managers_`y', .))
    }
    mvencode n_managers_*_`y', mv(0)
    replace n_managers_`y' = n_managers_M_`y' + n_managers_F_`y'
    order n_managers_M_`y' n_managers_F_`y', after(n_managers_`y')
}
keep CompanyID n_managers*
by CompanyID, sort: keep if _n == 1
reshape long n_managers_ n_managers_M_ n_managers_F_, i(CompanyID) j(Year)
rename *_ *

Added: You might want to follow the above code with -drop if n_managers == 0- to get rid of the numerous years when a company has no Directors listed in your data set.

[Fimi Karimi]

Thanks Clyde. I run your command but It shows me only year 1935 :
. summ YearStart, meanonly

.
. local low = r(min)

.
. summ YearEnd, meanonly

.
. local high = r(max)

.
.
.
. forvalues y = `low'/`high' {
2.
. frame put CompanyID DirectorID Gender if inrange(`y', YearStart, YearEnd), into(working)
3.
. frame working {
4.
. by CompanyID Gender (DirectorID), sort: gen n_managers_`y' = sum(DirectorID != DirectorID[_n-1])
5.
. by CompanyID Gender (DirectorID): keep if _n == _N
6.
. }
7.
. frlink m:1 CompanyID Gender, frame(working)
8.
. frget n_managers_`y', from(working)
9.
. drop working
10.
. frame drop working
11.
. foreach x in M F {
12.
. by CompanyID, sort: egen n_managers_`x'_`y' = max(cond(Gender == "`x'", ///
13.
. n_managers_`y', .))
14.
. }
15.
. mvencode n_managers_*_`y', mv(0)
16.
. replace n_managers_`y' = n_managers_M_`y' + n_managers_F_`y'
17.
. order n_managers_M_`y' n_managers_F_`y', after(n_managers_`y')
18.
. }
(0 observations deleted)
(213,679 observations in frame default unmatched)
(213,679 missing values generated)
(1 variable copied from linked frame)
parentheses unbalanced
r(132);

.
. keep CompanyID n_managers*

.
. by CompanyID, sort: keep if _n == 1
(200,023 observations deleted)

.
. reshape long n_managers_ n_managers_M_ n_managers_F_, i(CompanyID) j(Year)
(j = 1935)
(variable n_managers_M_1935 not found)
(variable n_managers_F_1935 not found)

Data Wide -> Long
-----------------------------------------------------------------------------
Number of observations 13,670 -> 13,670
Number of variables 2 -> 5
j variable (1 values) -> Year
xij variables:
n_managers_1935 -> n_managers_
n_managers_M_1935 -> n_managers_M_
n_managers_F_1935 -> n_managers_F_
-----------------------------------------------------------------------------

.
. rename *_ *

* Example generated by -dataex-. For more info, type help dataex
clear
input long CompanyID int Year float n_managers byte(n_managers_M n_managers_F)
3 1935 . . .
6 1935 . . .
12 1935 . . .
22 1935 . . .
26 1935 . . .
28 1935 . . .
29 1935 . . .
30 1935 . . .
31 1935 . . .
40 1935 . . .
47 1935 . . .
50 1935 . . .
54 1935 . . .
59 1935 . . .
71 1935 . . .
72 1935 . . .
73 1935 . . .
78 1935 . . .
79 1935 . . .
81 1935 . . .
83 1935 . . .
84 1935 . . .
98 1935 . . .
99 1935 . . .
103 1935 . . .
108 1935 . . .
112 1935 . . .
113 1935 . . .
114 1935 . . .
117 1935 . . .
119 1935 . . .
123 1935 . . .
124 1935 . . .
125 1935 . . .
128 1935 . . .
129 1935 . . .
130 1935 . . .
137 1935 . . .
142 1935 . . .
143 1935 . . .
145 1935 . . .
148 1935 . . .
150 1935 . . .
151 1935 . . .
152 1935 . . .
155 1935 . . .
159 1935 . . .
167 1935 . . .
183 1935 . . .
186 1935 . . .
190 1935 . . .
191 1935 . . .
194 1935 . . .
195 1935 . . .
197 1935 . . .
203 1935 . . .
206 1935 . . .
207 1935 . . .
212 1935 . . .
222 1935 . . .
229 1935 . . .
233 1935 . . .
238 1935 . . .
239 1935 . . .
243 1935 . . .
253 1935 . . .
254 1935 . . .
258 1935 . . .
275 1935 . . .
276 1935 . . .
283 1935 . . .
306 1935 . . .
313 1935 . . .
324 1935 . . .
329 1935 . . .
331 1935 . . .
335 1935 . . .
338 1935 . . .
348 1935 . . .
383 1935 . . .
391 1935 . . .
394 1935 . . .
398 1935 . . .
401 1935 . . .
418 1935 . . .
419 1935 . . .
428 1935 . . .
430 1935 . . .
435 1935 . . .
443 1935 . . .
446 1935 . . .
472 1935 . . .
475 1935 . . .
481 1935 . . .
483 1935 . . .
485 1935 . . .
487 1935 . . .
490 1935 . . .
491 1935 . . .
530 1935 . . .
end
[/CODE]
------------------ copy up to and including the previous line ------------------

[Clyde Schechter]

When I run my code with your example data, it runs without error messages and produces correct results:

. * Example generated by -dataex-. For more info, type help dataex
. clear

. input long(CompanyID DirectorID) str1 Gender int(YearStart YearEnd)

        CompanyID    DirectorID     Gender  YearSt~t   YearEnd
  1.  3   59796 "M" 2005 2007
  2.  3  340769 "M" 1995 2007
  3.  3  340791 "M" 2002 2007
  4.  3  340825 "M" 1995 2007
  5.  3  340842 "M" 2003 2007
  6.  3  341118 "M" 1996 2005
  7.  3  341132 "M" 2000 2007
  8.  3  341153 "M" 2000 2005
  9.  3  341168 "M" 2003 2004
 10.  3  341583 "M" 1997 2003
 11.  3  505279 "M" 2006 2007
 12.  3  547192 "M" 1996 1998
 13.  6   33553 "M" 1995 2012
 14.  6   36503 "M" 2005 2007
 15.  6   37576 "F" 2007 2008
 16.  6   37576 "F" 2008 2010
 17.  6   37576 "F" 2010 2010
 18.  6   86917 "M" 1976 2024
 19.  6   87882 "M" 2002 2013
 20.  6  141620 "M" 1999 2006
 21.  6  327069 "M" 1976 2024
 22.  6  340891 "M" 1993 2005
 23.  6  341596 "M" 2002 2024
 24.  6  341598 "F" 2002 2007
 25.  6  341863 "M" 1999 2003
 26.  6  341863 "M" 2006 2016
 27.  6  370490 "M" 2007 2024
 28.  6  457013 "F" 2012 2021
 29.  6  533773 "F" 2016 2024
 30.  6  747738 "F" 2021 2024
 31.  6  879271 "M" 2009 2024
 32.  6 1057384 "M" 2019 2024
 33.  6 1112878 "M" 2012 2024
 34.  6 1299622 "M" 2001 2002
 35.  6 1366174 "M" 2014 2020
 36.  6 1373247 "M" 1999 2001
 37.  6 1391443 "F" 2017 2024
 38.  6 2408676 "F" 2020 2024
 39. 12    8066 "M" 2009 2014
 40. 12   32412 "M" 2009 2013
 41. 12   36354 "M" 2009 2013
 42. 12   40911 "M" 2011 2013
 43. 12  140120 "M" 2007 2014
 44. 12  140488 "F" 1993 2003
 45. 12  201375 "M" 1988 1996
 46. 12  310234 "M" 2010 2013
 47. 12  320417 "M" 1969 2009
 48. 12  324072 "M" 2003 2006
 49. 12  341468 "M" 2007 2010
 50. 12  341979 "M" 1996 2006
 51. 12  371952 "M" 2008 2010
 52. 12  371960 "M" 2007 2014
 53. 12  374153 "M" 1985 2007
 54. 12  374182 "M" 2003 2006
 55. 12  374216 "M" 2003 2007
 56. 12  374225 "M" 1990 2006
 57. 12  374241 "M" 2003 2007
 58. 12  374273 "M" 1995 2003
 59. 12  374276 "M" 2003 2009
 60. 12  374408 "M" 1985 2003
 61. 12  374449 "M" 1986 2003
 62. 12  374887 "M" 2003 2004
 63. 12  374892 "F" 2003 2007
 64. 12  493920 "M" 2006 2011
 65. 12  551393 "M" 2007 2010
 66. 12  552193 "M" 2007 2013
 67. 12  555070 "M" 2013 2014
 68. 12  593295 "M" 2007 2008
 69. 12 1264202 "M" 2013 2014
 70. 22  746970 "M" 2001 2024
 71. 22  746973 "M" 2000 2011
 72. 22  746984 "M" 2001 2012
 73. 22 1146025 "F" 2012 2017
 74. 26  338835 "M" 2000 2007
 75. 26  373892 "M" 1995 2007
 76. 26  373894 "M" 1995 2007
 77. 26  373897 "M" 1997 2007
 78. 26  373899 "M" 2002 2007
 79. 26  481237 "M" 1995 2001
 80. 28   14242 "M" 1988 2014
 81. 28  102642 "M" 2010 2022
 82. 28  220968 "M" 2001 2024
 83. 28  335966 "M" 1995 2006
 84. 28  335970 "M" 1983 2015
 85. 28  335975 "M" 2003 2013
 86. 28  335980 "M" 2000 2007
 87. 28  335983 "M" 2000 2013
 88. 28  335986 "M" 1983 2005
 89. 28  335987 "F" 1997 2012
 90. 28  335989 "M" 2001 2015
 91. 28  335990 "M" 1996 2015
 92. 28  335993 "M" 2000 2021
 93. 28  335994 "M" 2000 2006
 94. 28  335996 "M" 1986 2007
 95. 28  335999 "M" 1983 2005
 96. 28  336002 "F" 2000 2024
 97. 28  336004 "M" 1995 2009
 98. 28  336006 "M" 1983 1990
 99. 28  336006 "M" 1996 2015
100. 28  336025 "F" 2005 2013
101. end

.
. summ YearStart, meanonly

. local low = r(min)

. summ YearEnd, meanonly

. local high = r(max)

.
. forvalues y = `low'/`high' {
  2.     frame put CompanyID DirectorID Gender if inrange(`y', YearStart, YearEnd), into(working)
  3.     frame working {
  4.         by CompanyID Gender (DirectorID), sort: gen n_managers_`y' = sum(DirectorID != DirectorID[_n-1])
  5.         by CompanyID Gender (DirectorID): keep if _n == _N
  6.     }
  7.     frlink m:1 CompanyID Gender, frame(working)
  8.     frget n_managers_`y', from(working)
  9.     drop working
 10.     frame drop working
 11.     foreach x in M F {
 12.         by CompanyID, sort: egen n_managers_`x'_`y' = max(cond(Gender == "`x'", ///
>             n_managers_`y', .))
 13.     }
 14.     mvencode n_managers_*_`y', mv(0)
 15.     replace n_managers_`y' = n_managers_M_`y' + n_managers_F_`y'
 16.     order n_managers_M_`y' n_managers_F_`y', after(n_managers_`y')
 17. }
(0 observations deleted)
(71 observations in frame default unmatched)
(71 missing values generated)
(1 variable copied from linked frame)
(69 missing values generated)
(100 missing values generated)
n_man~M_1969: 69 missing values recoded
n_man~F_1969: 100 missing values recoded
(71 real changes made)
(0 observations deleted)
(71 observations in frame default unmatched)
(71 missing values generated)
(1 variable copied from linked frame)
(69 missing values generated)
(100 missing values generated)
n_man~M_1970: 69 missing values recoded
n_man~F_1970: 100 missing values recoded
(71 real changes made)
(0 observations deleted)
... [output similar to above redacted]
...

. keep CompanyID n_managers*

. by CompanyID, sort: keep if _n == 1
(94 observations deleted)

. reshape long n_managers_ n_managers_M_ n_managers_F_, i(CompanyID) j(Year)
(j = 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 19
> 96 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 20
> 24)

Data                               Wide   ->   Long
-----------------------------------------------------------------------------
Number of observations                6   ->   336        
Number of variables                 169   ->   5          
j variable (56 values)                    ->   Year
xij variables:
n_managers_1969 n_managers_1970 ... n_managers_2024->n_managers_
n_managers_M_1969 n_managers_M_1970 ... n_managers_M_2024->n_managers_M_
n_managers_F_1969 n_managers_F_1970 ... n_managers_F_2024->n_managers_F_
-----------------------------------------------------------------------------

. rename *_ *

. drop if n_managers == 0
(148 observations deleted)

.
. list, noobs clean

    Compan~D   Year   n_mana~s   n_mana~M   n_mana~F  
           3   1995          2          2          0  
           3   1996          4          4          0  
           3   1997          5          5          0  
           3   1998          5          5          0  
           3   1999          4          4          0  
           3   2000          6          6          0  
           3   2001          6          6          0  
           3   2002          7          7          0  
           3   2003          9          9          0  
           3   2004          8          8          0  
           3   2005          8          8          0  
           3   2006          7          7          0  
           3   2007          7          7          0  
           6   1976          2          2          0  
           6   1977          2          2          0  
           6   1978          2          2          0  
           6   1979          2          2          0  
           6   1980          2          2          0  
           6   1981          2          2          0  
           6   1982          2          2          0  
           6   1983          2          2          0  
           6   1984          2          2          0  
           6   1985          2          2          0  
           6   1986          2          2          0  
           6   1987          2          2          0  
           6   1988          2          2          0  
           6   1989          2          2          0  
           6   1990          2          2          0  
           6   1991          2          2          0  
           6   1992          2          2          0  
           6   1993          3          3          0  
           6   1994          3          3          0  
           6   1995          4          4          0  
           6   1996          4          4          0  
           6   1997          4          4          0  
           6   1998          4          4          0  
           6   1999          7          7          0  
           6   2000          7          7          0  
           6   2001          8          8          0  
           6   2002         10          9          1  
           6   2003          9          8          1  
           6   2004          8          7          1  
           6   2005          9          8          1  
           6   2006          9          8          1  
           6   2007         10          8          2  
           6   2008          8          7          1  
           6   2009          9          8          1  
           6   2010          9          8          1  
           6   2011          8          8          0  
           6   2012         10          9          1  
           6   2013          9          8          1  
           6   2014          9          8          1  
           6   2015          9          8          1  
           6   2016         10          8          2  
           6   2017         10          7          3  
           6   2018         10          7          3  
           6   2019         11          8          3  
           6   2020         12          8          4  
           6   2021         12          7          5  
           6   2022         11          7          4  
           6   2023         11          7          4  
           6   2024         11          7          4  
          12   1969          1          1          0  
          12   1970          1          1          0  
          12   1971          1          1          0  
          12   1972          1          1          0  
          12   1973          1          1          0  
          12   1974          1          1          0  
          12   1975          1          1          0  
          12   1976          1          1          0  
          12   1977          1          1          0  
          12   1978          1          1          0  
          12   1979          1          1          0  
          12   1980          1          1          0  
          12   1981          1          1          0  
          12   1982          1          1          0  
          12   1983          1          1          0  
          12   1984          1          1          0  
          12   1985          3          3          0  
          12   1986          4          4          0  
          12   1987          4          4          0  
          12   1988          5          5          0  
          12   1989          5          5          0  
          12   1990          6          6          0  
          12   1991          6          6          0  
          12   1992          6          6          0  
          12   1993          7          6          1  
          12   1994          7          6          1  
          12   1995          8          7          1  
          12   1996          9          8          1  
          12   1997          8          7          1  
          12   1998          8          7          1  
          12   1999          8          7          1  
          12   2000          8          7          1  
          12   2001          8          7          1  
          12   2002          8          7          1  
          12   2003         15         13          2  
          12   2004         11         10          1  
          12   2005         10          9          1  
          12   2006         11         10          1  
          12   2007         13         12          1  
          12   2008         10         10          0  
          12   2009         12         12          0  
          12   2010         11         11          0  
          12   2011          9          9          0  
          12   2012          8          8          0  
          12   2013         10         10          0  
          12   2014          5          5          0  
          22   2000          1          1          0  
          22   2001          3          3          0  
          22   2002          3          3          0  
          22   2003          3          3          0  
          22   2004          3          3          0  
          22   2005          3          3          0  
          22   2006          3          3          0  
          22   2007          3          3          0  
          22   2008          3          3          0  
          22   2009          3          3          0  
          22   2010          3          3          0  
          22   2011          3          3          0  
          22   2012          3          2          1  
          22   2013          2          1          1  
          22   2014          2          1          1  
          22   2015          2          1          1  
          22   2016          2          1          1  
          22   2017          2          1          1  
          22   2018          1          1          0  
          22   2019          1          1          0  
          22   2020          1          1          0  
          22   2021          1          1          0  
          22   2022          1          1          0  
          22   2023          1          1          0  
          22   2024          1          1          0  
          26   1995          3          3          0  
          26   1996          3          3          0  
          26   1997          4          4          0  
          26   1998          4          4          0  
          26   1999          4          4          0  
          26   2000          5          5          0  
          26   2001          5          5          0  
          26   2002          5          5          0  
          26   2003          5          5          0  
          26   2004          5          5          0  
          26   2005          5          5          0  
          26   2006          5          5          0  
          26   2007          5          5          0  
          28   1983          4          4          0  
          28   1984          4          4          0  
          28   1985          4          4          0  
          28   1986          5          5          0  
          28   1987          5          5          0  
          28   1988          6          6          0  
          28   1989          6          6          0  
          28   1990          6          6          0  
          28   1991          5          5          0  
          28   1992          5          5          0  
          28   1993          5          5          0  
          28   1994          5          5          0  
          28   1995          7          7          0  
          28   1996          9          9          0  
          28   1997         10          9          1  
          28   1998         10          9          1  
          28   1999         10          9          1  
          28   2000         15         13          2  
          28   2001         17         15          2  
          28   2002         17         15          2  
          28   2003         18         16          2  
          28   2004         18         16          2  
          28   2005         19         16          3  
          28   2006         17         14          3  
          28   2007         15         12          3  
          28   2008         13         10          3  
          28   2009         13         10          3  
          28   2010         13         10          3  
          28   2011         13         10          3  
          28   2012         13         10          3  
          28   2013         12         10          2  
          28   2014          9          8          1  
          28   2015          8          7          1  
          28   2016          4          3          1  
          28   2017          4          3          1  
          28   2018          4          3          1  
          28   2019          4          3          1  
          28   2020          4          3          1  
          28   2021          4          3          1  
          28   2022          3          2          1  
          28   2023          2          1          1  
          28   2024          2          1          1

Here's my best guess as to what is going wrong when you run the code. This code was meant to be used by copy/pasting it into a do-file and then running it from there. I'm guessing that you are trying to run this by typing it line by line into the command window.

Notice in your output that the code breaks with an error message:

parentheses unbalanced
r(132);

So the loop never even completes its first iteration, let alone looping through all the years. That occurs after the message "(1 variable copied from linked frame)." So the problem is arising somewhere in the code after the -frget- command. The next command after -frget- that includes parentheses is

        by CompanyID, sort: egen n_managers_`x'_`y' = max(cond(Gender == "`x'", ///
            n_managers_`y', .))

Now, this command cannot work if entered in the Command window, because /// is not allowed there. So Stata thinks the command is just

        by CompanyID, sort: egen n_managers_`x'_`y' = max(cond(Gender == "`x'",

which is wrong in many ways, among them not having balanced parentheses.

The solution is very simple. Copy/paste the code into the do-editor and then run it from there. You will get good results, just as I did.

More generally, when people offer you code here on Statalist, it is usually developed to run from the do-editor, not from the Command window. And since the do-editor can run any legal Stata code, whereas the Command window is slightly restricted, it is best to always run code from Statalist in the do-editor.

Added: Another general point: Stata gave you an error message. You should never ignore Stata error messages. You should not proceed with the rest of the code after getting an error message. An error message means that something is wrong with the code, the data, or both. It implies that whatever results have been generated up to that point may be incorrect, and that moving on is almost certainly going to give you incorrect results, if it gives you anything at all. Whenever you get an error message, you should stop, investigate why you are getting the error message, fix the problem(s), and then start over again.

[Fimi Karimi]

Thanks Clyde, your command perfectly worked. Your guess was correct.

[Fimi Karimi]

Clyde, sorry for my lots of questions.
When I sent my data with dataex I just include part of my data which was needed for calculation of n_manager and n_female. So when I run your command, it reshape my data and remove all other variables.
for example key identifier for my data to merge with other data set is "CompDirec3" and I want "DirectorID" tto keep in my data and the number of managers and female manager repeat infront of them:
for example:

Compdirec3 CompanyID DirectorID n_manager n_female manager ..........
3-341168 3 341168 10 2
3-340791 3 340791 10 2
3-547192 3 547192 10 2
3-340769 3 340769 10 2
3-37576-3 3 37576-3 10 2
6-37576-2 6 37576 12 4
6-2022 6 2022 12 4
6-4123 6 4123 12 4

when I added other variables name to reshape command, they are not found. Could you please tell me how I can keep them?

dataex CompDirec3 CompanyID DirectorID GVKey Nationality YearStartRole StartPerc YearEndRole EndPerc Chair CEO

----------------------- copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. For more info, type help dataex
clear
input str17 CompDirec3 long(CompanyID DirectorID GVKey) str17 Nationality int YearStartRole double StartPerc int YearEndRole double EndPerc str5 Chair str3 CEO
"3-341153" 3 341153 66425 "American" 2000 .8356164383561644 2005 .5835616438356165 "" ""
"3-341168" 3 341168 66425 "American" 2003 .9205479452054794 2004 .15342465753424658 "" ""
"3-340791" 3 340791 66425 "American" 2002 .08493150684931507 2007 .6821917808219178 "" ""
"3-547192" 3 547192 66425 "" 1996 .915068493150685 1998 .8328767123287671 "" ""
"3-340769" 3 340769 66425 "American" 1995 .915068493150685 2007 .6821917808219178 "" ""
"3-341583" 3 341583 66425 "American" 1997 .16712328767123288 2003 .021917808219178082 "" ""
"3-59796" 3 59796 66425 "American" 2005 .4301369863013699 2007 .6821917808219178 "" ""
"3-505279" 3 505279 66425 "" 2006 .18082191780821918 2007 .6821917808219178 "" ""
"3-340842" 3 340842 66425 "American" 2003 .4849315068493151 2007 .6821917808219178 "" ""
"3-341132" 3 341132 66425 "American" 2000 .41643835616438357 2007 .6821917808219178 "" ""
"3-340825" 3 340825 66425 "American" 1995 .915068493150685 2007 .6821917808219178 "" ""
"3-341118" 3 341118 66425 "American" 1996 .915068493150685 2005 .5835616438356165 "" ""
"6-457013" 6 457013 122519 "American" 2012 .9698630136986301 2021 .9315068493150684 "" ""
"6-36503" 6 36503 122519 "American" 2005 .6410958904109589 2007 .20273972602739726 "" ""
"6-37576-1" 6 37576 122519 "American" 2007 .8958904109589041 2008 .7068493150684931 "" ""
"6-37576-3" 6 37576 122519 "American" 2010 .6931506849315069 2010 .9178082191780822 "" ""
"6-341863-2" 6 341863 122519 "American" 2006 .08493150684931507 2016 .9506849315068493 "" ""
"6-533773" 6 533773 122519 "" 2016 .5041095890410959 2024 1 "" ""
"6-87882" 6 87882 122519 "" 2002 .25205479452054796 2013 .947945205479452 "" ""
"6-33553" 6 33553 122519 "American" 1995 .915068493150685 2012 .030136986301369864 "" ""
"6-2408676" 6 2408676 122519 "" 2020 .06027397260273973 2024 1 "" ""
"6-879271" 6 879271 122519 "" 2009 .7890410958904109 2024 1 "" ""
"6-1299622" 6 1299622 122519 "" 2001 1 2002 .4794520547945205 "" ""
"6-1057384" 6 1057384 122519 "" 2019 .8986301369863013 2024 1 "" ""
"6-86917" 6 86917 122519 "American" 1976 .7506849315068493 2024 1 "" ""
"6-341863-1" 6 341863 122519 "American" 1999 .4191780821917808 2003 .9178082191780822 "" ""
"6-1391443" 6 1391443 122519 "" 2017 .9123287671232877 2024 1 "" ""
"6-327069" 6 327069 122519 "American" 1976 1 2024 1 "" ""
"6-1112878" 6 1112878 122519 "" 2012 .9698630136986301 2024 1 "" ""
"6-747738" 6 747738 122519 "" 2021 .6958904109589041 2024 1 "" ""
"6-141620" 6 141620 122519 "American" 1999 .5041095890410959 2006 .9315068493150684 "" ""
"6-341598" 6 341598 122519 "American" 2002 .6712328767123288 2007 .10136986301369863 "" ""
"6-1373247" 6 1373247 122519 "" 1999 .5041095890410959 2001 .9726027397260274 "" ""
"6-1366174" 6 1366174 122519 "" 2014 .958904109589041 2020 .9397260273972603 "" ""
"6-340891" 6 340891 122519 "" 1993 .08493150684931507 2005 .9178082191780822 "" ""
"6-370490" 6 370490 122519 "American" 2007 .5150684931506849 2024 1 "" ""
"6-37576-2" 6 37576 122519 "American" 2008 .29315068493150687 2010 .30684931506849317 "" ""
"6-341596" 6 341596 122519 "American" 2002 .25205479452054796 2024 1 "" ""
"12-374892" 12 374892 11819 "" 2003 .8383561643835616 2007 .7205479452054795 "" ""
"12-201375" 12 201375 11819 "American" 1988 1 1996 0 "" ""
"12-320417" 12 320417 11819 "American" 1969 1 2009 .2876712328767123 "" ""
"12-374887" 12 374887 11819 "" 2003 .25205479452054796 2004 .41643835616438357 "" ""
"12-371960" 12 371960 11819 "" 2007 .27945205479452057 2014 .5698630136986301 "" ""
"12-371952" 12 371952 11819 "American" 2008 .2136986301369863 2010 .4 "" ""
"12-374408" 12 374408 11819 "" 1985 .16712328767123288 2003 .3232876712328767 "" ""
"12-324072" 12 324072 11819 "" 2003 .25205479452054796 2006 .7397260273972602 "" ""
"12-40911" 12 40911 11819 "" 2011 .6219178082191781 2013 .38904109589041097 "" ""
"12-551393" 12 551393 11819 "" 2007 .27945205479452057 2010 .13972602739726028 "" ""
"12-374273" 12 374273 11819 "" 1995 1 2003 .3232876712328767 "" ""
"12-374225" 12 374225 11819 "" 1990 1 2006 .7397260273972602 "" ""
"12-374182" 12 374182 11819 "" 2003 .25205479452054796 2006 .3698630136986301 "" ""
"12-374216" 12 374216 11819 "American" 2003 .6767123287671233 2007 .736986301369863 "" ""
"12-310234" 12 310234 11819 "American" 2010 .6 2013 .38904109589041097 "" ""
"12-140488" 12 140488 11819 "American" 1993 1 2003 .1917808219178082 "" ""
"12-374449" 12 374449 11819 "" 1986 1 2003 .2054794520547945 "" ""
"12-555070" 12 555070 11819 "" 2013 .6109589041095891 2014 .5698630136986301 "" ""
"12-8066" 12 8066 11819 "Canadian" 2009 .5013698630136987 2014 .5698630136986301 "" ""
"12-374241" 12 374241 11819 "" 2003 .25205479452054796 2007 .9232876712328767 "" ""
"12-341468" 12 341468 11819 "American" 2007 .27945205479452057 2010 .4 "" ""
"12-140120" 12 140120 11819 "American" 2007 .27945205479452057 2014 .5698630136986301 "" ""
"12-1264202" 12 1264202 11819 "American" 2013 .6109589041095891 2014 .5698630136986301 "" ""
"12-36354" 12 36354 11819 "American" 2009 .5013698630136987 2013 .38904109589041097 "" ""
"12-32412" 12 32412 11819 "" 2009 .7123287671232876 2013 .38904109589041097 "" ""
"12-552193" 12 552193 11819 "" 2007 .26301369863013696 2013 .38904109589041097 "" ""
"12-593295" 12 593295 11819 "" 2007 .07671232876712329 2008 .7863013698630137 "" ""
"12-493920" 12 493920 11819 "" 2006 .5068493150684932 2011 .3315068493150685 "" ""
"12-374153" 12 374153 11819 "" 1985 .16712328767123288 2007 .7205479452054795 "" ""
"12-374276" 12 374276 11819 "" 2003 .6767123287671233 2009 .4547945205479452 "" ""
"12-341979" 12 341979 11819 "American" 1996 .589041095890411 2006 .7397260273972602 "" ""
"22-746970" 22 746970 62322 "" 2001 .7534246575342466 2024 1 "" ""
"22-746973" 22 746973 62322 "" 2000 .915068493150685 2011 .08767123287671233 "" ""
"22-1146025" 22 1146025 62322 "" 2012 .9315068493150684 2017 0 "" ""
"22-746984" 22 746984 62322 "" 2001 .7534246575342466 2012 .021917808219178082 "" ""
"26-338835" 26 338835 64433 "American" 2000 .052054794520547946 2007 .5643835616438356 "" ""
"26-481237" 26 481237 64433 "" 1995 .8383561643835616 2001 .7479452054794521 "" ""
"26-373894" 26 373894 64433 "American" 1995 .8383561643835616 2007 .5643835616438356 "" ""
"26-373897" 26 373897 64433 "American" 1997 .8383561643835616 2007 .5643835616438356 "" ""
"26-373892" 26 373892 64433 "American" 1995 .8383561643835616 2007 .5643835616438356 "" ""
"26-373899" 26 373899 64433 "American" 2002 .7506849315068493 2007 .5643835616438356 "" ""
"28-509294" 28 509294 16777 "" 2021 .2136986301369863 2024 1 "" ""
"28-335986" 28 335986 16777 "" 1983 1 2005 .33972602739726027 "" ""
"28-335975" 28 335975 16777 "American" 2003 .6904109589041096 2013 .3506849315068493 "" ""
"28-102642" 28 102642 16777 "American" 2010 .7780821917808219 2022 .33972602739726027 "" ""
"28-846396-2" 28 846396 16777 "" 2020 .2493150684931507 2022 0 "" ""
"28-846396-1" 28 846396 16777 "" 2017 .25205479452054796 2020 .7506849315068493 "" ""
"28-1540889" 28 1540889 16777 "" 2015 .6547945205479452 2024 1 "" ""
"28-336027" 28 336027 16777 "American" 2005 .6602739726027397 2024 1 "" ""
"28-846396-3" 28 846396 16777 "" 2022 1 2024 1 "" ""
"28-1075984" 28 1075984 16777 "" 2010 .7780821917808219 2013 .3506849315068493 "" ""
"28-335993" 28 335993 16777 "American" 2000 .2958904109589041 2021 .3424657534246575 "" ""
"28-335990" 28 335990 16777 "American" 1996 1 2015 .3452054794520548 "" ""
"28-2501291" 28 2501291 16777 "" 2021 .4904109589041096 2024 1 "" ""
"28-336002" 28 336002 16777 "American" 2000 .2958904109589041 2024 1 "" ""
"28-500955" 28 500955 16777 "" 2006 .6657534246575343 2013 .3506849315068493 "" ""
"28-335970" 28 335970 16777 "American" 1983 .06575342465753424 2015 .3452054794520548 "" ""
"28-336025-1" 28 336025 16777 "American" 2005 .6602739726027397 2013 .09863013698630137 "" ""
"28-14242" 28 14242 16777 "American" 1988 1 2014 .34794520547945207 "" ""
"28-335980" 28 335980 16777 "American" 2000 .2958904109589041 2007 .3315068493150685 "" ""
"28-1087055" 28 1087055 16777 "" 2021 .2136986301369863 2024 1 "" ""
"28-336004" 28 336004 16777 "American" 1995 1 2009 .3452054794520548 "" ""
end

[Clyde Schechter]

Before running the code given before, run:

tempfile holding
save `holding'

Then use the same code as before )from #5). And follow it with:

merge 1:m CompanyID Year using `holding'

Important: All of the code from -tempfile holding- down to the final -merge 1:m CompanyID Year using `holding'- must be run without interruption, from your do-file. It will not work if you attempt to run it one line at a time or in chunks.

Note: The example data you show in #9 is not suitable because it does not contain a Gender variable.

[Fimi Karimi]

Thank you so much. I run it from do file and it gave me an error which "Year ambiguous abbreviation"


reshape long n_managers_ n_managers_M_ n_managers_F_, i(CompanyID) j(Year)
(j = 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1
> 965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995
> 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024)

Data Wide -> Long
-----------------------------------------------------------------------------
Number of observations 13,670 -> 1,230,300
Number of variables 271 -> 5
j variable (90 values) -> Year
xij variables:
n_managers_1935 n_managers_1936 ... n_managers_2024->n_managers_
n_managers_M_1935 n_managers_M_1936 ... n_managers_M_2024->n_managers_M_
n_managers_F_1935 n_managers_F_1936 ... n_managers_F_2024->n_managers_F_
-----------------------------------------------------------------------------

. rename *_ *

. merge 1:m CompanyID Year using `holding'
Year ambiguous abbreviation
r(111);
end of do-file


And all other variables got removed:
input long CompanyID int Year float(n_managers n_managers_M n_managers_F)
3 1935 0 0 0
3 1936 0 0 0
3 1937 0 0 0
3 1938 0 0 0
3 1939 0 0 0
3 1940 0 0 0
3 1941 0 0 0
3 1942 0 0 0
3 1943 0 0 0
3 1944 0 0 0
3 1945 0 0 0
3 1946 0 0 0
3 1947 0 0 0
3 1948 0 0 0
3 1949 0 0 0
3 1950 0 0 0
3 1951 0 0 0
3 1952 0 0 0
3 1953 0 0 0

[Fimi Karimi]

I run this command:
tempfile holding
save `holding'
summ YearStart, meanonly
local low = r(min)
summ YearEnd, meanonly
local high = r(max)

forvalues y = `low'/`high' {
frame put CompanyID DirectorID Gender if inrange(`y', YearStart, YearEnd), into(working)
frame working {
by CompanyID Gender (DirectorID), sort: gen n_managers_`y' = sum(DirectorID != DirectorID[_n-1])
by CompanyID Gender (DirectorID): keep if _n == _N
}
frlink m:1 CompanyID Gender, frame(working)
frget n_managers_`y', from(working)
drop working
frame drop working
foreach x in M F {
by CompanyID, sort: egen n_managers_`x'_`y' = max(cond(Gender == "`x'", ///
n_managers_`y', .))
}
mvencode n_managers_*_`y', mv(0)
replace n_managers_`y' = n_managers_M_`y' + n_managers_F_`y'
order n_managers_M_`y' n_managers_F_`y', after(n_managers_`y')
}
keep CompanyID n_managers*
by CompanyID, sort: keep if _n == 1
reshape long n_managers_ n_managers_M_ n_managers_F_, i(CompanyID) j(Year)
rename *_ *
merge 1:m CompanyID Year using `holding'

[Clyde Schechter]

My mistake, sorry.

* Example generated by -dataex-. For more info, type help dataex
clear
input long(CompanyID DirectorID) str1 Gender int(YearStart YearEnd)
 3   59796 "M" 2005 2007
 3  340769 "M" 1995 2007
 3  340791 "M" 2002 2007
 3  340825 "M" 1995 2007
 3  340842 "M" 2003 2007
 3  341118 "M" 1996 2005
 3  341132 "M" 2000 2007
 3  341153 "M" 2000 2005
 3  341168 "M" 2003 2004
 3  341583 "M" 1997 2003
 3  505279 "M" 2006 2007
 3  547192 "M" 1996 1998
 6   33553 "M" 1995 2012
 6   36503 "M" 2005 2007
 6   37576 "F" 2007 2008
 6   37576 "F" 2008 2010
 6   37576 "F" 2010 2010
 6   86917 "M" 1976 2024
 6   87882 "M" 2002 2013
 6  141620 "M" 1999 2006
 6  327069 "M" 1976 2024
 6  340891 "M" 1993 2005
 6  341596 "M" 2002 2024
 6  341598 "F" 2002 2007
 6  341863 "M" 1999 2003
 6  341863 "M" 2006 2016
 6  370490 "M" 2007 2024
 6  457013 "F" 2012 2021
 6  533773 "F" 2016 2024
 6  747738 "F" 2021 2024
 6  879271 "M" 2009 2024
 6 1057384 "M" 2019 2024
 6 1112878 "M" 2012 2024
 6 1299622 "M" 2001 2002
 6 1366174 "M" 2014 2020
 6 1373247 "M" 1999 2001
 6 1391443 "F" 2017 2024
 6 2408676 "F" 2020 2024
12    8066 "M" 2009 2014
12   32412 "M" 2009 2013
12   36354 "M" 2009 2013
12   40911 "M" 2011 2013
12  140120 "M" 2007 2014
12  140488 "F" 1993 2003
12  201375 "M" 1988 1996
12  310234 "M" 2010 2013
12  320417 "M" 1969 2009
12  324072 "M" 2003 2006
12  341468 "M" 2007 2010
12  341979 "M" 1996 2006
12  371952 "M" 2008 2010
12  371960 "M" 2007 2014
12  374153 "M" 1985 2007
12  374182 "M" 2003 2006
12  374216 "M" 2003 2007
12  374225 "M" 1990 2006
12  374241 "M" 2003 2007
12  374273 "M" 1995 2003
12  374276 "M" 2003 2009
12  374408 "M" 1985 2003
12  374449 "M" 1986 2003
12  374887 "M" 2003 2004
12  374892 "F" 2003 2007
12  493920 "M" 2006 2011
12  551393 "M" 2007 2010
12  552193 "M" 2007 2013
12  555070 "M" 2013 2014
12  593295 "M" 2007 2008
12 1264202 "M" 2013 2014
22  746970 "M" 2001 2024
22  746973 "M" 2000 2011
22  746984 "M" 2001 2012
22 1146025 "F" 2012 2017
26  338835 "M" 2000 2007
26  373892 "M" 1995 2007
26  373894 "M" 1995 2007
26  373897 "M" 1997 2007
26  373899 "M" 2002 2007
26  481237 "M" 1995 2001
28   14242 "M" 1988 2014
28  102642 "M" 2010 2022
28  220968 "M" 2001 2024
28  335966 "M" 1995 2006
28  335970 "M" 1983 2015
28  335975 "M" 2003 2013
28  335980 "M" 2000 2007
28  335983 "M" 2000 2013
28  335986 "M" 1983 2005
28  335987 "F" 1997 2012
28  335989 "M" 2001 2015
28  335990 "M" 1996 2015
28  335993 "M" 2000 2021
28  335994 "M" 2000 2006
28  335996 "M" 1986 2007
28  335999 "M" 1983 2005
28  336002 "F" 2000 2024
28  336004 "M" 1995 2009
28  336006 "M" 1983 1990
28  336006 "M" 1996 2015
28  336025 "F" 2005 2013
end

preserve

summ YearStart, meanonly
local low = r(min)
summ YearEnd, meanonly
local high = r(max)

forvalues y = `low'/`high' {
    frame put CompanyID DirectorID Gender if inrange(`y', YearStart, YearEnd), into(working)
    frame working {
        by CompanyID Gender (DirectorID), sort: gen n_managers_`y' = sum(DirectorID != DirectorID[_n-1])
        by CompanyID Gender (DirectorID): keep if _n == _N
    }
    frlink m:1 CompanyID Gender, frame(working)
    frget n_managers_`y', from(working)
    drop working
    frame drop working
    foreach x in M F {
        by CompanyID, sort: egen n_managers_`x'_`y' = max(cond(Gender == "`x'", ///
            n_managers_`y', .))
    }
    mvencode n_managers_*_`y', mv(0)
    replace n_managers_`y' = n_managers_M_`y' + n_managers_F_`y'
    order n_managers_M_`y' n_managers_F_`y', after(n_managers_`y')
}
keep CompanyID n_managers*
by CompanyID, sort: keep if _n == 1
reshape long n_managers_ n_managers_M_ n_managers_F_, i(CompanyID) j(Year)
rename *_ *
tempfile results
save `results'

restore
rangejoin Year YearStart YearEnd using `results', by(CompanyID)

-rangejoin- is written by Robert Picard and is available from SSC. To use -rangejoin- you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

[Fimi Karimi]

Thank you so much for your help.

[Fimi Karimi]

Hi Clyde, Sorry for bothering you again.
I had to include some other variables in my data, and as one of them is Year it interfere with your command. Could I ask you to have a look at my data again:

I need to calculate the number of managers of the each company for each year, the number of female and male managers , and the percentage of their presence in company for each year( for example if someone has been in the company from 1/May/2000 till 1/May/2002 , the percentage for 2000 and 2002 is 67% and for 2001 is 100%. If someone enter the company in one year and left the company at the same year and then again enter that company at that year, I need to calculate the total percentage of his/her presentce (I mean sum of both percentage)
For gender 0 is male and 1 is female.
If someone start the membership in one year(say 2000) and left the company at the same year (2000) and then again enter that company at that year again(2000), I need to count him just once in the 2000 for numbers of members at that company.


clear
input long(CompanyID MnagerID) int(Year DateStartRole YearStartRole) double StartYearPercentage int(DateEndRole YearEndRole) double EndYearPercentage long Gender
14714 36350 1950 17481 2007 .13972602739726028 17584 2008 .14246575342465753 0
24404 274872 1950 20212 2015 .663013698630137 22715 2022 .18904109589041096 0
3268333 647023 1950 22175 2020 .2876712328767123 23142 2023 .3589041095890411 0
77289 140814 1950 17106 2006 .16712328767123288 18596 2010 .9123287671232877 0
2957 341389 1950 15857 2003 .5863013698630137 16192 2004 .3315068493150685 0
1216462 731518 1950 16751 2005 .13972602739726028 17938 2009 .1095890410958904 0
19249 27228 1950 13788 1997 .25205479452054796 14792 2000 .4986301369863014 0
2215970 602625 1950 20089 2015 1 23376 2024 1 0
2684926 766264 1950 21027 2017 .4328767123287671 21896 2019 .947945205479452 0
8542 1269387 1950 20676 2016 .3917808219178082 22022 2020 .29315068493150687 0
3471954 2680690 1950 23001 2022 .0273972602739726 23376 2024 1 0
734725 371154 1950 16497 2005 .8356164383561644 23376 2024 1 0
31761 1013779 1950 19724 2014 1 23376 2024 1 0
3230 310464 1950 16482 2005 .8767123287671232 23376 2024 1 1
31338 320152 1950 16657 2005 .3972602739726027 19484 2013 .3424657534246575 0
810610 2142863 1950 21670 2019 .6712328767123288 23376 2024 1 1
23104 535473 1950 14441 1999 .46301369863013697 14976 2001 0 0
1949535 33059 1950 19480 2013 .6684931506849315 23010 2022 .9972602739726028 0
1020882 1055600 1950 18435 2010 .5287671232876713 23376 2024 1 0
3061398 1601311 1950 21755 2019 .4383561643835616 22280 2020 1 0
2066595 345640 1950 19851 2014 .6520547945205479 21497 2018 .8547945205479452 0
15053 57816 1950 13393 1996 .3315068493150685 15854 2003 .4054794520547945 0
4913 180576 1950 13970 1998 .7534246575342466 16719 2005 .7726027397260274 0
1635432 34517 1950 18345 2010 .7753424657534247 22791 2022 .3972602739726027 0
603630 373308 1950 16284 2004 .41643835616438357 17960 2009 .16986301369863013 0
6357 41901 1950 15116 2001 .6164383561643836 18725 2011 .26575342465753427 0
2776429 220567 1950 21228 2018 .5643835616438356 21434 2018 .5643835616438356 0
570234 1538188 1950 20227 2015 .6219178082191781 23376 2024 1 0
27970 202652 1950 21208 2018 .936986301369863 23376 2024 1 0
32315 595234 1950 15706 2003 1 19191 2012 .5424657534246575 0
3454 49346 1950 10227 1988 1 14335 1999 .2465753424657534 0
2402253 337217 1950 21270 2018 .10136986301369863 21307 2018 .10136986301369863 0
23165 104112 1950 13844 1997 .09863013698630137 16433 2004 .9917808219178083 0
1967739 1017642 1950 20963 2017 .6082191780821918 23376 2024 1 0
25680 320153 1950 15620 2002 .2356164383561644 19170 2012 .4849315068493151 0
4873 64197 1950 15669 2002 .10136986301369863 17166 2006 .9972602739726028 0
31132 546904 1950 18994 2012 .9972602739726028 22370 2021 .24383561643835616 1
19601 2135197 1950 21768 2019 .40273972602739727 23376 2024 1 0
790219 205102 1950 16588 2005 .5863013698630137 17245 2007 .2136986301369863 0
127647 64197 1950 15805 2003 .7287671232876712 18828 2011 .547945205479452 0
28170 1535236 1950 20207 2015 .6767123287671233 23376 2024 1 0
1025143 64197 1950 20485 2016 .915068493150685 21677 2019 .34794520547945207 0
1984187 1948857 1950 21867 2019 .13150684931506848 22400 2021 .32602739726027397 1
1682362 1457678 1950 21537 2018 .03561643835616438 23376 2024 1 0
167 1818027 1950 12054 1993 1 23376 2024 1 1
1212957 1124709 1950 18808 2011 .5068493150684932 19026 2012 .09041095890410959 0
7518 59621 1950 11413 1991 .7534246575342466 17416 2007 .6821917808219178 0
1230126 1520380 1950 18049 2009 .5863013698630137 23376 2024 1 0
9281 274510 1950 14579 1999 .08493150684931507 14955 2000 .9452054794520548 1
26166 31196 1950 17986 2009 .7589041095890411 18416 2010 .4191780821917808 1
25615 331918 1950 12667 1994 .32054794520547947 15973 2003 .7315068493150685 0
494219 34164 1950 19332 2012 .07123287671232877 22420 2021 .38082191780821917 0
26718 332820 1950 13057 1995 .25205479452054796 17304 2007 .37534246575342467 0
125402 203099 1950 15580 2002 .3452054794520548 17218 2007 .13972602739726028 0
13136 760730 1950 12874 1995 .7534246575342466 20822 2017 .005479452054794521 0
1206647 750450 1950 11688 1992 1 17917 2009 .052054794520547946 0
1937998 762770 1950 20586 2016 .6383561643835617 23376 2024 1 0
1146485 274693 1950 15706 2003 1 19408 2013 .13424657534246576 0
1226156 444129 1950 15706 2003 1 17481 2007 .8602739726027397 0
25597 332813 1950 14792 2000 .5013698630136987 20096 2015 .019178082191780823 1
7588 484097 1950 20866 2017 .873972602739726 22462 2021 .4958904109589041 0
1584110 334721 1950 18172 2009 .2493150684931507 20040 2014 .8657534246575342 0
25680 273657 1950 20305 2015 .40821917808219177 21529 2018 .9424657534246575 0
2671 38526 1950 16274 2004 .4438356164383562 20235 2015 .4 0
33237 91477 1950 18877 2011 .3178082191780822 19342 2012 .9561643835616438 0
753102 13204 1950 16649 2005 .4191780821917808 18794 2011 .4547945205479452 0
3061358 2505633 1950 22475 2021 .4684931506849315 23376 2024 1 1
2034213 441136 1950 19940 2014 .40821917808219177 20388 2015 .8191780821917808 0
2469 38526 1950 17402 2007 .3561643835616438 19631 2013 .7452054794520548 0
3204648 893094 1950 21384 2018 .4547945205479452 23376 2024 1 0
25578 55217 1950 15749 2003 .8821917808219178 16925 2006 .336986301369863 0
27358 1351667 1950 21761 2019 .42191780821917807 23376 2024 1 0
2366 320145 1950 16342 2004 .25753424657534246 17189 2007 .06027397260273973 0
857145 554582 1950 17056 2006 .3041095890410959 18203 2009 .8356164383561644 0
2381322 1948985 1950 21124 2017 .16712328767123288 23376 2024 1 0
9519 623177 1950 20728 2016 .2493150684931507 22701 2022 .1506849315068493 0
1601866 592608 1950 21236 2018 .8602739726027397 21732 2019 .4986301369863014 0
27411 1008473 1950 22273 2020 .019178082191780823 23376 2024 1 0
23165 63432 1950 22264 2020 .043835616438356165 23376 2024 1 0
942661 732268 1950 15839 2003 .6356164383561644 16517 2005 .2191780821917808 0
754662 1337279 1950 15497 2002 .5726027397260274 19887 2014 .4465753424657534 0
31863 84307 1950 15778 2003 .8027397260273973 21306 2018 .3315068493150685 0
11888 1126710 1950 18864 2011 .35342465753424657 21353 2018 .4602739726027397 0
2718020 1279546 1950 22120 2020 .4383561643835616 23376 2024 1 0
646483 485045 1950 16875 2006 .8 19556 2013 .5397260273972603 0
33332 450785 1950 11323 1991 1 17169 2007 .005479452054794521 0
947097 332663 1950 21216 2018 .915068493150685 23376 2024 1 0
2273 35837 1950 15157 2001 .5041095890410959 18322 2010 .16164383561643836 0
3393193 1544657 1950 22414 2021 .6356164383561644 23376 2024 1 0
26883 34015 1950 17496 2007 .09863013698630137 19666 2013 .8410958904109589 0
2165093 1255358 1950 20030 2014 .16164383561643836 21697 2019 .40273972602739727 0
17178 140785 1950 13270 1996 .6684931506849315 14245 1999 0 0
605072 731838 1950 14731 2000 .6684931506849315 15127 2001 .4136986301369863 0
21520 86932 1950 13911 1998 .915068493150685 15826 2003 .3287671232876712 0
624022 1395037 1950 20051 2014 .10410958904109589 20468 2016 .038356164383561646 0
3181143 271656 1950 22740 2022 .7424657534246575 23376 2024 1 0
2458681 324474 1950 21125 2017 .1643835616438356 23376 2024 1 0
29776 82957 1950 9132 1985 1 14774 2000 .44931506849315067 0
1210655 2648152 1950 22036 2020 .6684931506849315 23376 2024 1 0
712 332941 1950 16859 2006 .8438356164383561 18612 2010 .9561643835616438 0
end
format %tdnn/dd/CCYY DateStartRole
format %tdnn/dd/CCYY DateEndRole
label values Gender sex
label def sex 1 "F", modify



Could I ask you help me with that please.
Thanks for your time.