Creating a firm-level dummy variable and a firm-level continuous variable from director-level dummy variable

[Christelle Alkhoury]

Hello,

I have a dummy variable (AC_member) which equals 1 if the director is also an audit committee member, and 0 otherwise (director-level variable).

I need to create the same variable but at the firm-year level (AC_member_firm). I want this dummy variable to be equal to 1 if there is at least one director in a specific year for which AC_member=1. (AC_member_firm=1 if at least one director in a specific year has AC_member=1).

Also, I need to create a continuous firm-level variable (AC_member_firm_nb) that counts the number of directors for which AC_member=1 in a specific year.

Eventually I will drop director-level variables and keep only firm-year level variables whereby I will only have 1 firm-year observation instead of multiple obseravtions as is the case now with multiple directors. But to get to that point, I will first need to create my firm-level variables through the director-level variables, and then I can delete director-level variables.

For example, for companyid 33 in 2009, there are 2 directors (directorid 119435 and 119437) who are also members. So for the year 2009, companyid 33 will have the dummy AC_member_firm=1 and the continuous variable AC_member_firm_nb=2.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year long(directorid companyid) float AC_member
2009   7398 33 0
2009  27346 33 0
2009  29393 33 0
2009  62380 33 0
2009 119435 33 1
2009 119436 33 0
2009 119437 33 1
2010  15144 33 0
2010  27346 33 0
2010  29393 33 0
2010  62380 33 0
2010 119435 33 1
2010 119436 33 0
2010 119437 33 1
2011  15144 33 0
2011  27346 33 0
2011  29393 33 0
2011  62380 33 0
2011 119435 33 1
2011 119436 33 0
2011 119437 33 1
2012  15144 33 1
2012  27346 33 0
2012  29393 33 0
2012  62380 33 0
2012 119435 33 1
2012 119436 33 0
2012 119437 33 0
2013  15144 33 1
2013  27346 33 0
2013  29393 33 0
2013  62380 33 0
2013 119435 33 1
2013 119436 33 0
2013 119437 33 0
2014  15144 33 1
2014  27346 33 0
2014  28529 33 1
2014  29393 33 0
2014  62380 33 0
2014 119436 33 0
2014 119437 33 0
2015  27346 33 0
2015  28529 33 1
2015  29393 33 0
2015  62380 33 0
2015 119436 33 0
2015 119437 33 0
2015 221576 33 0
2016  27346 33 0
2016  28529 33 1
2016  62380 33 0
2016 119436 33 1
2016 119437 33 0
2016 221576 33 0
2016 253893 33 0
2017  27346 33 0
2017  28529 33 1
2017  62380 33 0
2017 119436 33 0
2017 221576 33 0
2017 251589 33 0
2017 253893 33 1
2018  27346 33 0
2018  28529 33 1
2018  62380 33 0
2018 119436 33 0
2018 221576 33 0
2018 251589 33 1
2018 253893 33 1
2019  27346 33 0
2019  28529 33 1
2019  62380 33 0
2019 119436 33 0
2019 221576 33 0
2019 251589 33 1
2019 253893 33 1
2019 285245 33 1
2007   2092 34 0
2007   8264 34 0
2007   9099 34 1
2007  10750 34 0
2007  12780 34 0
2007  29421 34 1
2007  51994 34 1
2007  69704 34 0
2007  86014 34 1
2007  86015 34 0
2007 109814 34 0
2008   2092 34 0
2008   8264 34 0
2008   9099 34 1
2008  10750 34 1
2008  12780 34 0
2008  29421 34 1
2008  51994 34 1
2008  69704 34 0
2008  86014 34 1
2008  86015 34 0
2008 109814 34 0
end

P.S.: I am using Stata/SE 16.0

Thank you in advance for your help,
Christelle

[Christelle Alkhoury]

Please let me know whether further details are required.

Thank you!

[Andrew Musau]

See https://www.stata.com/support/faqs/d...ble-recording/

bysort comp Year: egen wanted1= max(AC_member)

Also, I need to create a continuous firm-level variable (AC_member_firm_nb) that counts the number of directors for which AC_member=1 in a specific year.

bys comp Year: egen wanted2= total(AC_member)

Eventually I will drop director-level variables and keep only firm-year level variables whereby I will only have 1 firm-year observation instead of multiple obseravtions as is the case now with multiple directors. But to get to that point, I will first need to create my firm-level variables through the director-level variables, and then I can delete director-level variables.

Directly, you can do it with collapse.

collapse (sum) AC_member, by(comp Year)

You may also include additional variables without necessarily summing them in the above command. See

help collapse

[Christelle Alkhoury]

Thank you Andrew! It worked. Just one comment, in the first code, gen resulted in "invalid syntax", so I replaced it with egen as in the second code.

[Andrew Musau]

Was a typo. egen not gen. Now edited.