options errorabend;

libname perm '../data';
libname ibes '../data/raw';
libname crsp '../data/raw';
libname comp '../data/raw';
libname disc '../data/raw';

%let infile = "../data/raw/restatement-data_20210604.csv";

data WORK.RESTATEMENTS  ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile &infile
    delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat Company $27. ;
       informat CIK_Code best32. ;
       informat Ticker $5. ;
       informat Market $22. ;
       informat IRS_Number $10. ;
       informat Bus_Street_1 $34. ;
       informat Bus_Street_2 $21. ;
       informat City $15. ;
       informat County $13. ;
       informat State_Code $2. ;
       informat State_Name $16. ;
       informat State_Region $17. ;
       informat Zip $10. ;
       informat Bus_Phone $16. ;
       informat Parent_CIK $1. ;
       informat Parent_Name $1. ;
       informat SIC_Code best32. ;
       informat SIC_Description $54. ;
       informat NAICS_Code best32. ;
       informat NAICS_Description $74. ;
       informat Filer_Act best32. ;
       informat Auditor___Current $28. ;
       informat Auditor___Current_Key best32. ;
       informat Auditor___At_Disclosure_Date $28. ;
       informat Auditor___At_Disclosure_Date_Ke best32. ;
       informat Auditor___During_Restated_Perio $96. ;
       informat VAR27 $5. ;
       informat Auditor___Opinion_Period_End_Du $45. ;
       informat VAR29 $5. ;
       informat Restatement_Key best32. ;
       informat Restated_Period_Begin yymmdd10. ;
       informat Restated_Period_Ended yymmdd10. ;
       informat Disclosure $15. ;
       informat Disclosure_Date yymmdd10. ;
       informat Disclosure_Accepted $21. ;
       informat Effect $8. ;
       informat Currency $3. ;
       informat Cumulative_Change_in_Net_Income best32. ;
       informat Cumulative_Change_in_Stockholde best32. ;
       informat Accounting_Rule__GAAP_FASB__App $200. ;
       informat Financial_Fraud__Irregularities $1. ;
       informat Errors___Accounting_and_Clerica $1. ;
       informat Other_Significant_Issues $64. ;
       informat SEC_Investigation $1. ;
       informat Board_Involvement $2. ;
       informat Auditor_Letter___Discussion $2. ;
       informat MR___Stock_Price____ best32. ;
       informat MR___Stock_Price_Date yymmdd10. ;
       informat MR___Market_Cap____ $19. ;
       informat MR___Financials_Date yymmdd10. ;
       informat MR___Revenue____ $17. ;
       informat MR___Earnings____ $17. ;
       informat MR___Book_Value____ $17. ;
       informat MR___Assets____ $19. ;
       informat H___Stock_Price____ best32. ;
       informat H___Stock_Price_Date $10. ;
       informat H___Market_Cap____ $17. ;
       informat H___Financials_Date yymmdd10. ;
       informat H___Revenue____ $16. ;
       informat H___Earnings____ $16. ;
       informat H___Book_Value____ $17. ;
       informat H___Assets____ $19. ;
       informat Date_of_8_K_Item_4_02 yymmdd10. ;
       informat Days_to_Securities_Class_Action best32. ;
       informat Securities_Class_Action_Litigat best32. ;
       informat VAR66 $1. ;
       informat VAR67 $1. ;
       informat VAR68 $1. ;
       format Company $27. ;
       format CIK_Code best12. ;
       format Ticker $5. ;
       format Market $22. ;
       format IRS_Number $10. ;
       format Bus_Street_1 $34. ;
       format Bus_Street_2 $21. ;
       format City $15. ;
       format County $13. ;
       format State_Code $2. ;
       format State_Name $16. ;
       format State_Region $17. ;
       format Zip $10. ;
       format Bus_Phone $16. ;
       format Parent_CIK $1. ;
       format Parent_Name $1. ;
       format SIC_Code best12. ;
       format SIC_Description $54. ;
       format NAICS_Code best12. ;
       format NAICS_Description $74. ;
       format Filer_Act best12. ;
       format Auditor___Current $28. ;
       format Auditor___Current_Key best12. ;
       format Auditor___At_Disclosure_Date $28. ;
       format Auditor___At_Disclosure_Date_Ke best12. ;
       format Auditor___During_Restated_Perio $96. ;
       format VAR27 $5. ;
       format Auditor___Opinion_Period_End_Du $45. ;
       format VAR29 $5. ;
       format Restatement_Key best12. ;
       format Restated_Period_Begin yymmdd10. ;
       format Restated_Period_Ended yymmdd10. ;
       format Disclosure $15. ;
       format Disclosure_Date yymmdd10. ;
       format Disclosure_Accepted $21. ;
       format Effect $8. ;
       format Currency $3. ;
       format Cumulative_Change_in_Net_Income best12. ;
       format Cumulative_Change_in_Stockholde best12. ;
       format Accounting_Rule__GAAP_FASB__App $200. ;
       format Financial_Fraud__Irregularities $1. ;
       format Errors___Accounting_and_Clerica $1. ;
       format Other_Significant_Issues $64. ;
       format SEC_Investigation $1. ;
       format Board_Involvement $2. ;
       format Auditor_Letter___Discussion $2. ;
       format MR___Stock_Price____ best12. ;
       format MR___Stock_Price_Date yymmdd10. ;
       format MR___Market_Cap____ $19. ;
       format MR___Financials_Date yymmdd10. ;
       format MR___Revenue____ $17. ;
       format MR___Earnings____ $17. ;
       format MR___Book_Value____ $17. ;
       format MR___Assets____ $19. ;
       format H___Stock_Price____ best12. ;
       format H___Stock_Price_Date $10. ;
       format H___Market_Cap____ $17. ;
       format H___Financials_Date yymmdd10. ;
       format H___Revenue____ $16. ;
       format H___Earnings____ $16. ;
       format H___Book_Value____ $17. ;
       format H___Assets____ $19. ;
       format Date_of_8_K_Item_4_02 yymmdd10. ;
       format Days_to_Securities_Class_Action best12. ;
       format Securities_Class_Action_Litigat best12. ;
       format VAR66 $1. ;
       format VAR67 $1. ;
       format VAR68 $1. ;
    input
                Company  $
                CIK_Code
                Ticker  $
                Market  $
                IRS_Number  $
                Bus_Street_1  $
                Bus_Street_2  $
                City  $
                County  $
                State_Code  $
                State_Name  $
                State_Region  $
                Zip  $
                Bus_Phone  $
                Parent_CIK  $
                Parent_Name  $
                SIC_Code
                SIC_Description  $
                NAICS_Code
                NAICS_Description  $
                Filer_Act
                Auditor___Current  $
                Auditor___Current_Key
                Auditor___At_Disclosure_Date  $
                Auditor___At_Disclosure_Date_Ke
                Auditor___During_Restated_Perio  $
                VAR27  $
                Auditor___Opinion_Period_End_Du  $
                VAR29  $
                Restatement_Key
                Restated_Period_Begin
                Restated_Period_Ended
                Disclosure  $
                Disclosure_Date
                Disclosure_Accepted  $
                Effect  $
                Currency  $
                Cumulative_Change_in_Net_Income
                Cumulative_Change_in_Stockholde
                Accounting_Rule__GAAP_FASB__App  $
                Financial_Fraud__Irregularities  $
                Errors___Accounting_and_Clerica  $
                Other_Significant_Issues  $
                SEC_Investigation  $
                Board_Involvement  $
                Auditor_Letter___Discussion  $
                MR___Stock_Price____
                MR___Stock_Price_Date
                MR___Market_Cap____  $
                MR___Financials_Date
                MR___Revenue____  $
                MR___Earnings____  $
                MR___Book_Value____  $
                MR___Assets____  $
                H___Stock_Price____
                H___Stock_Price_Date
                H___Market_Cap____  $
                H___Financials_Date
                H___Revenue____  $
                H___Earnings____  $
                H___Book_Value____  $
                H___Assets____  $
                Date_of_8_K_Item_4_02
                Days_to_Securities_Class_Action
                Securities_Class_Action_Litigat
                VAR66  $
                VAR67  $
                VAR68  $
