Dear Statalist.

I am trying to generate 2 separate measures of repeat collaboration between firms co-occurring in projects over time.

The 2 measures can be described as follows:

Wanted 1: For each firm x and year t, count the total number of collaborations in year t with firms z that firm x also collaborated with on projects in year t-1.

Wanted 2: For each firm x and year t, count the total number of distinct firms z that firm x collaborates with on projects in year t that firm x also collaborated with on projects in year t-1.

Here is a dataex example dataset to illustrate the data structure and show the wanted values.

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(year firm_id project_id wanted_1 wanted_2)
1 1 1 0 0
1 2 1 0 0
1 1 2 0 0
1 3 2 0 0
1 2 3 0 0
1 3 3 0 0
1 1 4 0 0
1 3 4 0 0
1 1 5 0 0
2 1 6 3 2
2 1 7 3 2
2 3 7 2 1
2 2 8 1 1
2 4 8 0 0
2 1 9 3 2
2 2 9 1 1
end

Any and all input on this problem would be greatly appreciated.

Sincerely,
Erik