************************************************
************Cooperation and Conflict************
************************************************;

* import data;
proc import datafile='D:\Dropbox\Research\China Foreign Share Discount\GDELT\gdelt1.csv'
	out=gdelt
	dbms=csv replace;
	getnames=yes;
run;

* global sample;
data gdelt1;
set gdelt;
year = floor(MonthYear/100);
month = mod(MonthYear, 100);
if month<=3 then quarter=1;
if 4<=month<=6 then quarter=2;
if 7<=month<=9 then quarter=3;
if month>=10 then quarter=4;
run;

proc sort data=gdelt1 nodupkey;
by Actor1CountryCode Actor2CountryCode year month;
run;

* quarterly average;
* get the year-mth total number of events;
proc sql;
create table gdelt2 as select distinct *, sum(NumEvents) as TotalNumEvents, sum(NumEvents_weighted) as TotalNumEvents_weighted
from gdelt1
group year, month;
quit;

data gdelt_mth;
set gdelt2;
*unweighted;
tension=tension/TotalNumEvents;
NumEvents=NumEvents/TotalNumEvents;
conflict=conflict/TotalNumEvents;
NumNegEvents=NumNegEvents/TotalNumEvents;
coop=coop/TotalNumEvents;
NumPosEvents=NumPosEvents/TotalNumEvents;
*weighted;
tension_weighted=tension_weighted/TotalNumEvents_weighted;
NumEvents_weighted=NumEvents_weighted/TotalNumEvents_weighted;
conflict_weighted=conflict_weighted/TotalNumEvents_weighted;
NumNegEvents_weighted=NumNegEvents_weighted/TotalNumEvents_weighted;
coop=coop_weighted/TotalNumEvents_weighted;
NumPosEvents_weighted=NumPosEvents_weighted/TotalNumEvents_weighted;
run;


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

*get the year-qtr total number of events;
proc sql;
create table gdelt3 as select distinct *, sum(NumEvents) as TotalNumEvents, sum(NumEvents_weighted) as TotalNumEvents_weighted
from gdelt1
group year, quarter;
quit;

proc sql;
create table gdelt_qtr as select distinct Actor1CountryCode, Actor2CountryCode, year, quarter, 
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from gdelt3
group by Actor1CountryCode, Actor2CountryCode, year, quarter;
quit;

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


* China with the rest of countries;
* CHN toward others;
PROC IMPORT OUT=develop
		DATAFILE="D:\Dropbox\Research\China Foreign Share Discount\Data\historical-classification-of-developed-and-developing-regions.xlsx"
		DBMS=EXCEL REPLACE;
	RANGE="Distinction as of May 2022$";
run;

data gdelt_chn;
set gdelt3;
if Actor1CountryCode="CHN";
run;

proc sql;
create table gdelt_chn1 as select distinct a.*, b.Developed___Developing_regions as status
from gdelt_chn as a left join develop as b
on a.Actor2CountryCode=b.ISO_alpha3_Code;
quit;

data gdelt_chn_all;
set gdelt_chn1;
status = 'All Others';
run;

data gdelt_chn_ed;
set gdelt_chn1;
if status = 'Developed';
run;

data gdelt_chn_ing;
set gdelt_chn1;
if status = 'Developing';
run;

proc sql;
create table gdelt_chn1_all as select distinct Actor1CountryCode, year, quarter, status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from gdelt_chn_all
group by Actor1CountryCode, year, quarter;
quit;

proc sql;
create table gdelt_chn1_ing as select distinct Actor1CountryCode, year, quarter, status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from gdelt_chn_ing
group by Actor1CountryCode, year, quarter;
quit;

proc sql;
create table gdelt_chn1_ed as select distinct Actor1CountryCode, year, quarter, status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from gdelt_chn_ed
group by Actor1CountryCode, year, quarter;
quit;

data gdelt_chn_final1;
length Actor1CountryCode $10.;
set gdelt_chn1_all gdelt_chn1_ing gdelt_chn1_ed;
informat Actor1CountryCode $10.;
format Actor1CountryCode $10.;
rename status=Actor2CountryCode;
run;

* others toward china;
data gdelt_other;
set gdelt3;
if Actor2CountryCode="CHN";
run;

proc sql;
create table gdelt_other1 as select distinct a.*, b.Developed___Developing_regions as status
from gdelt_other as a left join develop as b
on a.Actor1CountryCode=b.ISO_alpha3_Code;
quit;

data gdelt_other_all;
set gdelt_other1;
status = 'All Others';
run;