;
if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
run;

/*
Step 1: Get starting restatement sample and merge in GICS, GVKEY, and PERMNO
*/
data restate; set restatements 
(keep = company cik_code ticker market IRS_Number restatement_key restated_period_begin restated_period_ended disclosure 
			disclosure_date effect cumulative_change_in_net_income Cumulative_Change_in_Stockholde sec_investigation board_involvement
			Auditor_Letter___Discussion MR___Stock_Price____ MR___Stock_Price_Date MR___Market_Cap____ Date_of_8_K_Item_4_02 
			Days_to_Securities_Class_Action Securities_Class_Action_Litigat Accounting_Rule__GAAP_FASB__App Other_Significant_Issues effect);
if sec_investigation eq "Y" then sec = 1; else sec = 0;
if board_involvement = "Y" then board = 1; else board = 0;
if Auditor_Letter___Discussion = "Y" then auditor = 1; else auditor = 0;
drop sec_investigation board_involvement Auditor_Letter___Discussion;
disclosure_year = year(disclosure_date);
if 2005 le disclosure_year;
run;

proc sort data=restate nodupkey nodupkey; by cik_code disclosure_date;
run;
* 9,881 restatements - 8,403 restatements from AA between 2006 and 2019;

* ISSUES LISTED ARE SEPARATED BY "|", I separate the listed issues into separate columns to create category variables;
data restate1; set restate;
length issue1-issue6 $75.;
array issue(6) $;
do i = 1 to dim(issue);
	issue[i]=scan(Accounting_Rule__GAAP_FASB__App,i,'|','M');
end;
drop i issue1;
run;

