Dear fellow Statalisters,

For a large panel data set on alliances, I want to calculate the number of overlapping ties (or "common friends") in the five years preceding the alliance year. Although the idea sounds simple, I got stuck in calculating the ties of each firm prior to the focal alliance and checking whether these previous ties were the same as those of the partner in the focal alliance.

Here is an example that I have created manually for illustration. The variable "common_friends" is the variable of interest, the variables id_firm_a and id_firm_b represent unique numerical firm IDs.

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id_firm_a id_firm_b) int year byte common_friends str3 id_common str72 comment
1  2 2005 2 "3;4" "3 and 4 are common partners before the focal year (included)"            
1  3 2002 1 "4"   "2 is a common parter but after/in the focal year (excluded)"             
1  4 2001 0 ""    "2 and 3 are common partners but after/in the focal year (excluded)"      
2  3 2002 1 "4"   "5 and 6 are common partners but after/in the focal year (excluded)"      
2  4 2001 0 ""    "1,3, 5, and 6 are common partners but after/in the focal year (excluded)"
2  5 2003 2 "3;4" "3 and 4 are common partners before the focal year (included)"            
2  6 2004 2 "3;4" "3 and 4 are common partners before the focal year (included)"            
3  4 2001 2 "5;8" "6 and 7 are common partners but after/in the focal year (excluded)"      
3  5 2000 0 ""    "2 and 4 are common partners but after/in the focal year (excluded)"      
3  6 2003 0 ""    "2 and 4 are common partners but after/in the focal year (excluded)"      
3  7 2004 0 ""    "4 is a common parter but after/in the focal year (excluded)"             
3  8 2000 0 ""    "4 is a common parter but after/in the focal year (excluded)"             
4  5 2000 0 ""    "2 and 3 are common partners but after/in the focal year (excluded)"      
4  6 2005 2 "2;3" "2 and 3 are common partners before the focal year (included)"            
4  7 2005 1 "3"   "3 is a common parter before the focal year (included)"                   
4  8 2000 0 ""    "3 is a common parter but after/in the focal year (excluded)"             
4  9 2010 0 ""    "no common partners"                                                      
4 10 2012 0 ""    "no common partners"                                                      
end

Help or pointers would be greatly appreciated.

Best regards,

Marvin