data gdelt_other_ed;
set gdelt_other1;
if status = 'Developed';
run;

data gdelt_other_ing;
set gdelt_other1;
if status = 'Developing';
run;

proc sql;
create table gdelt_other1_all as select distinct Actor2CountryCode, year, quarter, status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from gdelt_other_all
group by Actor2CountryCode, year, quarter;
quit;

proc sql;
create table gdelt_other1_ing as select distinct Actor2CountryCode, year, quarter, status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from gdelt_other_ing
group by Actor2CountryCode, year, quarter;
quit;

proc sql;
create table gdelt_other1_ed as select distinct Actor2CountryCode, year, quarter, status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from gdelt_other_ed
group by Actor2CountryCode, year, quarter;
quit;

data gdelt_chn_final2;
length Actor2CountryCode $10.;
set gdelt_other1_all gdelt_other1_ing gdelt_other1_ed;
rename status=Actor1CountryCode;
informat Actor2CountryCode $10.;
format Actor2CountryCode $10.;
run;

data gdelt_chn_final;
set gdelt_chn_final1 gdelt_chn_final2;
run;

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

/* daily CHN & all other countries; */
proc import datafile='D:\Dropbox\Research\China Foreign Share Discount\GDELT\CHN_daily.csv'
	out=chn_daily
	dbms=csv replace;
	getnames=yes;
run;

data chn_daily1;
set chn_daily;
year=floor(SQLDATE/10000);
month=floor((SQLDATE-year*10000)/100);
day=SQLDATE-year*10000-month*100;
date = mdy(month,day,year);
informat date MMDDYY10.;
format date MMDDYY10.;
run;

proc sql;
create table chn_daily2 as select distinct *, sum(NumEvents) as TotalNumEvents, sum(NumEvents_weighted) as TotalNumEvents_weighted
from chn_daily1
group date;
quit;

proc sort data=chn_daily2 nodupkey;
by Actor1CountryCode Actor2CountryCode date;
run;

* China towards other countries;
data chn_other_daily;
set chn_daily2;
if Actor1CountryCode="CHN";
run;

proc sql;
create table chn_other_daily1 as select distinct a.*, b.Developed___Developing_regions as status
from chn_other_daily as a left join develop as b
on a.Actor2CountryCode=b.ISO_alpha3_Code;
quit;


data chn_other_all;
set chn_other_daily1;
status = 'All Others';
run;

data chn_other_ed;
set chn_other_daily1;
if status = 'Developed';
run;

data chn_other_ing;
set chn_other_daily1;
if status = 'Developing';
run;

proc sql;
create table chn_other_all1 as select distinct Actor1CountryCode, date, status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from chn_other_all
group by Actor1CountryCode, date;
quit;

proc sql;
create table chn_other_ing1 as select distinct Actor1CountryCode, date,  status,
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from chn_other_ing
group by Actor1CountryCode, date;
quit;

proc sql;
create table chn_other_ed1 as select distinct Actor1CountryCode, date,  status, 
										  sum(tension)/TotalNumEvents as tension, sum(tension_weighted)/TotalNumEvents_weighted as tension_weighted,
										  sum(NumEvents)/TotalNumEvents as NumEvents, sum(NumEvents_weighted)/TotalNumEvents_weighted as NumEvents_weighted,
										  sum(conflict)/TotalNumEvents as conflict, sum(conflict_weighted)/TotalNumEvents_weighted as conflict_weighted,
										  sum(NumNegEvents)/TotalNumEvents as NumNegEvents, sum(NumNegEvents_weighted)/TotalNumEvents_weighted as NumNegEvents_weighted,
										  sum(coop)/TotalNumEvents as coop, sum(coop_weighted/TotalNumEvents_weighted) as coop_weighted,
										  sum(NumPosEvents)/TotalNumEvents as NumPosEvents, sum(NumPosEvents_weighted)/TotalNumEvents_weighted as NumPosEvents_weighted,
										  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from chn_other_ed
group by Actor1CountryCode, date;
quit;


* Other countries towards China;
data other_chn_daily;
set chn_daily2;
if Actor2CountryCode="CHN";
run;

proc sql;
create table other_chn_daily1 as select distinct a.*, b.Developed___Developing_regions as status
from other_chn_daily as a left join develop as b
on a.Actor1CountryCode=b.ISO_alpha3_Code;
quit;

data other_chn_all;
set other_chn_daily1;
status = 'All Others';
run;