data restate2; set restate1;
if find(issue2,"In-process research and development") then category = "IPR&D";
else if find(issue2,"Revenue recognition") then category = "REV";
else if find(issue2,"Acquisitions, mergers") then category = "M&A";
else if find(issue2,"Deferred, stock-based and/or executive comp issues") then category = "COMP";
else if find(issue2,"Asset retirement issues") then category = "REST";
else if find(issue2,"Financial derivatives") then category = "SEC";
else if find(issue2,"security issues") then category = "SEC";
else if find(issue2,"classification") then category = "CLASS";
else if find(issue2,"Depreciation") then category = "EXP";
else if find(issue2,"Expense",'i') then category = "EXP";
else if find(issue2,"Capitalization of expenditures issues") then category = "EXP";
else if find(issue2,"Lease, leasehold") then category = "EXP";
else if find(issue2,"Inventory, vendor and/or cost of sales issues") then category = "EXP";
else if find(issue2,"Liabilities, payables, reserves and accrual estimate failures") then category = "EXP";
else if find(issue2,"(value/diminution) issues") then category = "REST";
else if find(issue2,"Foreign, related party,") then category = "RPT";
else if find(issue2,"Consolidation issues") then category = "RPT";
else if find(issue2,"Accounts/loans receivable,") then category = "OTH";
else category = "OTH";
run;

proc sort data = restate2 out = restate3 (keep = company cik_code ticker market restatement_key restated_period_begin restated_period_ended disclosure sec board
			auditor disclosure_date disclosure_year effect category issue2) nodupkey;
by cik_code restatement_key disclosure_date ;
where restatement_key ne . ;
run;

/*
Find gvkey and permno for the restatement observations. Keep the first gvkey after the RDQ.
*/
data y; set comp.fundq
(keep = gvkey cusip cik cusip tic datadate indfmt datafmt popsrc consol fqtr fyearq
		rdq);
where /*tic in ('MSFT','PII','HPQ','AAPL',"AIR","CECE","MATX","ALCO") and*/ year(datadate)>1993
and indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C';
drop indfmt datafmt popsrc consol;
cikn = cik*1;
run;

proc sql; create table y2 as select distinct
	a.*, b.lpermno as permno
    from y 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 <= a.datadate or b.linkdt = .B) 
	and (a.datadate <= b.linkenddt or b.linkenddt = .E);
 quit;

proc sort data = y2 nodupkey; by gvkey datadate; run;

* Merge in 8-digit sub-industry code (GICS);
proc sql;
	create table y2 as select
	a.*, b.gsubind as gics
	from y2 as a left join comp.names as b
	on a.gvkey = b.gvkey
	and b.year1 le a.fyearq le b.year2
	having permno ne . and gsubind ne "";
quit;


/*Identify restatements that are in the EA window*/
proc sql;
	create table restate3 as select distinct
	a.*, (b.rdq ne .) as ea_window
	from restate3 as a left join y2 as b
	on a.cik_code = b.cikn
	and intnx('weekday',a.disclosure_date,-1) le b.rdq le intnx('weekday',a.disclosure_date, +1);
quit;
*9,881 restatement obs;

proc sql;
	create table restate4 as select
	a.*, b.* , a.disclosure_date-b.rdq as dif_disc_rdq
	from restate3 as a left join y2 as b
	on a.cik_code = b.cikn
	and intnx('weekday',b.rdq,-1) le a.disclosure_date le intnx('month',b.rdq,13)-1 /*Disclosure date is between the day prior to the EA Date and 12 months later*/
	;
quit;

proc sort data = restate4; by cik_code restatement_key disclosure_date datadate rdq;
run;
* Keep the most recent compustat and permno identifiers prior to the 
* Why am I dropping 3,000 obs here?;
data restate5; set restate4; by cik_code restatement_key disclosure_date;
if last.disclosure_date; /* keep observations with the closest EA Date to the disclosure Date*/
/*drop dif_disc_rdq;*/
restate = 1;
run;

data restate5; set restate5;
where gics ne "";
event_id = _n_;
run;
*5,770 obs;

/*
Step 2: Get starting peer firm sample
- All firms in the GICS industry around the disclosure_date
* Get peer firms;
*		Will need to deal with observations with multiple restatements in the same industry on the same day;
*/
proc sort data = restate5 out = events (keep = gics event_id disclosure_date category issue2 ea_window) nodupkey; by gics event_id disclosure_date;
run;
* merge in data for peers if they have compustat data in the year leading up to the disclosure date;
proc sql;
	create table peers as select
	a.*, b.*, a.disclosure_date - b.rdq as dif_disc_rdq
	from events as a left join y2 as b
	on a.gics = b.gics
	and intnx('weekday',b.rdq,-1) le a.disclosure_date le intnx('month',b.rdq,13)-1;
quit;


proc sort data = peers nodupkey; by gvkey disclosure_date datadate rdq;
run;
*keep obs closest to disclosure date for each peer firm;
data peers2; set peers; by gvkey disclosure_date datadate rdq;
if last.disclosure_date;
*drop dif_disc_rdq;
run;

* Identify restating firms to remove from peer sample;
proc sql;
	create table peers3 as select
	a.*, (b.restate = 1) as restate
	from peers2 as a left join restate5 as b
	on a.gvkey = b.gvkey
	and a.disclosure_date = b.disclosure_date
	order by event_id;
