Dear community,

I have a dataset; the following code generates a snippet of it

Code:
clear
input float ID str6(F_1 F_2) float Year
1 "Pfizer" "Merck"  1999
2 "J&J"    "Merck"  1994
3 "J&J"    "Lilly"  1995
4 "Merck"  "Pfizer" 1997
5 "Pfizer" "Merck"  1996
6 "Pfizer" "Merck"  2000
7 "Merck"  "J&J"    1993
end
The data shows relationships between firms over years. I want to count the number of times the firms had a relationship in the past. For example, Pfizer and Merck in 1999 had a relationship. I want to know how many times Pfizer and Merck formed a relationship before 1999. The answer should be 2 for the first observation (whose ID is 1) because Merck and Pfizer had a relationship in 1997, and Pfizer and Merck had a relationship in 1996.

This is possible using SQL. But, I want to make this happen in Stata.

If Pfizer in this dataset was always an F_1 observation, I could do this using a -sort- command and then easily count the observations before 1999. The challenge is that a Pfizer-Merck relationship should be treated the same as a Merck-Pfizer relationship.

Thanks,
Navid