data other_chn_ed;
set other_chn_daily1;
if status = 'Developed';
run;

data other_chn_ing;
set other_chn_daily1;
if status = 'Developing';
run;

proc sql;
create table other_chn_all1 as select distinct Actor2CountryCode, date, status,
											  sum(tension) as tension, sum(tension_weighted) as tension_weighted,
											  sum(NumEvents) as NumEvents, sum(NumEvents_weighted) as NumEvents_weighted,
											  sum(conflict) as conflict, sum(conflict_weighted) as conflict_weighted,
											  sum(NumNegEvents) as NumNegEvents, sum(NumNegEvents_weighted) as NumNegEvents_weighted,
											  sum(coop) as coop, sum(coop_weighted) as coop_weighted,
											  sum(NumPosEvents) as NumPosEvents, sum(NumPosEvents_weighted) as NumPosEvents_weighted,
											  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from other_chn_all
group by Actor2CountryCode, date;
quit;

proc sql;
create table other_chn_ing1 as select distinct Actor2CountryCode, date, status,
											  sum(tension) as tension, sum(tension_weighted) as tension_weighted,
											  sum(NumEvents) as NumEvents, sum(NumEvents_weighted) as NumEvents_weighted,
											  sum(conflict) as conflict, sum(conflict_weighted) as conflict_weighted,
											  sum(NumNegEvents) as NumNegEvents, sum(NumNegEvents_weighted) as NumNegEvents_weighted,
											  sum(coop) as coop, sum(coop_weighted) as coop_weighted,
											  sum(NumPosEvents) as NumPosEvents, sum(NumPosEvents_weighted) as NumPosEvents_weighted,
											  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from other_chn_ing
group by Actor2CountryCode, date;
quit;

proc sql;
create table other_chn_ed1 as select distinct Actor2CountryCode, date, status, 
											  sum(tension) as tension, sum(tension_weighted) as tension_weighted,
											  sum(NumEvents) as NumEvents, sum(NumEvents_weighted) as NumEvents_weighted,
											  sum(conflict) as conflict, sum(conflict_weighted) as conflict_weighted,
											  sum(NumNegEvents) as NumNegEvents, sum(NumNegEvents_weighted) as NumNegEvents_weighted,
											  sum(coop) as coop, sum(coop_weighted) as coop_weighted,
											  sum(NumPosEvents) as NumPosEvents, sum(NumPosEvents_weighted) as NumPosEvents_weighted,
											  sum(negTone*NumEvents)/sum(NumEvents) as negTone, sum(negTone_weighted*NumEvents_weighted)/sum(NumEvents_weighted) as negTone_weighted
from other_chn_ed
group by Actor2CountryCode, date;
quit;

* combine the datasets;
data chn_other_final;
length Actor1CountryCode $10.;
set chn_other_all1 chn_other_ing1 chn_other_ed1;
rename status=Actor2CountryCode;
informat Actor1CountryCode $10.;
format Actor1CountryCode $10.;
run;

data other_chn_final;
length Actor2CountryCode $10.;
set other_chn_all1 other_chn_ing1 other_chn_ed1;
rename status=Actor1CountryCode;
informat Actor2CountryCode $10.;
format Actor2CountryCode $10.;
run;

data chn_daily_final;
set other_chn_final chn_other_final;
run;

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

* daily CHN & USA;
data gdelt_daily;
set chn_daily2;
if (Actor1CountryCode="CHN" and Actor2CountryCode='USA') or (Actor1CountryCode="USA" and Actor2CountryCode='CHN');
*unweighted;
tension=tension/TotalNumEvents;
NumEvents=NumEvents/TotalNumEvents;
conflict=conflict/TotalNumEvents;
NumNegEvents=NumNegEvents/TotalNumEvents;
coop=coop/TotalNumEvents;
NumPosEvents=NumPosEvents/TotalNumEvents;
*weighted;
tension_weighted=tension_weighted/TotalNumEvents_weighted;
NumEvents_weighted=NumEvents_weighted/TotalNumEvents_weighted;
conflict_weighted=conflict_weighted/TotalNumEvents_weighted;
NumNegEvents_weighted=NumNegEvents_weighted/TotalNumEvents_weighted;
coop=coop_weighted/TotalNumEvents_weighted;
NumPosEvents_weighted=NumPosEvents_weighted/TotalNumEvents_weighted;
run;

proc sort data=gdelt_daily nodupkey;
by Actor1CountryCode Actor2CountryCode date;
run;

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