quit;

/*
Step 3 - calculate abnormal returns over various windows around the restatement announcement
*/
proc sql;
	create table ret_2 as select
	a.*, b.date, b.ret, abs(b.prc) as prc, b.vol, b.shrout, b.hexcd
	from peers3 as a left join crsp.dsf as b
	on a.permno = b.permno
	and intnx('weekday',a.disclosure_date,-10) le b.date le intnx('weekday',a.disclosure_date,60)
	having prc >= 1 and ret ne .B;
quit;

*Get delisting returns;
proc sql;
	create table ret_3 as select
	a.*, b.dlret
	from ret_2 as a left join crsp.dse as b
	on a.permno = b.permno
	and a.date = b.date
;
quit;

*Get Historical SIC code from CRSP;
proc sql; create table ret_3a as select	
	a.*, b.exchcd, shrcd
	from ret_3 as a left join crsp.dseexchdates as b
	on (a.permno eq b.permno) 
	and b.namedt le a.date le b.NAMEENDT
	order by permno, date;
quit;

*Get value-weighted returns;
proc sql;
	create table ret_3b as select
	a.*, b.vwretd
	from ret_3a as a left join crsp.dsi as b
	on a.date = b.date;
quit;

data ret_3c; set ret_3b;
if dlret = . then dlret = 0;
if exchcd in (1,2,3) and shrcd in (10,11);
run;

proc sort data = ret_3c nodupkey; by permno disclosure_date date;
run;

* Split sample into separate windows to calculate window returns;
data pre_annc; set ret_3c;
where date < intnx('weekday',disclosure_date,-1);
	by permno disclosure_date date;
	if first.disclosure_date then count = 1;
		else count +1;
run;

data annc; set ret_3c;
where intnx('weekday',disclosure_date,-1) le date le intnx('weekday',disclosure_date,4);
	by permno disclosure_date date;
	if first.disclosure_date then count = 1;
		else count +1;
	if count le 3;
run;

data post_annc; set ret_3c;
where intnx('weekday',disclosure_date,2) lt date lt intnx('weekday',disclosure_date,10);
	by permno disclosure_date date;
	if first.disclosure_date then count = 1;
		else count +1;
run;

data post_annc2; set ret_3c;
where intnx('weekday',disclosure_date,2) lt date ;
	by permno disclosure_date date;
	if first.disclosure_date then count = 1;
		else count +1;
run;

**Create and merge CAR/bhar for different windows ; 
proc sql;
	create table pre_annc1 as select distinct
	permno, disclosure_date, min(prc) as prc,
			sum(ret+dlret-vwretd) as car_01, 
			exp(sum(log(1+ret+dlret)))-exp(sum(log(1+vwretd))) as bhar_01, 
			count(ret) as n
	from pre_annc group by permno, disclosure_date;
quit;

proc sql;
	create table annc1 as select distinct
	permno, disclosure_date, min(prc) as prc,
			sum(ret+dlret-vwretd) as car_01, 
			exp(sum(log(1+ret+dlret)))-exp(sum(log(1+vwretd))) as bhar_01, 
			count(ret) as n
	from annc group by permno, disclosure_date;
quit;

*Keep obs that have obs for three days;
data annc2; set annc1;
	if n=3;
run;

proc sql;
	create table post_annc1 as select distinct
	permno, disclosure_date, min(prc) as prc,
			sum(ret+dlret-vwretd) as car_01, 
			exp(sum(log(1+ret+dlret)))-exp(sum(log(1+vwretd))) as bhar_01, 
			count(ret) as n
	from post_annc group by permno, disclosure_date;
quit;

proc sql;
	create table post_annc3 as select distinct
	permno, disclosure_date, min(prc) as prc,
			sum(ret+dlret-vwretd) as car_01, 
			exp(sum(log(1+ret+dlret)))-exp(sum(log(1+vwretd))) as bhar_01, 
			count(ret) as n
	from post_annc2 group by permno, disclosure_date;
quit;

* merge returns back to main dataset;
proc sql;
	create table peers4 as select
	a.*,
	b.car_01 as car_pre, b.bhar_01 as bhar_pre, b.prc as prc_pre,
	c.car_01 as car_annc, c.bhar_01 as bhar_annc, b.prc as prc_ann,
	d.car_01 as car_post, d.bhar_01 as bhar_post, b.prc as prc_post,
	e.car_01 as car_post2, e.bhar_01 as bhar_post2,b.prc as prc_post2
	from peers3 as a 
		left join pre_annc1 as b on a.permno = b.permno and a.disclosure_date = b.disclosure_date
		left join annc2 as c on a.permno = c.permno and a.disclosure_date = c.disclosure_date
		left join post_annc1 as d on a.permno = d.permno and a.disclosure_date = d.disclosure_date
		left join post_annc3 as e on a.permno = e.permno and a.disclosure_date = e.disclosure_date;
quit;


data peers5; set peers4;
if prc_pre >= 5;
run;

* Filter restatement firms;
data restate7; set peers5;
if restate = 1;
run;

