Measuring firm rivals based on common ownership data

[Pantelis Kazakis]

Dear Statalist members,

Assume the following example:

input str5 (firm_id shareholder_id)
A x1
A x100
A x150
A x20
B y1
B y2
B y3
B y4
C x1
C z1
C z2
C z3
D x20
D x1000
D x1001
D x1002
end

In the above example, we have four firms (A, B, C, and D) each of which has different shareholders. From this example, we can observe that shareholder x1 and x20 appear in more than one firm. First, we see that x1 and x20 appear in firm A, while x1 appears also in firm C, and x20 in firm D. We can consider x1 and x20 as common owners, as they appear more than once in the dataset.

Let's denote the number of rivals as the number of other distinct firms a common owner appears. For example, firm A should have two rivals, because at least one of its common owners appears in two other firms (x1 in C and x20 in D). With similar logic, we can say that firm B should have zero common owners because none of its shareholders appears in any other firm; firm C should have one rival (firm A due to shareholder x1); firm D one rival (firm A due to shareholder x20).

How would one code this?
Thanks in advance.

[Ken Chui]

Not very elegant but this may do the job:

First. save a copy of it, but change the name of the company.

clear
input str5 (firm_id shareholder_id)
A x1
A x100
A x150
A x20
B y1
B y2
B y3
B y4
C x1
C z1
C z2
C z3
D x20
D x1000
D x1001
D x1002
end
save temp01, replace
rename firm_id f2
save temp02, replace

Perform a full join, by shareholder:

use temp01, clear
joinby shareholder_id using temp02

Then, identify unique company pairs, but follow with a replace to code all the identical pairs to 0, as we are not interested to count them.

Finally, perform a collapse by company id:

collapse (sum) uniq_pair, by(firm_id)
list

Results:

     +--------------------+
     | firm_id   uniq_p~r |
     |--------------------|
  1. |       A          2 |
  2. |       B          0 |
  3. |       C          1 |
  4. |       D          1 |
     +--------------------+

You can then use a 1:m merge to merge that back to the original data.

[Pantelis Kazakis]

Ken Chui

Not sure about this code. It might work on the initial example that I provided. But look here:

clear
input str5 (firm_id shareholder_id)
A    x1    
A    x2    
A    x3    
A    x4    
B    x1    
B    x2    
B    x3    
B    x4    
C    x1    
C    x2    
C    x3    
C    x4    
D    x1    
D    x2    
D    x3    
D    x4    
end

In this case, each firm should have three rivals. Your code gives 12.

Also, I would prefer that in the initial sample, only one column with the number of rivals is added and no other files stay in the disc.

[Pantelis Kazakis]

I added code to deal with duplicate cases. I think it works now.

save temp01, replace
rename firm_id f2
save temp02, replace

use temp01, clear
joinby shareholder_id using temp02

gen uniq_pair = (firm_id != f2)
drop if uniq_pair == 0

sort firm_id f2
quietly by firm_id f2:  gen dup = cond(_N==1,0,_n)
drop if dup > 1
drop dup

collapse (sum) uniq_pair, by(firm_id)

[Ken Chui]

Ken Chui

Not sure about this code. It might work on the initial example that I provided. But look here:

clear
input str5 (firm_id shareholder_id)
A x1
A x2
A x3
A x4
B x1
B x2
B x3
B x4
C x1
C x2
C x3
C x4
D x1
D x2
D x3
D x4
end

In this case, each firm should have three rivals. Your code gives 12.

Also, I would prefer that in the initial sample, only one column with the number of rivals is added and no other files stay in the disc.

Very sorry about that, I missed a code chunk in #2 after the sentence "Then, identify unique company pairs, but follow with a replace to code all the identical pairs to 0, as we are not interested to count them." There should be an egen as well. Here is the code for your data in #3:

clear
input str5 (firm_id shareholder_id)
A    x1    
A    x2    
A    x3    
A    x4    
B    x1    
B    x2    
B    x3    
B    x4    
C    x1    
C    x2    
C    x3    
C    x4    
D    x1    
D    x2    
D    x3    
D    x4    
end
save temp01, replace
rename firm_id f2
save temp02, replace

use temp01, clear
joinby shareholder_id using temp02

egen uniq_pair = tag(firm_id f2)
replace uniq_pair = 0 if firm_id == f2

collapse (sum) uniq_pair, by(firm_id)
list

Results:

     +--------------------+
     | firm_id   uniq_p~r |
     |--------------------|
  1. |       A          3 |
  2. |       B          3 |
  3. |       C          3 |
  4. |       D          3 |
     +--------------------+

Also, I would prefer that in the initial sample, only one column with the number of rivals is added and no other files stay in the disc

As I said, an 1:m merge will add that column into the first data:

merge 1:m firm_id using temp01
drop _merge

To get rid of that duplicated file, just use erase in your code at the end:

capture erase temp02