************************************************
******Geopolitical tension_voting pattern*******
************************************************;
* import data;
proc import datafile='D:\Dropbox\Research\China Foreign Share Discount\United Nations\UN_Voting_Data.xlsx'
	out=resolution
	dbms=xlsx replace;
	sheet="Resolution Summary";
	getnames=yes;
run;

proc import datafile='D:\Dropbox\Research\China Foreign Share Discount\United Nations\UN_Voting_Data.xlsx'
	out=vote
	dbms=xlsx replace;
	sheet="Voting Details";
	getnames=yes;
run;

* clean vote data;
data vote1;
set vote;
Vote=upcase(Vote);
if Vote not in ('A', 'Y', 'N') then Vote='';
run;

proc import datafile='D:\Dropbox\Research\China Foreign Share Discount\United Nations\country_map.xlsx'
	out=country
	dbms=xlsx replace;
	sheet="country_map";
	getnames=yes;
run;

data country_map;
set country;
if not(missing(Alpha_2_code));
run;

* voting difference;
data vote2;
set vote1;
if not(missing(Vote));
run;

proc sql;
create table vote3 as select distinct a.*, b.Alpha_2_code, b.Alpha_3_code
from vote2 as a inner join country_map as b
on a.Country=b.Country;
quit;

data vote4;
set vote3;
if Vote="A" then value=0;
if Vote="N" then value=-1;
if Vote="Y" then value=1;
run;

proc sql;
create table vote_dif as select distinct a.ID, a.Alpha_2_code as iso2_1, a.Alpha_3_code as iso3_1,
										b.Alpha_2_code as iso2_2, b.Alpha_3_code as iso3_2, abs(a.value-b.value) as vote_dif
from vote4 as a inner join vote4 as b
on a.ID=b.ID and a.Alpha_2_code~=b.Alpha_2_code;
quit;

* quarterly average voting difference;
data resolution1;
set resolution;
date = input(Vote_date, yymmdd10.);
format date yymmdd10.;
year = year(date);
quarter = qtr(date);
run;

proc sql;
create table vote_dif1 as select distinct a.*, b.year
from vote_dif as a inner join resolution1 as b
on a.ID=b.ID;
quit;

proc sql;
create table vote_dif_final as select distinct iso2_1, iso3_1, iso2_2, iso3_2, year, mean(vote_dif) as vote_tension, count(unique ID) as vote_count
from vote_dif1
group by iso2_1, iso2_2, year;
quit;

* Crimea vote;
data vote_crimea;
set vote_dif1;
if ID=3940;
run;

proc export data=vote_crimea outfile= "D:\Dropbox\Research\China Foreign Share Discount\Geopolitical Risk Measures\Data\un_vote_crimea.dta" replace;
run;