data restate7aa; set restate7;
if ea_window = 0 ;
run;

data restate7a; set restate7aa;
if bhar_annc < 0 ;
run;

data restate7b; set restate7a;
if bhar_annc <= -0.01 ;
run;

* Filter peer firms;
data peers6; set peers5;
if restate=0;
run;

* Firm did not have a restatement in the prior 24 months;
proc sql;
	create table peers7 as select distinct
	a.*, max(b.cik_code ) as bullet_restate
	from peers6 as a left join restate as b
	on a.cikn = b.cik_code
	and intnx('month',a.disclosure_date,-24) le b.disclosure_date lt a.disclosure_date
	group by gvkey, a.disclosure_date;
quit;

data peers8; set peers7;
if bullet_restate = .;
drop bullet_restate;
run;

proc sort data = peers8 nodupkey; by gvkey disclosure_date;
run;
* count number of non-restating peers;
proc sql;
	create table peers9 as select distinct
	*, count(event_id) as n_peers
	from peers8 group by event_id
	order by gvkey, disclosure_date;
quit;

proc sort data = peers9 out = n_peers (keep = event_id n_peers) nodupkey; by event_id;
run;

proc sql;
	create table restate7c as select
	a.*, b.n_peers
	from restate7b as a left join n_peers as b
	on a.event_id = b.event_id;
quit;

data restate7d; set restate7c;
if n_peers >= 5;
run;
* 1004 events outside of EA window, with return less than -0.01, and more than 5 peers;

proc sql;
	create table peers10 as select
	a.*, b.event_id as bullet
	from peers9 as a left join restate7d as b
	on a.event_id = b.event_id
	having bullet ne .;
quit;

proc sort data = peers10 nodupkey; by gvkey disclosure_date;
run;

proc sort data = restate7d nodupkey; by gvkey disclosure_date;
run;

data sample; set peers10 restate7d;
run;
proc sort data = sample nodupkey; by restate gvkey disclosure_date;
run;


proc means data = sample n mean std min p25 p50 p75 max;
var car_annc bhar_annc ;
class restate;
run;

/*
Step : Calculate independent variables
	- Annualize raw amounts
	- Scale by avg assets over the same annualization period
	- 
*/
data comp; set comp.fundq
(keep = gvkey tic cik cusip datadate rdq indfmt datafmt popsrc consol curcdq fqtr fyr fyearq
		IBQ OANCFY IVNCFY ATQ CSHOQ PRCCQ CEQQ LTQ );
where /*tic in ('MSFT', 'DELL') and */(year(datadate) ge 1994) and (year(datadate) le 2020)
		and (indfmt='INDL') and (datafmt='STD') and (popsrc='D') and (consol='C') and (curcdq='USD') and (atq > 0);
YEAR = year(datadate);
MVE = prccq*cshoq;
LEV = LTQ / MVE;
drop indfmt datafmt popsrc consol;
run;


proc sql;
	create table comp2 as select
	a.*, b.gsubind as GICS
	from comp as a left join comp.names as b
	on a.gvkey = b.gvkey
	and b.year1 le a.fyearq le b.year2;
quit;

proc sql; create table comp2a as select 
	a.*, b.lpermno as permno
    from comp2 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 <= a.datadate or b.linkdt = .B) 
	and (a.datadate <= b.linkenddt or b.linkenddt = .E)
	order by gvkey, year;
 	quit;

data comp2a; set comp2a;
where permno ne .;
run;

proc sql;
	create table comp2b as select
	a.*, b.ticker as ibes_ticker
	from comp2a as a left join crsp.ibcrsphist as b
	on a.permno = b.permno
	and (b.sdate-90 le a.datadate or b.sdate = .B)
	and (a.datadate le b.edate+90 or b.edate = .E);
quit;

proc sort data=comp2b nodupkey; by gvkey fyearq fqtr;
where permno ne . and ibes_ticker ne ""
;
run;

* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
* Analyst benchmark - Modified code from Tim Seidel 													   ;
* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
* Add analyst data;
data i; set ibes.statsum_epsus 
	(keep = ticker statpers fpi measure usfirm numest lowest meanest highest
		stdev actual anndats_act fpedats);
where /*(ticker in ('MSFT', 'HPQ', 'SNO','AAPL')) and*/ (measure eq 'EPS') and (fpi = '6') and (year(statpers) ge 1995 and year(statpers) le 2020);
drop measure usfirm;
year = year(fpedats);
run;

data ibesdata; set i;
if missing(stdev) then stdev=0;
actualdiff=actual-MEANEST;
range=highest-lowest;
run;

data ibesdata; set ibesdata;
* Difference between statistical period and announcement date;
days=ANNDATS_ACT-statpers;
run;

proc sort data=ibesdata; by ticker FPEDATS days;
run;

proc sql;
	create table follow as select
	ticker, year, max(numest) as follow
	from ibesdata group by ticker, year;

	create table ibesdata as select
	a.*, b.follow
	from ibesdata as a left join follow as b
	on a.ticker=b.ticker
	and a.year=b.year;
quit;

