Problem with creating the code from scratch

[Firangiz Aghayeva]

Dear Statalist users,

I have the following panel data that includes private equity investment deals (sample):

* Example generated by -dataex-. For more info, type help dataex
clear
input str20 ID str22 LP_Country str21 Manager_country int year
"5249-2609-718"    "UK"           "US"          1969
"3105-2609-718"    "Canada"       "US"          1969
"5960-2609-718"    "Canada"       "US"          1969
"4543-2609-718"    "UK"           "US"          1969
"252-2609-718"     "France"       "US"          1969
"2513-698-271"     "Canada"       "US"          1971
"234434-3574-537"  "Italy"        "US"          1978
"6792-2611-718"    "Guernsey"     "US"          1978
"5826-2612-718"    "Australia"    "US"          1980
"3732-73-21"       "Switzerland"  "US"          1980
"3561-2612-718"    "UK"           "US"          1980
"5404-2612-718"    "Australia"    "US"          1980
"3752-7448-795"    "UK"           "US"          1980
"10905-1264-496"   "Australia"    "US"          1981
"2900-7542-8918"   "US"           "UK"          1981
"2818-723-49"      "US"           "UK"          1981
"5885-2720-355"    "Australia"    "US"          1982
"10231-2720-355"   "Israel"       "US"          1982
end

I need to calculate the proxy for bilateral political relations (PR) between two countries based on United Nations voting records using the equation: PR=1−(2⁎d/dmax), where PR is the bilateral political relations, d is the sum of the distance between votes for a given bilateral pair and year, and dmax is the maximum possible distance between votes for a given bilateral pair and year. The distance between votes is calculated using only “Yes” votes equal to one and “No” votes equal to zero, ignoring the other votes. For each vote the distance is calculated as the absolute value of the difference in votes. United Nations voting records sample is the following:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte vote str21 countryname int year
1 "US"     1969
1 "Canada" 1969
1 "UK"     1969
8 "France" 1969
1 "US"     1969
1 "Canada" 1969
2 "UK"     1969
2 "France" 1969
8 "US"     1969
1 "Canada" 1969
9 "UK"     1969
0 "France" 1969
0 "US"     1969
2 "Canada" 1969
1 "UK"     1969
1 "France" 1969
0 "US"     1970
1 "Canada" 1970
2 "UK"     1970
8 "France" 1970
9 "US"     1970
2 "Canada" 1970
2 "UK"     1970
0 "France" 1970
1 "US"     1970
1 "Canada" 1970
1 "UK"     1970
1 "France" 1970
1 "US"     1971
1 "Canada" 1971
1 "UK"     1971
1 "France" 1971
2 "US"     1971
1 "Canada" 1971
2 "UK"     19671
2 "France" 19671
0 "US"     1971
2 "Canada" 1971
0 "UK"     1971
2 "France" 1971
end

Unfortunately, I have difficulties building this code from scratch, as it involves two databases. Could you please help me with this issue or guide me on how to build the code?

Kind regards,
Firangiz Aghayeva

[Clyde Schechter]

It's not clear to me what dmax is supposed to be. Since different countries have different numbers of votes in the same year, I'm taking dmax to be the lesser of the number of votes cast by either country in the pair that year. I'm reasoning that d itself is, at most, 1 per vote. But, frankly, this approach doesn't really make sense to me. In fact, it would seem to me that for the purpose of assessing political relations you would need paired data. That is, for each voting opportunity at the UN, we would want to look at the difference between the votes of a pair of countries on each occasion where both of them voted. Then dmax would be the number of occasions where both voted. Anyway, I'll leave it to you to figure that part out. Using my assumption, code like this does what you ask for:

use voting_dataset, clear
drop if !inlist(vote, 0, 1)
collapse (count) n_votes = vote (sum) vote_total = vote, by(countryname year)

preserve
rename countryname Manager_country
rename (*vote*) =2
tempfile second
save `second'
restore
rename countryname LP_Country
rename (*vote*) =1
joinby year using `second'
drop if LP_Country == Manager_country

gen d = abs(vote_total2 - vote_total1)
gen d_max = min(n_votes1, n_votes2)
gen PR = 1-(2*d/d_max)

merge 1:m LP_Country Manager_country year using deal_dataset

Replace the italicized portions with the actual data set filenames.

[Firangiz Aghayeva]

Dear Clyde,

Thank you for your reply. You are right, I need paired data. I already figured out how to construct it. Thank you for the code!

Kind regards,
Firangiz