identify common ownership of shareholders

[Flora Yin]

Dear statalist,

I have a set of data with the following variables: year, symbol (symbol of each firm), industry (industry the firm belongs to), shareholder_ID, holding (how many shares the shareholder holds for a firm). A firm has multiple shareholders in a year and the data spans over 10 years.

I would like to construct 3 variables:
1. a dummy variable common_dum indicating whether a firm shares common shareholders with at least one other firm in the same industry in a year.
2. a variable common_num counting the number of common shareholders a firm shares with other firms in the same industry in a year.
3. a variable common_percent summing all percentage of common shareholds in a firm in a year.

Here is a simplified example of the data:

year    symbol    industry    shareholder_ID    holding
2009    1    A    501    2.3
2009    1    A    502    1.2
2009    1    A    511    6.8
2009    2    A    535    3.1
2009    2    A    511    2.2
2009    3    B    522    10.6
2009    3    B    548    4.6
2009    4    C    538    3.3
2009    4    C    577    1
2009    5    B    522    4.9
2009    5    B    548    15.8
2009    5    B    514    9.8

Thanks a lot for any advice!

[William Lisowski]

* Example generated by -dataex-. For more info, type help dataex
clear
input int year byte symbol str1 industry int sh_id float holding
2009 1 "A" 501  2.3
2009 1 "A" 502  1.2
2009 1 "A" 511  6.8
2009 2 "A" 535  3.1
2009 2 "A" 511  2.2
2009 3 "B" 522 10.6
2009 3 "B" 548  4.6
2009 4 "C" 538  3.3
2009 4 "C" 577    1
2009 5 "B" 522  4.9
2009 5 "B" 548 15.8
2009 5 "B" 514  9.8
end
generate original_order = _n
bysort year industry sh_id (symbol): generate multi = _N>1
bysort year symbol: egen com_dum = max(multi)
bysort year symbol: egen com_num = total(multi)
bysort year symbol: gen  com_pct = com_num/_N
sort original_order
drop original_order
list, noobs sepby(year symbol) abbreviate(20)

. list, noobs sepby(year symbol) abbreviate(20)

  +-----------------------------------------------------------------------------------+
  | year   symbol   industry   sh_id   holding   multi   com_dum   com_num    com_pct |
  |-----------------------------------------------------------------------------------|
  | 2009        1          A     501       2.3       0         1         1   .3333333 |
  | 2009        1          A     502       1.2       0         1         1   .3333333 |
  | 2009        1          A     511       6.8       1         1         1   .3333333 |
  |-----------------------------------------------------------------------------------|
  | 2009        2          A     535       3.1       0         1         1         .5 |
  | 2009        2          A     511       2.2       1         1         1         .5 |
  |-----------------------------------------------------------------------------------|
  | 2009        3          B     522      10.6       1         1         2          1 |
  | 2009        3          B     548       4.6       1         1         2          1 |
  |-----------------------------------------------------------------------------------|
  | 2009        4          C     538       3.3       0         0         0          0 |
  | 2009        4          C     577         1       0         0         0          0 |
  |-----------------------------------------------------------------------------------|
  | 2009        5          B     522       4.9       1         1         2   .6666667 |
  | 2009        5          B     548      15.8       1         1         2   .6666667 |
  | 2009        5          B     514       9.8       0         1         2   .6666667 |
  +-----------------------------------------------------------------------------------+

[Flora Yin]

Hi William,

That works perfectly! Thanks a lot!