*****************************************************
*													*
*		Calculate the PVIOL following				*
*		the Demerjian and Owns (2016 JAE) paper		*
*		Created by Allen Huang, HKUST				*
*		Last updated: 9/13/2018						*
*													*
*****************************************************;

*****************************************************
*													*
*	1.	Read in Dealscan-Compustat Linktable 		*
*		from Chava and Roberts 2008 JF				*
*													*
*****************************************************;

* To download the Dealscan-Compustat Linktable, 
 go to https://wrds-web.wharton.upenn.edu/wrds/ds/linkingtable/index.cfm?navId=337;

PROC IMPORT DATAFILE= "D:\Dealscan-Compustat Linking Database.xlsx" 
	OUT= dealscan_gvkey_linktable
	DBMS=EXCEL REPLACE;
	SHEET = "link_data"; 
RUN;

* convert gvkey into character;
PROC SQL;
	CREATE TABLE dealscan_gvkey_linktable AS
	SELECT	facid, bcoid, PUT(gvkey, z6.) AS gvkey
	FROM	dealscan_gvkey_linktable;
QUIT;


%LET wrds = wrds.wharton.upenn.edu 4016;
OPTIONS COMAMID = TCP REMOTE = wrds; 
SIGNON USERNAME = _PROMPT_;
RSUBMIT;

	LIBNAME compna '/wrds/comp/sasdata/naa'; 
	LIBNAME dealscan '/wrdslin/tfn/sasdata/dealscan';

	* upload Dealscan-Compustat linktable to WRDS;
	PROC UPLOAD DATA = dealscan_gvkey_linktable OUT = dealscan_gvkey_linktable; RUN;

	*****************************************************
	*													*
	*	2.	Calculate Covenant Financials based on 		*
	*	definition in Demerjian and Owens 2016, Table 4	*
	*													*
	*****************************************************;

	* Quarterly Compustat;
	* make sure gvkey-datadate is unique;
	PROC SORT DATA = compna.fundq 
		(KEEP = gvkey datadate fyearq fqtr atq saleq 
			oibdpq xintq intpny dlcq dlttq intanq ltq actq lctq rectq cheq ltq
			indfmt datafmt popsrc consol)
		OUT = fundq (DROP = indfmt datafmt popsrc consol);
		BY gvkey datadate DESCENDING atq DESCENDING saleq;
		WHERE indfmt = 'INDL' AND datafmt = 'STD' AND popsrc = 'D' AND consol = 'C';
	RUN;

	* for duplicate gvkey-datadate, pick the observations with the largest asset and then sales;
	DATA fundq;
		SET fundq;
		BY gvkey datadate DESCENDING atq DESCENDING saleq;
		IF FIRST.datadate;
	RUN;

	* calculate the quarterly intpnq from the year-to-date intpny, intpnq is not available; 
	PROC SQL;
		CREATE TABLE financials (DROP = intpny) AS
		SELECT		a.*, a.intpny - COALESCE(b.intpny, 0) AS intpnq
		FROM		fundq a LEFT JOIN fundq (KEEP = gvkey fyearq fqtr intpny) b
		ON			a.gvkey = b.gvkey AND a.fyearq = b.fyearq AND a.fqtr = b.fqtr + 1
		ORDER BY	gvkey, datadate;
	QUIT;

	* following notes to Table 4, all flow variables are annualized;
	DATA financials;
		SET financials;
		BY gvkey datadate;
		
		* obtain size and dlcq from last quarter;
		atq_1 = LAG1(atq);
		dlcq_1 = LAG1(dlcq);

		oibdpq_1 = LAG1(oibdpq);
		oibdpq_2 = LAG2(oibdpq);
		oibdpq_3 = LAG3(oibdpq);

		xintq_1 = LAG1(xintq);
		xintq_2 = LAG2(xintq);
		xintq_3 = LAG3(xintq);
		
		intpnq_1 = LAG1(intpnq);
		intpnq_2 = LAG2(intpnq);
		intpnq_3 = LAG3(intpnq);

		IF gvkey ^= LAG1(gvkey) OR datadate - LAG1(datadate) > 360 THEN DO;
			oibdpq_1 = .; xintq_1 = .; intpnq_1 = .; atq_1 = .; dlcq_1 = .;
		END;
		IF gvkey ^= LAG2(gvkey) OR datadate - LAG2(datadate) > 450 THEN DO;
			oibdpq_2 = .; xintq_2 = .; intpnq_2 = .;
		END;
		IF gvkey ^= LAG3(gvkey) OR datadate - LAG3(datadate) > 540 THEN DO;
			oibdpq_3 = .; xintq_3 = .; intpnq_3 = .;
		END;
		
		* calculate annualized flow variables;
		oibdpq_a = SUM(oibdpq, oibdpq_1, oibdpq_2, oibdpq_3);
		xintq_a = SUM(xintq, xintq_1, xintq_2, xintq_3);
		intpnq_a = SUM(intpnq, intpnq_1, intpnq_2, intpnq_3);

		DROP oibdpq oibdpq_1 oibdpq_2 oibdpq_3 xintq xintq_1 xintq_2 xintq_3
			intpnq intpnq_1 intpnq_2 intpnq_3;
	RUN;

	* Annual Compustat;
	* make sure gvkey-datadate is unique;
	PROC SORT DATA = compna.funda 
		(KEEP = gvkey datadate at sale ds xrent indfmt datafmt popsrc consol)		
			OUT = funda (KEEP = gvkey datadate at sale ds xrent);
		BY gvkey datadate DESCENDING at DESCENDING sale;
		WHERE indfmt = 'INDL' AND datafmt = 'STD' AND popsrc = 'D' AND consol = 'C';
	RUN;

	* for duplicate gvkey-datadate, pick the observations with the largest asset and then sales;
	DATA funda;
		SET funda;
		BY gvkey datadate DESCENDING at DESCENDING sale;
		IF FIRST.datadate;
		KEEP gvkey datadate ds xrent;
	RUN;

	PROC SQL UNDO_POLICY = NONE;
		CREATE TABLE financials AS
		SELECT 		a.*, b.ds, b.xrent				
		FROM	 	financials a LEFT JOIN funda b
		ON			a.gvkey = b.gvkey AND b.datadate BETWEEN a.datadate - 400 AND a.datadate
		GROUP BY	a.gvkey, a.datadate
		HAVING		b.datadate = MAX(b.datadate); 
	QUIT;	

	* calculate covenant financials based on definition in Demerjian and Owens (Table 4 and its notes);
	DATA financials;
		SET financials;
		avg_size = MEAN(atq, atq_1);
		roa = oibdpq_a / MEAN(atq, atq_1);
		interest_coverage = oibdpq_a / xintq_a;
		IF interest_coverage < 0 THEN interest_coverage = .;
		cash_interest_coverage = oibdpq_a / intpnq_a;
		IF cash_interest_coverage < 0 THEN cash_interest_coverage = .;
		fixed_charge_coverage = oibdpq_a / SUM(xintq_a, dlcq_1, xrent);
		IF fixed_charge_coverage < 0 THEN fixed_charge_coverage = .;
		debt_service_coverage = oibdpq_a / SUM(xintq_a, dlcq_1);
		IF debt_service_coverage < 0 THEN debt_service_coverage = .;
		debt_to_ebitda = SUM(dlttq, dlcq) / oibdpq_a;
		IF debt_to_ebitda < 0 THEN debt_to_ebitda = .;
		senior_debt_to_ebitda = SUM(dlttq, dlcq, -ds) / oibdpq_a;
		IF senior_debt_to_ebitda < 0 THEN senior_debt_to_ebitda = .;
		leverage = SUM(dlttq, dlcq) / atq;
		IF leverage < 0 THEN leverage = .;
		senior_leverage = SUM(dlttq, dlcq, -ds) / atq;
		IF senior_leverage < 0 THEN senior_leverage = .;
		debt_to_tangible_net_worth = SUM(dlttq, dlcq) / SUM(atq, -intanq, -ltq);
		IF debt_to_tangible_net_worth < 0 THEN debt_to_tangible_net_worth = .;
		debt_to_equity = SUM(dlttq, dlcq) / SUM(atq, -ltq);
		IF debt_to_equity < 0 THEN debt_to_equity = .;
		current_ratio = actq / lctq;
		IF current_ratio < 0 THEN current_ratio = .;
		quick_ratio = SUM(rectq, cheq) / lctq;
		IF quick_ratio < 0 THEN quick_ratio = .;
		ebitda = oibdpq_a * 1000000;
		net_worth = SUM(atq, -ltq) * 1000000;
		tangible_net_worth = SUM(atq, -intanq, -ltq) * 1000000;
		KEEP gvkey datadate fyearq fqtr dlttq avg_size roa
			interest_coverage cash_interest_coverage fixed_charge_coverage debt_service_coverage 
			debt_to_ebitda senior_debt_to_ebitda leverage senior_leverage debt_to_tangible_net_worth 
			debt_to_equity current_ratio quick_ratio ebitda net_worth tangible_net_worth;
	RUN;


	*****************************************************
	*													*
	* 	3. Calculate change in financials from last qtr	*
	*													*
	*****************************************************;

	* calculate the change in each financials and construct the match_firm_sample;
	PROC SQL UNDO_POLICY = NONE;
		CREATE TABLE chg_financials AS
		SELECT 		a.gvkey, a.datadate, a.fyearq, YEAR(a.datadate) AS year,
					a.fqtr, a.dlttq, a.avg_size, a.roa,
					a.interest_coverage / b.interest_coverage AS chg_interest_coverage,
					a.cash_interest_coverage / b.cash_interest_coverage AS chg_cash_interest_coverage,
					a.fixed_charge_coverage / b.fixed_charge_coverage AS chg_fixed_charge_coverage,
					a.debt_service_coverage / b.debt_service_coverage AS chg_debt_service_coverage,
					a.debt_to_ebitda / b.debt_to_ebitda AS chg_debt_to_ebitda,
					a.senior_debt_to_ebitda / b.senior_debt_to_ebitda AS chg_senior_debt_to_ebitda,
					a.leverage / b.leverage AS chg_leverage,
					a.senior_leverage / b.senior_leverage AS chg_senior_leverage,
					a.debt_to_tangible_net_worth / b.debt_to_tangible_net_worth AS chg_debt_to_tangible_net_worth,
					a.debt_to_equity / b.debt_to_equity AS chg_debt_to_equity,
					a.current_ratio / b.current_ratio AS chg_current_ratio,
					a.quick_ratio / b.quick_ratio AS chg_quick_ratio,
					a.ebitda / b.ebitda AS chg_ebitda,
					a.net_worth / b.net_worth AS chg_net_worth,
					a.tangible_net_worth / b.tangible_net_worth AS chg_tangible_net_worth				
		FROM	 	financials a, financials b
		WHERE		a.gvkey = b.gvkey AND b.datadate BETWEEN a.datadate - 200 AND a.datadate - 1
		GROUP BY	a.gvkey, a.datadate
		HAVING		b.datadate = MAX(b.datadate) AND dlttq > 0 AND avg_size ^= . AND roa ^= .
		ORDER BY	year; 
	QUIT;	

	* truncate top and bottom percent following Demerjian and Owens;
	* truncate macro can be downloaded at https://www.allenhuang.org/uploads/2/6/5/5/26555246/truncate.sas;
	%INC '/home/Truncate.sas';

	%truncate(
		var 	= 	chg_interest_coverage chg_cash_interest_coverage chg_fixed_charge_coverage 
					chg_debt_service_coverage chg_debt_to_ebitda chg_senior_debt_to_ebitda 
					chg_leverage chg_senior_leverage chg_debt_to_tangible_net_worth
					chg_debt_to_equity chg_current_ratio chg_quick_ratio 
					chg_ebitda chg_net_worth chg_tangible_net_worth,
		by_var 	= , 
		left 	= 1, 
		right 	= 99, 
		input 	= chg_financials,
		output 	= chg_financials);

	* remove observation with any missing variables following Demerjian and Owens;
	DATA chg_financials;
		SET chg_financials;
		WHERE chg_interest_coverage ^= . AND chg_cash_interest_coverage ^= . AND 
			chg_fixed_charge_coverage ^= . AND chg_debt_service_coverage ^= . AND
			chg_debt_to_ebitda ^= . AND chg_senior_debt_to_ebitda ^= . AND chg_leverage ^= . AND 
			chg_senior_leverage ^= . AND chg_debt_to_tangible_net_worth ^= . AND 
			chg_debt_to_equity ^= . AND chg_current_ratio ^= . AND chg_quick_ratio ^= . 
			AND chg_ebitda ^= . AND chg_net_worth ^= . AND chg_tangible_net_worth ^= .;
	RUN;

	
	*****************************************************
	*													*
	*	4.	Sort firms in 12 size-profitability bins	*
	*													*
	*****************************************************;

	* random sample draw will be in one of the 12 size-profitability bins;
	* bins are formed in each year by first sorting firms by size into quartiles;
	* firms in each year-size quartile are then sorted into terciles based on ROA;

	* rank size quartiles within each year;
	PROC RANK DATA = chg_financials OUT = size_roa_bins GROUPS = 4 TIES = MEAN;
		VAR avg_size;
		RANKS size_r4;
		BY year;
	RUN;

	* get the cutoff number for each size, used to assign loans to size portfolio;
	PROC UNIVARIATE DATA = size_roa_bins NOPRINT;
		BY year;
		VAR avg_size;
		OUTPUT OUT = size_cutoff PCTLPTS = 25 50 75
			PCTLPRE = size
			PCTLNAME = _25 _50 _75;
	RUN;

	PROC SORT DATA = size_roa_bins;
		BY year size_r4;
	RUN;

	* rank ROA tercile within each year-size portfolio;
	PROC RANK DATA = size_roa_bins OUT = size_roa_bins GROUPS = 3 TIES = MEAN;
		VAR roa;
		RANKS roa_r3;
		BY year size_r4;
	RUN;

	* get the cutoff number for each roa within each year-size, used to assign loans to roa portfolio;
	PROC UNIVARIATE DATA = size_roa_bins NOPRINT;
		BY year size_r4;
		VAR roa;
		OUTPUT OUT = roa_cutoff PCTLPTS = 33.33333333 66.66666666
			PCTLPRE = roa
			PCTLNAME = _33 _66;
	RUN;

	DATA size_roa_bins;
		SET size_roa_bins;
		roa_r3 = roa_r3 + 1;
		size_r4 = size_r4 + 1;
	RUN;


	*****************************************************
	*													*
	* 	5. Creating match-firm sample for random draw	*
	*	each firm can be matched to a firm in t-1 or t-2*
	*													*
	*****************************************************;

	DATA roa_r3; DO roa_r3 = 1 TO 3; OUTPUT; END; RUN;
	DATA size_r4; DO size_r4 = 1 TO 4; OUTPUT; END; RUN;

	PROC SQL; 
		CREATE TABLE year_roa_size AS
		SELECT	*				
		FROM	(SELECT UNIQUE YEAR(dealactivedate) AS loanyear FROM dealscan.package), size_r4, roa_r3;
	QUIT;

	* for each year t, the match firm can be in year t-1 or t-2;
	PROC SQL;
		CREATE TABLE match_firm_sample AS
		SELECT		a.*, b.*
		FROM		year_roa_size a, size_roa_bins b
		WHERE		b.year BETWEEN a.loanyear - 1 AND a.loanyear - 2
					AND a.size_r4 = b.size_r4 AND a.roa_r3 = b.roa_r3
		ORDER BY	loanyear, size_r4, roa_r3;
	QUIT;

	* assign a uniuqe number to each obs in the match firm portfolio, used to random draw later;
	DATA match_firm_sample;
		SET match_firm_sample;
		BY loanyear size_r4 roa_r3;
		IF FIRST.roa_r3 THEN portfolio_count = 0;
		portfolio_count + 1;
	RUN;

	* number of observations in each year-size-roa portfolio, used for random draw;
	PROC SQL;
		CREATE TABLE max_portfolio_count AS
		SELECT		loanyear, size_r4, roa_r3, MAX(portfolio_count) AS max_portfolio_count
		FROM		match_firm_sample
		GROUP BY	loanyear, size_r4, roa_r3;
	QUIT;

	* turn the observation into firm-covenant level;
	PROC TRANSPOSE DATA = match_firm_sample (DROP = gvkey year datadate fyearq fqtr dlttq avg_size roa)
			OUT = match_firm_sample;
		BY loanyear size_r4 roa_r3 portfolio_count;
	RUN;
		
	DATA match_firm_sample;
		SET match_firm_sample;
		IF _name_ = 'chg_interest_coverage' THEN covenantnum = 1;
		IF _name_ = 'chg_cash_interest_coverage' THEN covenantnum = 2;
		IF _name_ = 'chg_fixed_charge_coverage' THEN covenantnum = 3;
		IF _name_ = 'chg_debt_service_coverage' THEN covenantnum = 4;
		IF _name_ = 'chg_debt_to_ebitda' THEN covenantnum = -5;
		IF _name_ = 'chg_senior_debt_to_ebitda' THEN covenantnum = -6;
		IF _name_ = 'chg_leverage' THEN covenantnum = -7;
		IF _name_ = 'chg_senior_leverage' THEN covenantnum = -8;
		IF _name_ = 'chg_debt_to_tangible_net_worth' THEN covenantnum = -9;
		IF _name_ = 'chg_debt_to_equity' THEN covenantnum = -10;
		IF _name_ = 'chg_current_ratio'  THEN covenantnum = 11;
		IF _name_ = 'chg_quick_ratio' THEN covenantnum = 12;
		IF _name_ = 'chg_ebitda' THEN covenantnum = 13;
		IF _name_ = 'chg_net_worth' THEN covenantnum = 14;
		IF _name_ = 'chg_tangible_net_worth' THEN covenantnum = 15;
		RENAME col1 = match_firm_change;
		KEEP loanyear size_r4 roa_r3 portfolio_count covenantnum col1;
		WHERE col1 ^= .;
	RUN;


	*****************************************************
	*													*
	* 		6.	Obtain Dealscan Covenants				*
	*													*
	*****************************************************;


	* process loan covenants raw data;
	* assign covenant number, 1-15 from Demerjian and Owens paper;
	* positive covenant number is for covenants with minimin;
	* negative covenant number is for covenants with maximum;
	DATA loan_terms;
		SET dealscan.financialcovenant (KEEP = packageid CovenantType initialratio initialamt)
			dealscan.networthcovenant (KEEP = packageid CovenantType baseamt);
		IF CovenantType IN ('Min. Interest Coverage', 'Min. Cash Interest Coverage', 
		'Min. Fixed Charge Coverage', 'Min. Debt Service Coverage', 'Max. Debt to EBITDA',
		'Max. Senior Debt to EBITDA', 'Max. Leverage ratio', 'Max. Senior Leverage', 
		'Max. Debt to Tangible Net Worth', 'Max. Debt to Equity', 'Min. Current Ratio',
		'Min. Quick Ratio', 'Min. EBITDA', 'Net Worth', 'Tangible Net Worth');
		IF CovenantType = 'Min. Interest Coverage' THEN covenantnum = 1;
		IF CovenantType = 'Min. Cash Interest Coverage' THEN covenantnum = 2;
		IF CovenantType = 'Min. Fixed Charge Coverage' THEN covenantnum = 3;
		IF CovenantType = 'Min. Debt Service Coverage' THEN covenantnum = 4;
		IF CovenantType = 'Max. Debt to EBITDA' THEN covenantnum = -5;
		IF CovenantType = 'Max. Senior Debt to EBITDA' THEN covenantnum = -6;
		IF CovenantType = 'Max. Leverage ratio' THEN covenantnum = -7;
		IF CovenantType = 'Max. Senior Leverage' THEN covenantnum = -8;
		IF CovenantType = 'Max. Debt to Tangible Net Worth' THEN covenantnum = -9;
		IF CovenantType = 'Max. Debt to Equity' THEN covenantnum = -10;
		IF CovenantType = 'Min. Current Ratio'  THEN covenantnum = 11;
		IF CovenantType = 'Min. Quick Ratio' THEN covenantnum = 12;
		IF CovenantType = 'Min. EBITDA' THEN covenantnum = 13;
		IF CovenantType = 'Net Worth' THEN covenantnum = 14;
		IF CovenantType = 'Tangible Net Worth' THEN covenantnum = 15;
		IF ABS(covenantnum) <= 12 THEN threshold = initialratio;
		IF covenantnum = 13 THEN threshold = initialamt;
		IF covenantnum >= 14 THEN threshold = baseamt;
		IF threshold ^= . AND packageid ^= .;
		DROP initialratio initialamt baseamt;
	RUN;

	* match each loan packageid to a gvkey using the dealscan-compustat linktable;
	PROC SQL;
		CREATE TABLE loan_identifier AS
		SELECT		UNIQUE a.packageid, a.DealActiveDate, gvkey, FacilityAmt, maturity
		FROM		dealscan.package a, dealscan.facility b, dealscan_gvkey_linktable c
		WHERE		a.packageid = b.packageid 
					AND b.borrowercompanyid = c.bcoid AND b.FacilityID = c.facid
		ORDER BY	packageid, dealactivedate, facilityamt DESC, maturity DESC, gvkey;
	QUIT;
	* ensure that each packageid is matched to one gvkey;
	DATA loan_identifier;
		SET loan_identifier;
		BY packageid dealactivedate DESCENDING facilityamt DESCENDING maturity gvkey;
		IF FIRST.packageid;
	RUN;

	PROC SQL;
		CREATE TABLE loan_terms AS
		SELECT	a.*, b.gvkey, b.dealactivedate
		FROM	loan_terms a, loan_identifier b
		WHERE	a.packageid = b.packageid;
	QUIT;


	*****************************************************
	*													*
	* 7.Obtain the initial financials of the covenant	*
	*													*
	*****************************************************;

	* obtain the current covenant financials of the borrower;
	PROC SORT DATA = financials;
		BY gvkey datadate;
	RUN;

	PROC TRANSPOSE DATA = financials (DROP = fyearq fqtr dlttq avg_size roa) 
			OUT =  initial_financial;
		BY gvkey datadate;
	RUN;

	DATA initial_financial;
		SET initial_financial;
		IF _name_ = 'interest_coverage' THEN covenantnum = 1;
		IF _name_ = 'cash_interest_coverage' THEN covenantnum = 2;
		IF _name_ = 'fixed_charge_coverage' THEN covenantnum = 3;
		IF _name_ = 'debt_service_coverage' THEN covenantnum = 4;
		IF _name_ = 'debt_to_ebitda' THEN covenantnum = -5;
		IF _name_ = 'senior_debt_to_ebitda' THEN covenantnum = -6;
		IF _name_ = 'leverage' THEN covenantnum = -7;
		IF _name_ = 'senior_leverage' THEN covenantnum = -8;
		IF _name_ = 'debt_to_tangible_net_worth' THEN covenantnum = -9;
		IF _name_ = 'debt_to_equity' THEN covenantnum = -10;
		IF _name_ = 'current_ratio'  THEN covenantnum = 11;
		IF _name_ = 'quick_ratio' THEN covenantnum = 12;
		IF _name_ = 'ebitda' THEN covenantnum = 13;
		IF _name_ = 'net_worth' THEN covenantnum = 14;
		IF _name_ = 'tangible_net_worth' THEN covenantnum = 15;
		RENAME col1 = initial_financial;
		WHERE col1 ^= .;
	RUN;

	* obtain the initial financial of each covenant as of deal active date;
	PROC SQL;
		CREATE TABLE loan_terms AS
		SELECT		a.*, initial_financial
		FROM		loan_terms a, initial_financial b
		WHERE		a.gvkey = b.gvkey AND a.covenantnum = b.covenantnum
					AND b.datadate BETWEEN a.dealactivedate - 200 AND a.dealactivedate
		GROUP BY	a.packageid, a.gvkey, a.dealactivedate, a.covenantnum
		HAVING		b.datadate = MAX(b.datadate);
	QUIT;

	*****************************************************
	*													*
	*	 8. Assign each loan to a size-roa portfolio	*
	*													*
	*****************************************************;

	* Assign each firm-qtr to a size portfolio based on avg_size;
	PROC SQL;
		CREATE TABLE size_roa_rank AS
		SELECT	a.gvkey, a.datadate, roa,
				CASE 	WHEN avg_size <= size_25 THEN 1
						WHEN avg_size <= size_50 THEN 2
						WHEN avg_size <= size_75 THEN 3
						ELSE 4
				END AS size_r4
		FROM	financials a, size_cutoff b
		WHERE	YEAR(a.datadate) = b.year AND avg_size ^= . AND roa ^= .;
	QUIT;

	* Assign each firm-qtr to a ROA portfolio based on ROA;
	PROC SQL;
		CREATE TABLE size_roa_rank AS
		SELECT	a.gvkey, a.datadate, a.size_r4, 
				CASE 	WHEN roa <= roa_33 THEN 1
						WHEN roa <= roa_66 THEN 2
						ELSE 3
				END AS roa_r3
		FROM	size_roa_rank a, roa_cutoff b
		WHERE	YEAR(a.datadate) = b.year AND a.size_r4 = b.size_r4;
	QUIT;

	* obtain the current size-roa bin of each loan;
	PROC SQL;
		CREATE TABLE loan_terms AS
		SELECT		a.packageid, YEAR(dealactivedate) AS loanyear, roa_r3, size_r4,
					covenantnum, initial_financial, threshold
		FROM		loan_terms a, size_roa_rank b
		WHERE		a.gvkey = b.gvkey 
					AND b.datadate BETWEEN a.dealactivedate - 200 AND a.dealactivedate
		GROUP BY	a.packageid, a.gvkey, a.dealactivedate, a.covenantnum
		HAVING		b.datadate = MAX(b.datadate);
	QUIT;

	
	*****************************************************
	*													*
	*	 9. Run simulation to calculate probability of 	*
	*				default in the next year			*
	*													*
	*****************************************************;

	* run simulations;
	%MACRO iterations(max_iteration);
		* number of random numbers needed in each iteration;
		* this is the number of total covenant;
		* in each iteration, generate a random number for each covenant;
		%LET fileid = %SYSFUNC(OPEN(loan_terms));
		%LET num_of_obs = %SYSFUNC(ATTRN(&fileid, NOBS));
		%LET filerc = %SYSFUNC(CLOSE(&fileid));
		
		%DO i = 1 %TO &max_iteration;
			%PUT On &i of &max_iteration iteration;
			* generate random number;
			DATA random_numbers (DROP = seed_1 i);
				RETAIN seed_1 0;
				DO i = 1 TO &num_of_obs;
					CALL RANUNI(seed_1, ran);
					OUTPUT;
				END;
			RUN;
			
			* merge random number with loan covenants;
			DATA random;
				MERGE loan_terms random_numbers;
			RUN;

			
			/*
			* assess whether one covenant is violated;
			* for debugging, to investigate which covenants are violated most often and why;
			PROC SQL;
				CREATE TABLE random AS
				SELECT		a.packageid, &i AS iteration, a.covenantnum,
							initial_financial, match_firm_change, 
							initial_financial * match_firm_change AS expected_financial, threshold,
							(initial_financial * match_firm_change  < threshold AND a.covenantnum > 0
							OR initial_financial * match_firm_change > threshold AND a.covenantnum < 0) AS violation
				FROM		random a, max_portfolio_count b, match_firm_sample c
				WHERE		a.loanyear = b.loanyear AND a.size_r4 = b.size_r4 AND a.roa_r3 = b.roa_r3
							AND a.loanyear = c.loanyear AND a.size_r4 = c.size_r4 AND a.roa_r3 = c.roa_r3
							AND a.covenantnum = c.covenantnum
							AND FLOOR(a.ran * b.max_portfolio_count) + 1 = c.portfolio_count
				GROUP BY	packageid;
			QUIT;
			*/

			* assess whether any covenant is violated for a loan;
			PROC SQL;
				CREATE TABLE random AS
				SELECT		a.packageid, &i AS iteration, 
							MAX(initial_financial * match_firm_change  < threshold AND a.covenantnum > 0
							OR initial_financial * match_firm_change > threshold AND a.covenantnum < 0) AS violation
				FROM		random a, max_portfolio_count b, match_firm_sample c
				WHERE		a.loanyear = b.loanyear AND a.size_r4 = b.size_r4 AND a.roa_r3 = b.roa_r3
							AND a.loanyear = c.loanyear AND a.size_r4 = c.size_r4 AND a.roa_r3 = c.roa_r3
							AND a.covenantnum = c.covenantnum
							AND FLOOR(a.ran * b.max_portfolio_count) + 1 = c.portfolio_count
				GROUP BY	packageid;
			QUIT;

			* save simulation results;
			%IF &i = 1 %THEN %DO;
				DATA simulation_outcome;
					SET random;
				RUN;
			%END;
			%ELSE %DO;
				DATA simulation_outcome;
					SET simulation_outcome random;
				RUN;
			%END;
		%END;
	%MEND iterations;
	* set the iteration number here;
	%iterations(1000);

	* calculate the probability that each loan defaults;
	PROC SQL;
		CREATE TABLE covenant_violation AS
		SELECT		packageid, AVG(violation) AS pviol
		FROM		simulation_outcome
		GROUP BY	packageid;
	QUIT;

	PROC DOWNLOAD DATA = covenant_violation OUT = covenant_violation; RUN;
ENDRSUBMIT;


/*
* for debugging, to investigate which covenants are violated most often and why;

PROC SQL;
	CREATE TABLE violation_pct AS
	SELECT		covenantnum, AVG(violation) AS violation_pct
	FROM		simulation_outcome
	GROUP BY	covenantnum
	ORDER BY	violation_pct DESC;
QUIT;

DATA sample;
	SET simulation_outcome;
	WHERE covenantnum = 13;
RUN;
*/