options errorabend;
libname crsp "../data/raw";
libname match "../data/CIQ";
libname ciq "C:\Users\jaredflake\Dropbox\Research\Data\CIQ_Transcripts\capitaliq_transcripts";
libname forth "../data";

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
/**Step 1: Get classification data and pull permno from CRSP GVKEY-PERMNO linking table**/
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;

%let infile = "../data/glz.csv";

data work.glz;
	infile &infile dlm=',' dsd firstobs=2;
	format gvkey0 best32. mostimportantdateutc yymmdd10.; 
	informat gvkey0 best32. mostimportantdateutc yymmdd10.;
	input transcriptid gvkey0 mostimportantdateutc :yymmdd10. companyid0 word_count_qa 
			transcriptcomponentid_q transcriptcomponenttypeid_q word_count_q group_id transcriptpersonid_q proid_q 
			transcriptcomponentid_ans transcriptcomponenttypeid_ans word_count_ans nonanswer refuse unable aftercall transcriptpersonid_ans proid_ans word_count_ans_scaled qa_min;
run;
* Visually confirmed first 5 rows line up with the output file from python;

data glz1; set glz;
t_year = year(mostimportantdateutc);
run;

proc sql;
	create table date_range as select
	min(mostimportantdateutc) as start_ciq format=yymmddn8., max(mostimportantdateutc) as last_ciq format=yymmddn8.
	from glz1;
	quit;
proc print data = date_range;
run;

proc sql;
	create table date_range as select
	t_year as year, count(*) as N
	from glz1 group by t_year;
	quit;
proc print data = date_range;
run;

proc means data = glz1; run;

data glz2; set glz1;
gvkey = put(gvkey0,z6.);
format mostimportantdateutc yymmddn8.;
drop gvkey0;
run;


**************************
* Merge in analyst name and analyst's company name;
proc sql;
	create table glz2a as select distinct
	a.*, b.transcriptpersonname, coalescec(b.companyname,c.companyname) as companyofperson_q
	from glz2 as a 
		left join ciq.ciqtranscriptperson as b on a.transcriptpersonid_q = b.transcriptpersonid
		left join ciq.wrds_professional as c on a.proid_q = c.proid;
quit;

proc sort data = glz2a out = glz2b nodupkey; by transcriptid transcriptcomponentid_q transcriptcomponentid_ans;
run;

proc sql;
	create table glz3 as select
	a.*, b.estimid
	from glz2b as a left join match.ciq_ibesbrokertranslation as b
	on a.companyofperson_q = b.companyofperson
	order by transcriptid, transcriptcomponentid_q, transcriptcomponentid_ans;
quit;

proc sql;
	create table glz3b as select
	a.*, b.amaskcd
	from glz3 as a left join match.ciq_ibesanalysttranslation as b
	on a.transcriptpersonid_q = b.transcriptpersonid
	order by transcriptid, transcriptcomponentid_q, transcriptcomponentid_ans;
quit;

proc sort data = glz3b out = glz_temp 
(keep = gvkey mostimportantdateutc)
nodupkey; by gvkey mostimportantdateutc;
run;

*Merge to get gvkey;
proc sql; create table glz_temp2 as select distinct
	a.*, b.lpermno as permno
    from glz_temp as a left join crsp.ccmxpf_linktable
	(where=(linktype in ('LU', 'LC', 'LD', 'LF', 'LN', 'LO', 'LS', 'LX'))) as b
    on (a.gvkey = b.gvkey)
	and (b.linkdt-90 <= a.mostimportantdateutc or b.linkdt = .B) 
	and (a.mostimportantdateutc <= b.linkenddt+90 or b.linkenddt = .E);
 quit;

proc sort data=glz_temp2 nodupkey; by gvkey mostimportantdateutc;
where permno ne .;
run;

proc sql;
	create table glz4 as select
	a.*, b.permno
	from glz3b as a left join glz_temp2 as b
	on a.gvkey = b.gvkey
	and a.mostimportantdateutc = b.mostimportantdateutc;
quit;

proc sql;
	create table dates as select
	min(mostimportantdateutc) as start format=yymmddn8., max(mostimportantdateutc) as last format=yymmddn8.
	from glz4;
	quit;
proc print data = dates;
run;

proc sql;
	create table dates as select
	t_year as year, count(*) as N
	from glz4 group by t_year;
	quit;
proc print data = dates;
run;

data forth.glz_w_ibes; set glz4;
run;



