libname ws "E:\worldscope";
libname comp "E:\Compustat";

proc import datafile = "D:\Dropbox\Research\China Foreign Share Discount\Conference Call Transcript\calls_part_full_0227.dta" 
	out=call_part 
	replace;
run;

/* download GVKEY ISIN link (according to Rui Dai's paper;
%let wrds=wrds.wharton.upenn.edu 4016;
options comamid=TCP remote=WRDS;
signon username=_prompt_;

rsubmit;

proc download data=comp.G_SECURITY out=comp.G_SECURITY; run;

endrsubmit;
signoff;
*/

* merge in gvkey;
proc sql;
create table call_part1 as select distinct a.*, b.gvkey
from call_part as a left join comp.G_SECURITY as b
on a.isin = b.isin;
quit;

* Get the participant country list;
data part_ctry;
set call_part1;
if not(missing(part_ctry1));
rename part_ctry1=part_ctry;
keep part_ctry1;
run;



/*
data part_ctry2;
set call_part;
if not(missing(part_ctry2));
rename part_ctry2=part_ctry;
keep part_ctry2;
run;

data part_ctry;
set part_ctry1 part_ctry2;
if length(part_ctry)=3;
run;
*/

proc sort data=part_ctry nodupkey;
by part_ctry;
run;

* merge iso2 and also delete incorrect iso3;
proc import datafile='D:\Dropbox\Research\China Foreign Share Discount\Conference Call Transcript\country code.xlsx'
	out=countryCode
	dbms=xlsx replace;
	sheet="Sheet1";
	getnames=yes;
run;

proc sql;
create table part_ctry1 as select distinct a.*, b.alpha_2_code as part_ctry_iso2
from part_ctry as a inner join countryCode as b
on a.part_ctry=b.alpha_3_code;
quit;

* get the event list;
data eventList;
set call_part1;
drop institutions part_ctry1 part_ctry2 institution foreign;
run;

proc sort data=eventList nodupkey;
by eventId;
run;

* count by country;
proc sql;
create table part_count as select distinct a.eventId, a.part_ctry1 as part_ctry, count(a.part_ctry1) as part_ctry_num
from call_part1 as a
group by a.eventId, a.part_ctry1;
quit;

* Expand by participant country;
proc sql;
create table event_part as select distinct a.*, b.*
from eventList as a full join part_ctry1 as b
on not(missing(a.eventId)) and not(missing(b.part_ctry));
quit;

proc sql;
create table event_part1 as select distinct a.*, b.part_ctry_num
from event_part as a left join part_count as b
on a.eventId=b.eventId and a.part_ctry=b.part_ctry;
quit;

data event_part_final;
set event_part1;
if part_ctry_num=. then part_ctry_num=0;
rename Alpha_2_code=firm_ctry_iso2;
keep eventId eventTypeId isin gvkey Alpha_2_code firm_ctry year quarter part_ctry part_ctry_iso2 part_ctry_num;
run;

proc export data=event_part_final outfile= "D:\Dropbox\Research\China Foreign Share Discount\Conference Call Pairs Analysis\call_part_full_all_0227.dta" replace;
run;
