proc printto log = "/scratch/bocconi/manconi/transparency.log"; run;

libname t "/scratch/bocconi/manconi";

* Load daily CRSP data;
data crsp;
set crsp.dsf;
if year(date) >= 1969;
keep permno date prc ret askhi bidlo vol shrout;
if prc < 0 then prc = -prc;
if ret < -1 then ret = .;
run;

proc sort data = crsp; by permno date; run;

data crsp;
set crsp;
by permno date; 
lprc = lag(prc);
if first.permno then lprc = .;
run;

data crsp; set crsp; dprc = prc - lprc; run;

* Compute relevant ratios (daily frequency);
data crsp;
set crsp;
year = year(date);
bidask = (askhi - bidlo)/prc;
amihud = abs(dprc)/(vol*prc/1000000);
run;

* Merge in daily Fama-French factors;
proc sql;

	create table crsp as
		select crsp.*, ff.rf, ff.mktrf, ff.smb, ff.hml
		from crsp
		left join ff.factors_daily as ff
		on crsp.date = ff.date;

quit;

data crsp;
set crsp;
exret = ret - rf;
run;

* Sort data and run regressions to estimate idiosyn;
proc sort data = crsp; by permno year; run;

proc reg data = crsp tableout rsquare outest = betas noprint;
by permno year;
model exret = mktrf smb hml;
run;
quit;

data betas_clean;
set betas;
if _type_ = "PARMS";
run;

data betas_clean; set betas_clean; keep permno year _rsq_; run;
data betas_clean; set betas_clean; idiosyn = 1 - _rsq_; run;

proc sql;

	create table transparency as
		select permno, year, mean(bidask) as bidask, mean(amihud) as amihud
		from crsp
		group by permno, year;

	create table transparency as
		select t.*, b.idiosyn
		from transparency as t
		left join betas_clean as b
		on t.permno = b.permno
		and t.year = b.year;

quit;

/* 
	The file crsp_with_gvkey contains links between Compustat's GVKEY
	identifiers and CRSP's PERMNO identifiers. It has been downloaded 
	from the CRSP/Compustat merged database in January 2021. 
	Each row of this file corresponds to a unique GVKEY-PERMNO-datadate.
*/
data link;
set t.crsp_with_gvkey;
keep gvkey lpermno datadate;
run;

data link;
set link;
if gvkey ^= "";
rename lpermno = permno;
year = year(datadate);
run;

data link; set link; keep permno gvkey year; run;

proc sort data = link nodupkey; by permno year; run;
proc sort data = transparency; by permno year;

data transparency; 
merge transparency link;
by permno year; 
label 	bidask = "Yearly avg of daily bid-ask spead/prc"
		amihud = "Yearly avg of daily Amihud ratio"
		idiosyn = "1 - R2 from 3-factor model, yearly";
run;

proc export data = transparency outfile = "/scratch/bocconi/manconi/stock_transparency_touse.dta" replace; run;

proc printto; run;