proc sort data = ibesdata nodupkey; by ticker statpers fpedats; run;

data ibesdata; set ibesdata;
if days le 0 then delete;
run;

proc sort data=ibesdata; by ticker FPEDATS days;
run;
data ibesdata2; set ibesdata; by ticker FPEDats days;
if first.FPEDats;
run;


data ibesdata2; set ibesdata2;
horizon=days/30;
run;

*create lag actual;
proc sort data=ibesdata2; by ticker FPEDATS;
run;

data ibesdata2; set ibesdata2; by ticker FPEDATS;
actual_tm1=lag(actual);
actualdiff_tm1=lag(actualdiff);
lagyear=lag(year);
if first.ticker then do; actual_tm1=.; actualdiff_tm1=.; lagyear=.; end;
run;

proc sort data=ibesdata2; by ticker FPEDATS;
run;


*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
*Merge to main dataset - add to main program;
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~;
proc sql; create table comp2c as select
	a.*,b.actual, b.actualdiff, b.horizon, b.actualdiff_tm1, b.follow, log(1+b.follow) as lnfollow
	from comp2b as a left join ibesdata2 as b
	on a.ibes_ticker = b.ticker
	and month(a.DATADATE) = month(b.FPEDATS)
	and year(a.DATADATE) = year(b.FPEDATS);	
	quit;

	
proc sql;
	create table comp2d as select
	a.*, a.actual/b.actual - 1 as epsgrth
	from comp2c as a left join comp2c as b
	on a.gvkey = b.gvkey
	and a.fyearq = b.fyearq+1
	and a.fqtr = b.fqtr;
quit;


* Get lagged assets to calculate average assets;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=comp2d out=comp3 method=none;
convert oancfy = oancfy_tm1 / transform=(lag 1);	
convert ivncfy = ivncfy_tm1 / transform=(lag 1);	
convert fyearq = fyearq_tm1 / transform=(lag 1); 
convert fqtr = fqtr_tm1 / transform=(lag 1); 
by gvkey;
run;	
proc printto; run;

data comp4; set comp3;
if (fyearq - fyearq_tm1 = 0 and fqtr - fqtr_tm1 ne 1) or (fyearq - fyearq_tm1 = 1 and fqtr - fqtr_tm1 ne -3) then 
do;
	oancfy_tm1 = .;
	ivncfy_tm1 = .;
end;
if fqtr = 1 then 
do;
	oancfq = oancfy;
	ivncfq = ivncfy;
end;
else do;
	oancfq = oancfy - oancfy_tm1; 
	ivncfq = ivncfy - ivncfy_tm1;
end;
drop oancfy_tm1 ivncfy_tm1 oancfy ivncfy fyearq_tm1 fqtr_tm1;
run;

* Annualize quarterly data by summing the lagged quarters ;
proc printto log="../logs/proc_expand_junk.log" ; run;
proc expand data=comp4 out=comp5 method=none;
convert atq = atq_tm1 / transform=(lag 1);	
convert atq = atq_tm2 / transform=(lag 2);	
convert atq = atq_tm3 / transform=(lag 3);	
convert atq = atq_tm4 / transform=(lag 4);	

convert ibq = ibq_tm1 / transform=(lag 1);	
convert ibq = ibq_tm2 / transform=(lag 2);	
convert ibq = ibq_tm3 / transform=(lag 3);	
convert ibq = ibq_tm4 / transform=(lag 4);	

convert oancfq = oancfq_tm1 / transform=(lag 1);	
convert oancfq = oancfq_tm2 / transform=(lag 2);	
convert oancfq = oancfq_tm3 / transform=(lag 3);	
convert oancfq = oancfq_tm4 / transform=(lag 4);	

convert ivncfq = ivncfq_tm1 / transform=(lag 1);	
convert ivncfq = ivncfq_tm2 / transform=(lag 2);	
convert ivncfq = ivncfq_tm3 / transform=(lag 3);	
convert ivncfq = ivncfq_tm4 / transform=(lag 4);

convert epsgrth = epsgrth_tm1 / transform=(lag 1);	
convert epsgrth = epsgrth_tm2 / transform=(lag 2);	
convert epsgrth = epsgrth_tm3 / transform=(lag 3);	
convert epsgrth = epsgrth_tm4 / transform=(lag 4);	

convert actualdiff = actualdiff_tm1 / transform=(lag 1);	
convert actualdiff = actualdiff_tm2 / transform=(lag 2);	
convert actualdiff = actualdiff_tm3 / transform=(lag 3);	
convert actualdiff = actualdiff_tm4 / transform=(lag 4);	

convert fyearq = fyearq_tm1 / transform=(lag 1); 
convert fyearq = fyearq_tm2 / transform=(lag 2); 
convert fyearq = fyearq_tm3 / transform=(lag 3); 
convert fyearq = fyearq_tm4 / transform=(lag 4); 

convert fqtr = fqtr_tm1 / transform=(lag 1); 
convert fqtr = fqtr_tm2 / transform=(lag 2); 
convert fqtr = fqtr_tm3 / transform=(lag 3); 
convert fqtr = fqtr_tm4 / transform=(lag 4); 
	by gvkey;
run;
proc printto; run;

data comp6; set comp5;
if (fyearq - fyearq_tm1 = 0 and fqtr - fqtr_tm1 ne 1) or (fyearq - fyearq_tm1 = 1 and fqtr - fqtr_tm1 ne -3) then 
do;
	atq_tm1 = .;
	ibq_tm1 = .;
	oancfq_tm1 = .;
	ivncfq_tm1 = .;
	epsgrth_tm1 = .;
	actualdiff_tm1 = .;
end;
if (fyearq - fyearq_tm2 = 0 and fqtr - fqtr_tm2 ne 2) or (fyearq - fyearq_tm2 = 1 and fqtr - fqtr_tm2 ne -2) then 
do;
	atq_tm2 = .;
	ibq_tm2 = .;
	oancfq_tm2 = .;
	ivncfq_tm2 = .;
	epsgrth_tm2 = .;
	actualdiff_tm2 = .;
end;
if (fyearq - fyearq_tm3 = 0 and fqtr - fqtr_tm3 ne 3) or (fyearq - fyearq_tm3 = 1 and fqtr - fqtr_tm3 ne -1) then 
do;
	atq_tm3 = .;
	ibq_tm3 = .;
	oancfq_tm3 = .;
	ivncfq_tm3 = .;
	epsgrth_tm3 = .;
	actualdiff_tm3 = .;
end;
if (fyearq - fyearq_tm4 = 1 and fqtr - fqtr_tm4 ne 0) then 
do;
	atq_tm4 = .;
	ibq_tm4 = .;
	oancfq_tm4 = .;
	ivncfq_tm4 = .;
	epsgrth_tm4 = .;
	actualdiff_tm4 = .;
end;
run;


data comp7; set comp6;
year=year(datadate);
EARN = sum(IBQ,IBQ_tm1,IBQ_tm2,IBQ_tm3);
CFO = sum(OANCFQ,OANCFQ_tm1,OANCFQ_tm2,OANCFQ_tm3);
CFI = sum(IVNCFQ,IVNCFQ_tm1,IVNCFQ_tm2,IVNCFQ_tm3);
TACC = EARN - sum(CFO,CFI);
OPER_ACC = EARN - CFO;
INV_ACC = TACC - OPER_ACC;
EP = EARN/MVE;
BM = CEQQ/MVE;
SIZE = log(mve);
if epsgrth > 0 and epsgrth_tm1 > 0 and epsgrth_tm2 > 0 and epsgrth_tm3 > 0 then eps_growth = 1;
else eps_growth = 0;
if (0 lt actualdiff le 0.03) and (0 lt actualdiff_tm1 le 0.03) and (0 lt actualdiff_tm2 le 0.03) and (0 lt actualdiff_tm3 le 0.03) then string = 1;
else string = 0;
run;

* Scale variables by average assets;
data comp8; set comp7; 
AveAssets = mean(atq,atq_tm1,atq_tm2,atq_tm3);
array vars EARN CFO CFI TACC OPER_ACC INV_ACC;
do over vars;
vars = vars / AveAssets;
end;
run;


proc sql;
	create table r2 as select
	*, min(disclosure_date) as disc_date_first format=yymmddn8.
	from restate7d group by gics, category
	order by gics, category, disclosure_date;
quit;

proc sort data = r2; by disclosure_date;
run;

data r3; set r2;
if disclosure_date = disc_date_first then first = 1;
else first = 0;
days_f = intck('weekday',disc_date_first,disclosure_date);
days_l = intck('weekday',lag(disclosure_date),disclosure_date);
date_l = lag(disclosure_date);
run;

* Merge compustat data to restatement firms for all firms in restating industry;
proc sql;
	create table comp9 as select
	a.gics, a.event_id, a.gvkey as gvkey_restate, a.category, a.issue2, a.permno as permno_restate, a.disclosure_date, 
		a.first, a.days_f, a.days_l, a.bhar_annc as rest_bhar_annc, a.bhar_post2 as rest_bhar_post2, 
		a.bhar_post as rest_bhar_post, a.car_annc as rest_car_annc, a.prc_pre, 
		b.cusip, b.gvkey, b.permno, b.ibes_ticker, b.datadate, b.rdq, b.year, b.fyearq, b.fqtr, b.EARN, b.CFO, b.CFI, 
		b.TACC, b.OPER_ACC, b.INV_ACC, b.EPS_GROWTH, b.BM, b.SIZE, b.STRING, b.LEV, b.EP
	from r3 as a left join comp8 as b
	on a.gics = b.gics
	and intnx('month',a.disclosure_date,-12) le b.rdq lt a.disclosure_date 
	order by event_id, gvkey, disclosure_date, datadate;
quit;

* Calculate industry adjusted measures - excluding restating firm;
data comp10; set comp9; by event_id gvkey;
if last.gvkey;
if gvkey_restate ne gvkey; /* exclude restating firm from industry mean*/
run;

proc sql;
	create table comp10b as select
	a.*, b.bhar_annc, b.car_annc, b.bhar_post, b.car_post, b.bhar_post2, b.car_post2
	from comp10 as a left join peers10 as b
	on a.gvkey = b.gvkey
	and a.event_id = b.event_id;
quit;

proc sql;
	create table comp11 as select
	*, EARN - mean(EARN) as EARN_ADJ, CFO - mean(CFO) as CFO_ADJ, CFI - mean(CFI) as CFI_ADJ, TACC - mean(TACC) as TACC_ADJ, 
		OPER_ACC - mean(OPER_ACC) as OPER_ACC_ADJ, INV_ACC - mean(INV_ACC) as INV_ACC_ADJ
	from comp10b group by gics;
quit;

/*
* Calculate analyst forecast revision;
*/
data statsum; set ibes.statsum_epsus (keep = ticker statpers anndats_act measure fpi meanest usfirm);
where ('01jan1994'd le statpers le '31dec2020'd) and (fpi in ("1","2","3")) and (usfirm = 1) and (measure = "EPS") and (ticker ne "");
drop usfirm;
run;


proc sort data = statsum; by ticker anndats_act statpers;
run;

data statsum2; set statsum; by ticker anndats_act;
meanest_tm2 = lag2(meanest);
statpers_tm2 = lag2(statpers);
if first.anndats_act then count = 1;
else count+1;
if count le 2 then meanest_tm2 = .;
revision = meanest - meanest_tm2;
if fpi in ("1","2");
if month(statpers) - month(statpers_tm2) = 2;
format statpers_tm2 yymmddn8.;
run;


proc sql;
	create table comp12 as select
	a.*, b.anndats_act, b.statpers_tm2, b.revision/a.prc_pre as revision, b.fpi
	from comp11 as a left join statsum2 as b
	on a.ibes_ticker = b.ticker
	and intnx('month',a.disclosure_date,-2) lt b.statpers_tm2 lt a.disclosure_date
	order by ibes_ticker, anndats_act;
quit;

proc sort data = comp12; by event_id ibes_ticker anndats_act fpi statpers_tm2;
run;

data comp13; set comp12;; by event_id ibes_ticker anndats_act fpi statpers_tm2;
if last.fpi;
run;

proc sql;
	create table comp14 as select
	*, mean(revision) as comp_revision
	from comp13 group by event_id, ibes_ticker, statpers_tm2;
quit;

data comp15; set comp14;
if fpi = 1;
drop fpi statpers_tm2 anndats_act;
run;


proc sort data = comp15 nodupkey; by event_id gvkey datadate;run;
data gl; set comp15 (keep = gics event_id gvkey_restate permno_restate disclosure_date category issue2 gvkey permno ibes_ticker datadate rdq
									EARN CFO TACC OPER_ACC INV_ACC EARN_ADJ CFO_ADJ TACC_ADJ OPER_ACC_ADJ INV_ACC_ADJ 
									EP BM SIZE EPS_GROWTH STRING REST_BHAR_ANNC  
									BHAR_ANNC REST_CAR_ANNC  
									CAR_ANNC rest_bhar_post2 rest_bhar_post car_post car_post2 
									REVISION COMP_REVISION FIRST DAYS_F DAYS_L LEV);
run;

proc sort data = gl nodupkey; by event_id gvkey datadate;
run;

* Add bearish measure ;
data firm_qtr_bearish; set perm.firm_qtr_bearish;
run;

proc sort data = firm_qtr_bearish nodupkey; by permno mostimportantdateutc;
run;

proc sql;
	create table gl2 as select distinct
	a.*, b.mostimportantdateutc, 
			b.bearish_in_firm_qtr, b.firm_qtr_bearish_ind, b.max_bearish_priority,
			b.firm_qtr_bearish_ind_rmean as bearish_engage, b.bearish_in_roll as bearish_engage_2, b.max_bearish_priority_roll as bearish_priority,
			b.pct_bearish,  (b.n_analysts - b.n_bearish) as n_non_bearish, b.n_analysts, b.n_in, b.n_out,
			b.n_bearish_in, b.n_bearish_out, b.n_bearish, b.n_analysts_on_call, b.avg_questions_bearish , b.avg_answers_bearish as avg_answers_bearish2, b.avg_n_answers, 
			b.avg_n_questions, b.prin_sir
	from gl as a left join firm_qtr_bearish as b
	on a.permno = b.permno
	and a.datadate- 90 le b.mostimportantdateutc lt intnx('weekday',a.rdq,-1)
	order by event_id, gvkey, datadate, mostimportantdateutc;
quit;

proc sort data = gl2; by event_id gvkey datadate mostimportantdateutc;
run;

data gl3; set gl2; by event_id gvkey datadate mostimportantdateutc;
if last.datadate;
run;

proc sort data = gl3 nodupkey; by event_id gvkey datadate mostimportantdateutc;
run;

%let outfile = gleason_cccast;
data perm.&outfile; set gl3;
run;

proc export data = gl3
	outfile = "../data/&outfile..dta"
	dbms = dta
	replace;
run;
