Skip to content

brycewang-stanford/Auto-Empirical-Research-Skills / skills/18-jusi-aalto-stata-accounting-research

stata-accounting-research

Unverified

STATA code pattern library for empirical archival accounting research. Provides tested syntax from 126 peer-reviewed JAR (Journal of Accounting Research) replication files (2017-2025). Use when the user asks procedural questions like "How do I implement [method]?" or "Show me code for [technique]" — including: entropy balancing, propensity score matching (PSM), difference-in-differences (DiD), regression discontinuity (RDD), instrumental variables (IV), event studies (CAR/BHAR), survival analysis, Fama-MacBeth regressions, bootstrap, quantile regression, reghdfe/xtreg/areg, clustering standard errors, fixed effects, esttab/outreg2 table formatting, winsorization, leads/lags. Users can specify their variables (e.g., treatment, outcomes, controls) and receive adapted syntax. NOTE: This skill provides code patterns from published papers, not research design advice.

FileKindSize
README-original.mdmarkdown2.9 KB
SKILL.mdskill md4.8 KB
references/JAR_55_bbcl.doother59.1 KB
references/JAR_55_chaplinksy-hanley-moon.doother46.3 KB
references/JAR_55_clm.doother95.1 KB
references/JAR_55_elw.doother58.0 KB
references/JAR_55_hlsw.doother104.1 KB
references/JAR_55_kks.doother365.5 KB
references/JAR_55_lzz.doother25.5 KB
references/JAR_55_madsen.doother307.5 KB
references/JAR_55_mr.doother41.5 KB
references/JAR_56_al.doother75.3 KB
references/JAR_56_bch.doother13.5 KB
references/JAR_56_blv.doother24.6 KB
references/JAR_56_csmw.doother27.6 KB
references/JAR_56_en.doother21.2 KB
references/JAR_56_granja.doother107.9 KB
references/JAR_56_htw.doother18.6 KB
references/JAR_56_kmt.doother5.6 KB
references/JAR_56_ksvw.doother8.0 KB
references/JAR_56_llz.doother3.1 KB
references/JAR_56_ls.doother147.5 KB
references/JAR_56_lv2018.doother123.7 KB
references/JAR_57_bht.doother24.4 KB
references/JAR_57_bl.doother22.6 KB
references/JAR_57_bw.doother263.2 KB
references/JAR_57_cct.doother347.1 KB
references/JAR_57_cgw.doother195.8 KB
references/JAR_57_hcp.doother38.0 KB
references/JAR_57_honigsberg.doother99.5 KB
references/JAR_57_lehmann.doother112.8 KB
references/JAR_57_lm.doother85.5 KB
references/JAR_58_bsz.doother36.9 KB
references/JAR_58_ckm.doother52.3 KB
references/JAR_58_eyring.doother58.1 KB
references/JAR_58_gj.doother187.5 KB
references/JAR_58_gsz.doother121.6 KB
references/JAR_58_htyz.doother30.9 KB
references/JAR_58_jansen.doother99.5 KB
references/JAR_58_joshi.doother43.0 KB
references/JAR_58_kvy.doother19.5 KB
references/JAR_58_msz.doother35.0 KB
references/JAR_58_rauter.doother433.2 KB
references/JAR_58_wy.doother64.5 KB
references/JAR_58_zz.doother125.5 KB
references/JAR_59_breuer.doother826.9 KB
references/JAR_59_ctv.doother383.7 KB
references/JAR_59_dess.doother78.4 KB
references/JAR_59_hmo.doother351.6 KB
references/JAR_60_abkp.doother129.9 KB
references/JAR_60_alv.doother35.4 KB
references/JAR_60_at.doother38.5 KB
references/JAR_60_barrios.doother119.6 KB
references/JAR_60_bbin.doother72.9 KB
references/JAR_60_bdem.doother11.6 KB
references/JAR_60_bl.doother43.5 KB
references/JAR_60_bmsw.doother7.7 KB
references/JAR_60_cc.doother20.1 KB
references/JAR_60_cpss.doother15.4 KB
references/JAR_60_cstv.doother175.3 KB
references/JAR_60_dg.doother19.9 KB
references/JAR_60_duguay.doother8.0 KB
references/JAR_60_fhl.doother71.3 KB
references/JAR_60_gls.doother37.0 KB
references/JAR_60_hmryz.doother28.6 KB
references/JAR_60_hst.doother18.3 KB
references/JAR_60_kt.doother10.4 KB
references/JAR_60_mliu.doother28.3 KB
references/JAR_60_nrwx.doother14.6 KB
references/JAR_60_ptwy.doother26.6 KB
references/JAR_60_skim.doother14.7 KB
references/JAR_61_bcdr.doother524.9 KB
references/JAR_61_bkr.doother51.6 KB
references/JAR_61_bls.doother79.2 KB
references/JAR_61_bo.doother29.7 KB
references/JAR_61_ccm.doother48.7 KB
references/JAR_61_cdss.doother3.9 KB
references/JAR_61_cfhll.doother47.1 KB
references/JAR_61_cfmp.doother12.7 KB
references/JAR_61_ckor.doother77.5 KB
references/JAR_61_cllw.doother11.2 KB
references/JAR_61_clm.doother11.0 KB
references/JAR_61_deller.doother368.9 KB
references/JAR_61_dlrtw.doother30.6 KB
references/JAR_61_dlz.doother13.6 KB
references/JAR_61_drs.doother15.8 KB
references/JAR_61_ds.doother197.5 KB
references/JAR_61_glt.doother1.9 KB
references/JAR_61_gyz.doother7.5 KB
references/JAR_61_ll.doother89.3 KB
references/JAR_61_lszz.doother15.9 KB
references/JAR_61_os.doother128.9 KB
references/JAR_61_zureich.doother22.2 KB
references/JAR_62_1_bdsy_banerjee_etal.doother6.6 KB
references/JAR_62_1_bhs_bouwens_etal.doother59.7 KB
references/JAR_62_1_gsz_gu_sun_zhou.doother46.6 KB
references/JAR_62_2_dgzz_defranco_etal.doother1.8 KB
references/JAR_62_2_gmp_grewal_etal.doother37.2 KB
references/JAR_62_2_xyz_xin_etal.doother25.3 KB
references/JAR_62_3_bht_bloomfield_etal.doother16.0 KB
references/JAR_62_3_ccz_cheynel_etal.doother23.3 KB
references/JAR_62_3_kim.doother2.6 KB
references/JAR_62_3_rr_raghunandan_ruchti.doother15.5 KB
references/JAR_62_4_bd_breuer_dehaan.doother15.3 KB
references/JAR_62_4_cc_cadman_carrizosa.doother20.1 KB
references/JAR_62_4_kmsv_kohler_etal.doother91.5 KB
references/JAR_62_4_mps_marra_etal.doother68.0 KB
references/JAR_62_4_raghunandan.doother50.0 KB
references/JAR_62_4_stv_sran_etal.doother41.1 KB
references/JAR_62_4_xu.doother49.4 KB
references/JAR_62_5_aov_abraham_etal.doother418.9 KB
references/JAR_62_5_gibbons.doother80.1 KB
references/JAR_62_5_jiang.doother114.3 KB
references/JAR_62_5_kstz_krueger_etal.doother26.7 KB
references/JAR_62_5_llz_li_lou_zhang.doother6.2 KB
references/JAR_62_5_lrst_lins_etal.doother66.2 KB
references/JAR_62_5_lswy_lin_etal.doother85.2 KB
references/JAR_62_5_lw_li_wang.doother16.6 KB
references/JAR_63_1_blmv_bastiaansen_etal.doother18.5 KB
references/JAR_63_1_cvw_chen_etal.doother61.2 KB
references/JAR_63_1_fgr_frankel_etal.doother30.3 KB
references/JAR_63_1_hrsx_huang_etal.doother35.5 KB
references/JAR_63_1_jia.doother12.0 KB
references/JAR_63_2_bcrw_bakke_etal.doother271.3 KB
references/JAR_63_2_dlr_desimone_etal.doother83.5 KB
references/JAR_63_2_mqtz_mao_etal.doother94.2 KB
references/JAR_63_2_npstv_nessa_etal.doother7.0 KB
references/JAR_63_3_breuer.doother51.4 KB
references/REFERENCES.mdmarkdown30.7 KB

references/JAR_58_gsz.do

View on GitHub
/******************************************************************************/

/* This file cleans SDC data. */
/*
Date: 2020.03.13


/******************************/		
Input files:
/******************************/		

	- (1) /Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/SDC/SDC_IssuerSaleDate_19902014.csv.
	
cusip_6	saledate	county	issuer	ratingagency
729773	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729778	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729780	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729783	01/02/90	Hennepin	Plymouth City-Minnesota	SP
864048	01/02/90	Door	Sturgeon Bay-Wisconsin	
864056	01/02/90	Door	Sturgeon Bay-Wisconsin	
944048	01/02/90	Kosciusko	Wawasee Comm School Corp	
944050	01/02/90	Kosciusko	Wawasee Comm School Corp	
947644	01/02/90	Weber	Weber Co-Utah	
947648	01/02/90	Weber	Weber Co-Utah	
947651	01/02/90	Weber	Weber Co-Utah	
947659	01/02/90	Weber	Weber Co-Utah	
004590	01/03/90	Franklin-Hardin	Ackley City-Iowa	
004606	01/03/90	Franklin-Hardin	Ackley City-Iowa	
004613	01/03/90	Franklin-Hardin	Ackley City-Iowa	
048339	01/03/90	Atlantic	Atlantic City-New Jersey	
048343	01/03/90	Atlantic	Atlantic City-New Jersey	
048339	01/03/90	Atlantic	Atlantic City-New Jersey	MOODY;SP
048343	01/03/90	Atlantic	Atlantic City-New Jersey	MOODY;SP
	
	
*/
/******************************************************************************/

clear all 
set more off 
set matsize 750

// cd "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\SDC"
cd "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/SDC"
global data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Analysis/Round 2 new"



***********************************************************************/
/* SDC data cleaning */
**********************************************************************/



use SDC_IssuerSaleDate_19902014, clear // created by 01_sdc_issuer.R


* Sale date
gen year = substr(saledate, 7,2) 
gen month = substr(saledate, 1,2)
gen day = substr(saledate, 4,2)

destring year, replace
destring month, replace
destring day, replace
replace year = 2000+year if year <=14
replace year = 1900+year if year >=90 & year <=99 & year !=.

tab year
tab month
tab day
count 
*857,323

drop saledate
gen saledate = mdy(month, day, year)
format saledate %td

* Ratinng agency
replace ratingagency = lower(ratingagency)
tab ratingagency

gen moody_rated = strpos(ratingagency, "moody")>0
gen sp_rated = strpos(ratingagency, "sp")>0
gen fitch_rated = strpos(ratingagency, "fitch")>0
count if moody_rated == 0 & sp_rated == 0 & fitch_rated == 0 & rateddealflag == "Yes" 
*0

gen rated = rateddealflag == "Yes"

* Credit enhancement
gen insurance = creditenhancetype != ""

* Callable
gen call = callable == "Yes"
tab call

* Go vs reveneu
gen go = security == "GO"


* amount of issue
destring amountofissue, replace force 
*forced is used because missing amount is denoted by NA.


* Maturity

gen yearm = substr(maxmaturity, 1,4)
gen monthm = substr(maxmaturity, 6,2)
gen daym = substr(maxmaturity,9,2)
destring monthm daym, replace
destring yearm, replace force
tab yearm
gen max_maturity = mdy(monthm, daym, yearm)
replace max_maturity = . if maxmaturity == "NA" | yearm <1990
format max_maturity %td
drop yearm monthm daym




* Underwriter
gen underwriter = leadmng 
*857,323










/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
* Generate issuer-year level data
/**********************************************************************/

preserve

gen year1 = year
replace year1 = year - 1 if month <=6

drop if year <= 1990 & month <=6
drop if year >=2014 & month>=7
tab year1
drop year
rename year1 year

* Compute average issuer-year rating, insurance, call, go
sort cusip_6 year

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6 year: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6 year: egen first_offer_date = min(saledate)
by cusip_6 year: egen last_offer_date = max(saledate)
by cusip_6 year: gen num_offer = _N
format first_offer_date last_offer_date %td
by cusip_6 year: egen nvals = total(amountofissue)
gen amountofissue0=amountofissue
replace amountofissue = nvals
drop nvals

* Max maturity

by cusip_6 year: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals
format max_maturity %td



* Drop duplicates
* Keep issuers that issue the highest amount of debt and highest frequency of debt
gsort cusip_6 year issuerdscrp -saledate state uop1
by cusip_6 year issuerdscrp: egen nvals = total(amountofissue0) 
** total amount
by cusip_6 year issuerdscrp: gen nvals1 = _N 
** total number
by cusip_6 year issuerdscrp: gen nvals0 =_n ==1

keep if nvals0 == 1
drop nvals0
gsort cusip_6 year -saledate
by cusip_6 year: egen nvals2 = max(nvals) 
** max total amount of debt by cusip_6 year and issuerdscrp
keep if nvals == nvals2 
** keep issuers that issue the highest total amount of debt.
duplicates report cusip_6 year


by cusip_6 year: egen nvals3 = max(nvals1)
keep if nvals3 == nvals1
duplicates report cusip_6 year


sort cusip_6 year state uop1 issuerdscrp
by cusip_6 year: gen issuerdscrp1 = issuerdscrp[_n+1]
sort cusip_6 year 



keep cusip_6 year moody_rated rated sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state amountofissue issuerdscrp issuerdscrp1 uop1 underwriter issuer
sort cusip_6 year state uop1 issuerdscrp issuerdscrp1
cap drop nvals*
by cusip_6 year: gen nvals = _n==1
keep if nvals == 1
drop nvals

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable rated "issuer is rated"
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"
		label variable amountofissue "total annual issuance amount"
		label variable uop1 "purpose"
		label variable underwriter "underwriter"
		label variable issuer "issuer name"

count 
* 387,174 issuer-year


saveold "${data}/sdc_issuer_year20190530.dta", replace version(12)
restore
















/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/* Issuer level: debt outstanding */
/**********************************************************************/


preserve

keep if saledate <= mdy(06,30,2010) & max_maturity >= mdy(06,30,2010) & max_maturity !=. 
keep if rated ==1
sort cusip_6

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6: egen first_offer_date = min(saledate)
by cusip_6: egen last_offer_date = max(saledate)
by cusip_6: gen num_offer = _N
format first_offer_date last_offer_date %td

* Max maturity

by cusip_6: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals

duplicates drop cusip_6, force
keep cusip_6 moody_rated  sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"

count 
*39,158

saveold "${data}/sdc_debtoustanding_20190530.dta", replace version(12) // only sample itself is used. 

restore





/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/* access market */


preserve

keep if saledate <= mdy(06,30,2010) & saledate >= mdy(07,01,2006) 
sort cusip_6

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6: egen first_offer_date = min(saledate)
by cusip_6: egen last_offer_date = max(saledate)
by cusip_6: gen num_offer = _N
format first_offer_date last_offer_date %td
by cusip_6: egen nvals = total(amountofissue)
gen amountofissue0=amountofissue
replace amountofissue = nvals
drop nvals

* Max maturity

by cusip_6: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals

* Drop duplicates
* Keep issuers that issue the highest amount of debt and highest frequency of debt
gsort cusip_6 issuerdscrp -saledate state uop1 issuer
by cusip_6 issuerdscrp: egen nvals = total(amountofissue0) 
** total amount
by cusip_6 issuerdscrp: gen nvals1 = _N 
** total number
by cusip_6 issuerdscrp: gen nvals0 =_n ==1

keep if nvals0 == 1
drop nvals0
gsort cusip_6 issuerdscrp -saledate state uop1 issuer
by cusip_6: egen nvals2 = max(nvals) 
** max total amount of debt by cusip_6 and issuerdscrp
keep if nvals == nvals2 
** keep issuers that issue the highest total amount of debt.
duplicates report cusip_6
/*
--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |        32362             0
        2 |           18             9
--------------------------------------
*/

by cusip_6: egen nvals3 = max(nvals1)
keep if nvals3 == nvals1
duplicates report cusip_6
/*
--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |        32362             0
        2 |           18             9
--------------------------------------
*/
sort cusip_6 year state uop1 issuerdscrp issuer
by cusip_6 (year state uop1 issuerdscrp issuer): gen issuerdscrp1 = issuerdscrp[_n+1]
cap drop nvals
by cusip_6: gen nvals = _n==1
keep if nvals ==1
drop nvals


keep cusip_6 rated moody_rated sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state issuerdscrp issuerdscrp1 amountofissue uop1  underwriter issuer

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"
		label variable amountofissue "total annual issuance amount"

count 
*32,371


saveold "${data}/sdc_accessmarket_20190530.dta", replace version(12)

restore










/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/

* Issue level

preserve

keep if saledate <= mdy(06,30,2010) & saledate >= mdy(07,01,2006) 
sort cusip_6 saledate 
by cusip_6 saledate: gen nvals =_n==1
keep if nvals ==1
drop nvals

gen issue_id = _n
keep cusip_6 saledate year

saveold "${data}/sdc_issuereg_20190304.dta", replace version(12)

restore









/******************************************************************************/

/*


This file creates the data for financial disclosures. 
Date: 2020.03.13

	
*/


global Data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/Timeliness"
global Data "/Users/szho/Documents/Temp"


* Change formats
forvalues i=2009(1)2014{
	use "${Data}/cd`i'.dta", clear
	saveold "${Data}/cd`i'.dta", replace version(12)
}


























/******************************************************************************/

* STEP 1: CREATE FINANCIAL DISCLOSURE DATA

/******************************************************************************/


* Other disclosures that are CAFR

import excel "${Data}/OtherVoluntaryFinancialDisclosures.xlsx", sheet("Sheet1") firstrow clear
keep othervoluntarydisclosure CAFR
keep if CAFR == 1
sort othervoluntarydisclosure
duplicates drop othervoluntarydisclosure, force
save "${Data}/tempCAFR.dta", replace




* Create disclosure data

forvalues i=2009(1)2014{

	use "${Data}/cd`i'.dta", clear
	
	sort submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure submissiondatetime
	drop if financialdisclosurecat == "NA" // drop non-financial disclosures
	duplicates drop submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure, force // drop duplicates 
	
	gen other = financialdisclosurecat == "OtherFinancialVoluntaryInformation"
	by submissionidentifier: egen nvals = sd(other)
	drop if other == 1 & nvals >0 & nvals !=. // drop footnote disclosures 
	drop nvals other
	
	
	keep submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure submissiondatetime endingdate periodtype
		
	
	* Create disclosure types
	
	gen CAFR = financialdisclosurecat == "AuditedFinancialStatementsOrCAFR15c212"
	gen AnnualFinancial =  financialdisclosurecat == "AnnualFinancialInformationOperatingData15c212"
	gen QMFinancial = financialdisclosurecat == "QuarterlyMonthlyFinancialInformation" | financialdisclosurecat == "InterimAdditionalFinancialInformationOperatingData"
	gen DisclosureFailure = financialdisclosurecat == "FailureToProvideAnnualFinancialInformationAsRequired15c212"
	gen Budget = financialdisclosurecat == "Budget" 

	
	* Create date
	replace submissiondatetime = trim(submissiondatetime)
	gen date = substr(submissiondatetime, 1, 10)
	
	
	keep submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure endingdate periodtype date CAFR AnnualFinancial QMFinancial DisclosureFailure Budget
	
	save "${Data}/tempcd_`i'.dta", replace
	
}

use "${Data}/tempcd_2009.dta", clear
forvalues i=2010(1)2014{
	append using "${Data}/tempcd_`i'.dta"
}


saveold "${Data}/cd2009to2014.dta", replace version(12)
shell rm "${Data}/temp"*






/******************************************************************************/

* STEP 2: DROP DUPLICATE ANNUAL FINANCIAL STATEMENTS

/******************************************************************************/


use "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\Timeliness\cd2009to2014.dta", clear

sort cusip9 date
duplicates tag cusip9 date, gen(tag)

bysort cusip9 date: egen sumcafr=sum(CAFR)
bysort cusip9 date: egen sumannual=sum( AnnualFinancial )

drop if tag>0 & sumcafr==1 & sumannual==1 & AnnualFinancial==1

saveold cd2009to2014_20180725, replace version(12)






/******************************************************************************/

* STEP 3: CREATE FILE TO COUNT NUMBER OF MATERIAL EVENTS

/******************************************************************************/


cd "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\Timeliness"

forvalues i=2009(1)2014{

	use "cd`i'.dta", clear
	
	sort submissionidentifier cusip9 eventdisclosurecat
	drop if eventdisclosurecat == "NA" 
	
	* Create date
	replace submissiondatetime = trim(submissiondatetime)
	gen date = substr(submissiondatetime, 1, 10)
	
	keep submissionidentifier cusip9 eventdisclosurecat submissiondatetime endingdate periodtype date
		
	save "tempcd_`i'.dta", replace
}

use "tempcd_2009.dta", clear
forvalues i=2010(1)2014{
	append using "tempcd_`i'.dta"
}


saveold "events2009to2014.dta", replace version(12)

























/*


* Match financial statement sample cd2009to2014 (produced by FinancialDisclosure.do)
* with the regression sample




*/



global rawdata "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/Timeliness"
global data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Analysis"






********************************************************************************
********************************************************************************

* Regression sample from gsz_20200313.do

********************************************************************************
********************************************************************************

use "${data}/sample_20190530.dta", clear

sort cusip_6 year
egen cusip_id = group(cusip_6)
egen state_id = group(state)
xtset cusip_id year


* ISSUE INDICATOR
gen issue = log_amountofissue >0


* CREATE UNDERWRITER VARIABLES
capture drop leadmng*
sort cusip_6 year
merge 1:1 cusip_6 year using tempunderwriter, keep (1 3) nogen // tempunderwriter is a balanced panel by cusip_6 year
gen missuw = leadmng == ""

foreach var of varlist leadmng leadmng1{
	replace `var' = "NA" if missuw == 1
}
replace mktshr = 0  if missuw == 1
egen uwid1 = group(leadmng1)


* ISSUER TYPE FIXED EFFECTS
egen issuerdscrp_pre_id = group(issuerdscrp_pre)


* SECTOR FIXED EFFECTS
replace uop1_pre="NA" if uop1_pre==""
egen uop1_pre_id = group(uop1_pre)


 
* IMPORT LAGGED MACRO VARIABLES
sort state year
merge m:1 state year using "${data}/tempmacro.dta", keep(1 3) nogen

gen gsp_avg_sc=gsp_avg/10000
gen pci_avg_sc=pci_avg/100
gen hpi_avg_sc=hpi_avg/100








/***********************************************/
* TABLE 3 - MAIN RESULTS
/***********************************************/

capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre ==1 & insurance_pre<1

capture drop samp1
xtreg financials_all treated_post post log_amountofissue call go gsp_avg_sc pci_avg_sc hpi_avg_sc unemploy_avg if sample==1
gen samp1=1 if e(sample)

keep if samp1==1
keep cusip_6
duplicates drop cusip_6, force

sort cusip_6
save "${rawdata}/tempregsample.dta", replace

























********************************************************************************
********************************************************************************

* Load linking table

********************************************************************************
********************************************************************************

import delimited "/Volumes/Frank's Passport for Mac Files/msrb_disclosure/SubmissionFileLink2009.csv", encoding(ISO-8859-1) clear
forvalues i=2010(1)2015{
	preserve 
	import delimited "/Volumes/Frank's Passport for Mac Files/msrb_disclosure/SubmissionFileLink`i'.csv", encoding(ISO-8859-1) clear
	save "${rawdata}/tempSubmissionFileLink`i'", replace
	restore
	append using "${rawdata}/tempSubmissionFileLink`i'"
}
shell rm "${rawdata}/tempSubmissionFileLink"*
capture erase "${rawdata}/tempSubmissionFileLink"*

sort submissionidentifier fileidentifier year month submissiontransactiondatetime
by submissionidentifier fileidentifier: gen nvals=_n==1
keep if nvals ==1
drop nvals
keep submissionidentifier fileidentifier year month
save "${rawdata}/SubmissionFileLink.dta", replace




















********************************************************************************
********************************************************************************


* Merge linking table with regression sample

********************************************************************************
********************************************************************************

use "${rawdata}/cd2009to2014.dta", clear

gen cusip_6 = substr(cusip9, 1, 6)
merge m:1 cusip_6 using  "${rawdata}/tempregsample.dta", keep(3)
duplicates drop submissionidentifier, force

keep submissionidentifier
sort submissionidentifier

merge 1:m submissionidentifier using "${rawdata}/SubmissionFileLink.dta", nogen keep(3)
gen month1 = string(month)
replace month1 = "0" + month1 if strlen(month1)==1

keep if year<=2013 | (year ==2014 & month<=6) 
saveold "${rawdata}/SubmissionFileLinkRegSample.dta", replace version(12)
shell rm "${rawdata}/tempregsample.dta"

capture erase "${rawdata}/tempregsample.dta"















clear all 
set more off 
set matsize 750


global author "/Users/szho/Dropbox/My Projects/Municipal Disclosure" 
cd "${author}/Analysis/Round 3"





/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   CREATE LAGGED MACRO VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

/*

* This file computes the annual macro variables by states and the outputs are later
* merged to our regression sample to compute the lagged macro variables. 


Input files:
	- Macro data in the folder: RawData/Economic_Characteristics

	
Output file:
	- By year measure of economic variables
	
*/

global macrolag "${author}/Rawdata/Economic_Characteristics"


preserve

clear

* GSP
import delimited "${macrolag}/GSP/GSP20160129.csv", encoding(ISO-8859-1)clear
sort state year
save tempgsp, replace

* PCI
import delimited "${macrolag}/Personal_Income/percapitaIncome20170909.csv", encoding(ISO-8859-1)clear
sort state year
save temppci, replace

* HPI
import excel "${macrolag}/HPI/HPI_PO_state.xls", clear first
sort state yr qtr
by state yr: egen hpi_avg = mean(index_sa)
rename yr year
keep state year hpi_avg
duplicates drop state year, force
sort state year
save temphpi, replace

* UNEMP
import delimited "${macrolag}/Unemployment/Unemploy20160908.csv", encoding(ISO-8859-1)clear
sort state year
by state year: egen unemploy_avg = mean(unemploy)
keep state year unemploy_avg
duplicates drop state year, force
sort state year

* Combine outputs
merge 1:1 state year using temphpi
keep if _merge == 3
drop _merge

merge 1:1 state year using temppci
keep if _merge == 3
drop _merge

merge 1:1 state year using tempgsp
keep if _merge == 3
drop _merge

rename gsp gsp_avg
rename percapitaincome pci_avg

replace year = year +1 
* so that year 2008 is matched to year 2009 for our data
sort state year

winsor2 unemploy_avg, replace cuts(1 99)
winsor2 gsp_avg, replace cuts(1 99)
winsor2 pci_avg, replace cuts(1 99)
winsor2 hpi_avg, replace cuts(1 99)

save tempmacro, replace

restore







/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   IMPORT 2NDARY MARKET TRADE VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/


/*
	- RetailTrades20190530, number and volume of trades at the bond-year level, created by 05_retail_trades.sas 
*/

* INDICATOR FOR THE EXISTENCE OF SECONDARY MARKET TRADE in 2009

preserve

use RetailTrades20190530, clear
gen num = inst1_num_2nd + inst2_num_2nd + retail1_num_2nd + retail2_num_2nd
keep if num > 1 & year ==2009
gen trade1=1
keep cusip_6 trade1
duplicates drop
sort cusip_6
save XSretail1, replace

restore



* CHANGES IN INVESTOR BASE VARIABLES

preserve
use RetailTrades20190530, clear 
sort cusip9 year

sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}
gen pct_retail_num_2nd = (retail1_num_2nd_t + retail2_num_2nd_t) / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
gen pct_inst_num_2nd   = (inst2_num_2nd_t)  / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
keep cusip_6 year pct_retail_num_2nd pct_inst_num_2nd
duplicates drop
sort cusip_6 year

save PctTrade, replace

restore



* For alternative control group:

preserve
use RetailTradesAltControl20190530, clear
sort cusip9 year

* Aggregate trading at the issuer level
sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}
gen pct_retail_num_2nd = (retail1_num_2nd_t + retail2_num_2nd_t) / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
gen pct_inst_num_2nd   = (inst2_num_2nd_t)  / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
keep cusip_6 year pct_retail_num_2nd pct_inst_num_2nd
duplicates drop
sort cusip_6 year
rename pct_retail_num_2nd pct_retail_num_2nd_alt
rename pct_inst_num_2nd pct_inst_num_2nd_alt

save PctTrade_altcontrol, replace
restore



preserve

use RetailTrades20190530, clear 

* Aggregate total number of trades at the issuer-year level:

sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}

gen total_trade_2nd = (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)

keep cusip_6 year total_trade_2nd
duplicates drop cusip_6 year, force
sort cusip_6 year

sum total_trade_2nd,d

save TotalTrade, replace

restore














/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   CREATE UNDERWRITER VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/


/*
 
Input file:
		- Underwriters20190530.csv, created by 06_underwriters.R, which is at the cusip_6 by year level. 
		# 1. For each underwriter and year, construct the amount of debt issuance by each underwriter
		# as a percentage of total debt issuance in that year. 	
		# 2. If multiple underwriters are used in a given issue, then assign the issuer's underwriter market share
		# with the largest market share. 
		# For example, for a cusip_6 issuer and 2010, there might be underwriters A, B, C and A has the largest market share in 2010. 
		# We assign the issuer's underwriter market share as A's market share. 
		
*/


* DEFINE UNDERWRITER FE 

preserve

clear

import delimited "Underwriters20190530.csv", encoding(ISO-8859-1) clear

drop v1
duplicates report cusip_6 year
* sanity check, should be zero

egen cusipid = group(cusip_6)
xtset cusipid year

tsfill,full
tab year
count if cusipid == .
* 0

* Fill in data of the missing year
sort cusipid year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist mktshr leadmng leadmng1 numstate{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

gsort cusipid -year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist mktshr leadmng leadmng1 numstate{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

* Check if all non-missing
foreach var of varlist cusip_6 mktshr leadmng leadmng1 numstate{
	count if missing(`var')
}

drop cusipid
sort cusip_6 year
save tempunderwriter, replace

restore



* DEFINE UNDERWRITER XS VARIABLES
preserve

clear

import delimited "Underwriters_2_20190530.csv", encoding(ISO-8859-1) clear

drop v1
duplicates report cusip_6 year
* sanity check, should be zero

egen cusipid = group(cusip_6)
xtset cusipid year

tsfill,full
tab year
count if cusipid == .
* 0

* Fill in data of the missing year
sort cusipid year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

gsort cusipid -year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

* Check if all non-missing
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	count if missing(`var')
}

drop cusipid
sort cusip_6 year
save tempunderwriter_20190530, replace

restore










/******************************************************************************/
/******************************************************************************/
/******************************************************************************/




* Timeliness




/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

global Timeliness  "${author}/Rawdata/Timeliness"

preserve

use "${Timeliness}/cd2009to2014.dta", clear 
// created by 03_disclosures.do 
// Includes only financial disclosures

* Submission date
gen year=substr(date, 1,4)
gen month=substr(date,6,2)
gen day=substr(date,9,2)
destring year month day, replace force
gen date1 = mdy(month, day, year)
keep if date1 !=.

* Fiscal year end date, benchmark year
drop year month day
gen year=substr(endingdate, 1,4)
gen month=substr(endingdate,6,2)
gen day=substr(endingdate,9,2)
destring year month day, replace force
gen fye=mdy(month,day,year)
keep if fye !=.

* Timeliness measure
gen timeliness = date1 - fye // larger, less timely!!!
gen cusip_6 = substr(cusip9,1,6)
keep if cusip_6 !=""
gen year1= year
forvalues i=2010(1)2014{
	replace year = `i'-1 if year1 == `i' & month<=6
}
drop year1 month day
keep if year>=2009 & year<=2013

sort cusip_6 year
by cusip_6 year: egen timeliness2 = min(timeliness) // select the most timely disclosures
duplicates drop cusip_6 year, force
keep cusip_6 year timeliness2

saveold "timeliness2_20190304.dta", replace version(12)

restore












/* COMPUTE AVERAGE ISSUER-LEVEL CREDIT RATING:*/

preserve

use "sample_20190530.dta", clear
keep cusip_6 year treated state moody_rated_pre sp_rated_pre
sort cusip_6 year treated state moody_rated_pre sp_rated_pre
by cusip_6: gen nvals = _n==1
keep if nvals ==1
drop nvals
save "tempsample0.dta", replace

restore

preserve

use "sample_20190530.dta", clear
capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre == 1 & insurance_pre<1
keep if sample ==1
keep cusip_6 year
save "tempratingagency1.dta", replace

restore


global rawdata "${author}/Rawdata/SDC"
use "${rawdata}/SDC_BondSaleDate_19902014.dta", clear

* Sale date
gen year = substr(saledate, 7,2) 
gen month = substr(saledate, 1,2)
gen day = substr(saledate, 4,2)

destring year, replace
destring month, replace
destring day, replace
replace year = 2000+year if year <=14
replace year = 1900+year if year >=90 & year <=99 & year !=.

tab year
tab month
tab day
count // 1,564,258

drop saledate
gen saledate = mdy(month, day, year)
format saledate %td

gen year1 = year
replace year1 = year - 1 if month <=6

drop if year <= 1990 & month <=6
drop if year >=2014 & month>=7
tab year1
drop year
rename year1 year


* Rating agency
replace ratingagency = lower(ratingagency)
tab ratingagency

gen moody_rated = strpos(ratingagency, "moody")>0
gen sp_rated = strpos(ratingagency, "sp")>0
gen fitch_rated = strpos(ratingagency, "fitch")>0
count if moody_rated == 0 & sp_rated == 0 & fitch_rated == 0 & rateddealflag == "Yes" // 0
gen rated = rateddealflag == "Yes"


* Moodys long term underlying rating
gen moody_rate_scale = .
replace moody_rate_scale = 9 if strpos(moodylongtermunderlying, "Aaa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 8 if strpos(moodylongtermunderlying, "Aa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 7 if strpos(moodylongtermunderlying, "A")>0 & moody_rate_scale ==.
replace moody_rate_scale = 6 if strpos(moodylongtermunderlying, "Baa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 5 if strpos(moodylongtermunderlying, "Ba")>0 & moody_rate_scale ==.
replace moody_rate_scale = 4 if strpos(moodylongtermunderlying, "B")>0 & moody_rate_scale ==.
replace moody_rate_scale = 3 if strpos(moodylongtermunderlying, "Caa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 2 if strpos(moodylongtermunderlying, "Ca")>0 & moody_rate_scale ==.
replace moody_rate_scale = 1 if strpos(moodylongtermunderlying, "C")>0 & moody_rate_scale ==.
replace moody_rate_scale = 0 if strpos(moodylongtermunderlying, "D")>0 & moody_rate_scale ==.
replace moody_rate_scale = 0 if strpos(moodylongtermunderlying, "E")>0 & moody_rate_scale ==.
tab moody_rate_scale 

gen sp_rate_scale = .
replace sp_rate_scale = 9 if strpos(splongtermunderlying, "AAA")>0 & sp_rate_scale ==.
replace sp_rate_scale = 8 if strpos(splongtermunderlying, "AA")>0 & sp_rate_scale ==.
replace sp_rate_scale = 7 if strpos(splongtermunderlying, "A")>0 & sp_rate_scale ==.
replace sp_rate_scale = 6 if strpos(splongtermunderlying, "BBB")>0 & sp_rate_scale ==.
replace sp_rate_scale = 5 if strpos(splongtermunderlying, "BB")>0 & sp_rate_scale ==.
replace sp_rate_scale = 4 if strpos(splongtermunderlying, "B")>0 & sp_rate_scale ==.
replace sp_rate_scale = 3 if strpos(splongtermunderlying, "CCC")>0 & sp_rate_scale ==.
replace sp_rate_scale = 2 if strpos(splongtermunderlying, "CC")>0 & sp_rate_scale ==.
replace sp_rate_scale = 1 if strpos(splongtermunderlying, "C")>0 & sp_rate_scale ==.
replace sp_rate_scale = 0 if strpos(splongtermunderlying, "D")>0 & sp_rate_scale ==.
replace sp_rate_scale = 0 if strpos(splongtermunderlying, "E")>0 & sp_rate_scale ==.

gen fitch_rate_scale = .
replace fitch_rate_scale = 9 if strpos(fitchlongtermunderlying, "AAA")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 8 if strpos(fitchlongtermunderlying, "AA")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 7 if strpos(fitchlongtermunderlying, "A")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 6 if strpos(fitchlongtermunderlying, "BBB")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 5 if strpos(fitchlongtermunderlying, "BB")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 4 if strpos(fitchlongtermunderlying, "B")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 3 if strpos(fitchlongtermunderlying, "CCC")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 2 if strpos(fitchlongtermunderlying, "CC")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 1 if strpos(fitchlongtermunderlying, "C")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 0 if strpos(fitchlongtermunderlying, "D")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 0 if strpos(fitchlongtermunderlying, "E")>0 & fitch_rate_scale ==.

egen rating = rowmean(sp_rate_scale fitch_rate_scale moody_rate_scale) // command ignores missing value
tab rating

cap drop state 
gen cusip_6 = substr(cusip1, 1,6)
merge m:1 cusip_6 using "tempsample0.dta", nogen keep(3)
sort cusip1 rating moody_rated sp_rated fitch_rated treated amount creditenhancer year state moody_rated_pre sp_rated_pre

merge m:1 cusip_6 year using "tempratingagency1.dta"
drop if _merge ==2
bys cusip_6: egen nvals = mean(_merge == 3)
keep if nvals >0
drop _merge nvals

keep cusip1 cusip_6 rating moody_rated sp_rated fitch_rated treated amount creditenhancer rated year state moody_rated_pre sp_rated_pre
sort cusip1 rating moody_rated sp_rated fitch_rated treated amount creditenhancer year state moody_rated_pre sp_rated_pre
by cusip1: gen nvals = _n==1
keep if nvals ==1
drop nvals

save "tempsdc.dta", replace // bond characteristics at the bond level, also used for parallel trends for yield. 






* Compute rating at issuer-year level. 
use "tempsdc.dta", clear

keep cusip_6 year rating
sort cusip_6 year
by cusip_6 year: egen avgrating = max(rating)
keep if avgrating !=.
duplicates drop cusip_6 year, force


preserve

use sample_20190530, clear
keep cusip_6 year
sort cusip_6 year
save tempsample1, replace

restore

merge 1:1 cusip_6 year using tempsample1 // to get back to a balanced panel
sort cusip_6 year
replace avgrating = avgrating[_n-1] if cusip_6 == cusip_6[_n-1] & avgrating ==.
keep cusip_6 year avgrating 

saveold IssuerRating20190530, replace version(12)

 
preserve
use IssuerRating20190530, clear
keep if year==2009
save avgrating2009, replace

restore 











/* Parallel trends */


use "sdc_issuer_year20190530.dta", clear // created by 02_sdc_processing.do, unique issuer-year level data

keep cusip_6 year moody_rated sp_rated fitch_rated call go amountofissue
egen cusipid = group(cusip_6)

xtset cusipid year
tsfill, full
sort cusipid year
foreach var of varlist moody_rated sp_rated fitch_rated{
	replace `var' = `var'[_n-1] if `var' ==. & `var'[_n-1] !=. & cusipid == cusipid[_n-1]
	rename `var' `var'_issuer
}
replace cusip_6 = cusip_6[_n-1] if cusipid == cusipid[_n-1] & cusip_6[_n-1] !=""
drop if cusip_6 ==""
sort cusip_6 year

replace moody_rated_issuer = moody_rated >0 & moody_rated !=.
replace sp_rated_issuer = sp_rated >0 & sp_rated !=.
replace fitch_rated_issuer = fitch_rated >0 & fitch_rated !=.

gen log_amountofissue = log(1 + amountofissue)
replace log_amountofissue = 0 if log_amountofissue ==.
gen call_issuer = call >0 & call !=.
gen go_issuer = go >0 & go !=.

keep cusip_6 year moody_rated_issuer sp_rated_issuer fitch_rated_issuer call_issuer go_issuer log_amountofissue
save "temptrends_rating.dta", replace










/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   SAMPLE TO GENERATE TABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/



/***********************************************/

global author "/Users/szho/Dropbox/My Projects/Municipal Disclosure" 


cd "${author}/Analysis/Round 3"
global Timeliness  "${author}/Rawdata/Timeliness" 

* CHOOSE A CLUSTERING DIMENSION
global clustervar cusip_id
// global clustervar state_id



use sample_20190530.dta, clear

sort cusip_6 year
egen cusip_id = group(cusip_6)
egen state_id = group(state)
xtset cusip_id year

* ISSUE INDICATOR
gen issue = log_amountofissue >0


* CREATE UNDERWRITER VARIABLES
capture drop leadmng*
sort cusip_6 year
merge 1:1 cusip_6 year using tempunderwriter, keep (1 3) nogen // tempunderwriter is a balanced panel by cusip_6 year
gen missuw = leadmng == ""

foreach var of varlist leadmng leadmng1{
	replace `var' = "NA" if missuw == 1
}
replace mktshr = 0  if missuw == 1
egen uwid1 = group(leadmng1)


* ISSUER TYPE FIXED EFFECTS
egen issuerdscrp_pre_id = group(issuerdscrp_pre)


* SECTOR FIXED EFFECTS
replace uop1_pre="NA" if uop1_pre==""
egen uop1_pre_id = group(uop1_pre)


* PRE-PERIOD ISSUER RATING FIXED EFFECTS
merge m:1 cusip_6 year using avgrating2009, keep(1 3) nogen
bysort cusip_6 (year): egen avgrating_pre=sum(avgrating)
replace avgrating_pre=. if avgrating_pre==0 & avgrating!=0
replace avgrating_pre=-1 if missing(avgrating_pre) // create -1 group for missing longtermratings
egen avgrating_pre_id = group(avgrating_pre)
drop avgrating

 
* IMPORT LAGGED MACRO VARIABLES
sort state year
merge m:1 state year using tempmacro, keep(1 3) nogen
gen gsp_avg_sc=gsp_avg/10000
gen pci_avg_sc=pci_avg/100
gen hpi_avg_sc=hpi_avg/100


* STATE BY YEAR FIXED EFFECTS
gen stateXyear = year*100+state_id


* SAMPLE FOR OUR ANALYSES
capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre ==1 & insurance_pre<1

capture drop samp1
xtreg financials_all treated_post post log_amountofissue call go  gsp_avg_sc pci_avg_sc hpi_avg_sc unemploy_avg if sample==1
gen samp1=1 if e(sample)
tab samp1

















/******************************************************************************/

/* This file cleans SDC data. */
/*
Date: 2020.03.13


/******************************/		
Input files:
/******************************/		

	- (1) /Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/SDC/SDC_IssuerSaleDate_19902014.csv.
	
cusip_6	saledate	county	issuer	ratingagency
729773	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729778	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729780	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729783	01/02/90	Hennepin	Plymouth City-Minnesota	SP
864048	01/02/90	Door	Sturgeon Bay-Wisconsin	
864056	01/02/90	Door	Sturgeon Bay-Wisconsin	
944048	01/02/90	Kosciusko	Wawasee Comm School Corp	
944050	01/02/90	Kosciusko	Wawasee Comm School Corp	
947644	01/02/90	Weber	Weber Co-Utah	
947648	01/02/90	Weber	Weber Co-Utah	
947651	01/02/90	Weber	Weber Co-Utah	
947659	01/02/90	Weber	Weber Co-Utah	
004590	01/03/90	Franklin-Hardin	Ackley City-Iowa	
004606	01/03/90	Franklin-Hardin	Ackley City-Iowa	
004613	01/03/90	Franklin-Hardin	Ackley City-Iowa	
048339	01/03/90	Atlantic	Atlantic City-New Jersey	
048343	01/03/90	Atlantic	Atlantic City-New Jersey	
048339	01/03/90	Atlantic	Atlantic City-New Jersey	MOODY;SP
048343	01/03/90	Atlantic	Atlantic City-New Jersey	MOODY;SP
	
	
*/
/******************************************************************************/

clear all 
set more off 
set matsize 750

// cd "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\SDC"
cd "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/SDC"
global data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Analysis/Round 2 new"



***********************************************************************/
/* SDC data cleaning */
**********************************************************************/



use SDC_IssuerSaleDate_19902014, clear // created by 01_sdc_issuer.R


* Sale date
gen year = substr(saledate, 7,2) 
gen month = substr(saledate, 1,2)
gen day = substr(saledate, 4,2)

destring year, replace
destring month, replace
destring day, replace
replace year = 2000+year if year <=14
replace year = 1900+year if year >=90 & year <=99 & year !=.

tab year
tab month
tab day
count 
*857,323

drop saledate
gen saledate = mdy(month, day, year)
format saledate %td

* Ratinng agency
replace ratingagency = lower(ratingagency)
tab ratingagency

gen moody_rated = strpos(ratingagency, "moody")>0
gen sp_rated = strpos(ratingagency, "sp")>0
gen fitch_rated = strpos(ratingagency, "fitch")>0
count if moody_rated == 0 & sp_rated == 0 & fitch_rated == 0 & rateddealflag == "Yes" 
*0

gen rated = rateddealflag == "Yes"

* Credit enhancement
gen insurance = creditenhancetype != ""

* Callable
gen call = callable == "Yes"
tab call

* Go vs reveneu
gen go = security == "GO"


* amount of issue
destring amountofissue, replace force 
*forced is used because missing amount is denoted by NA.


* Maturity

gen yearm = substr(maxmaturity, 1,4)
gen monthm = substr(maxmaturity, 6,2)
gen daym = substr(maxmaturity,9,2)
destring monthm daym, replace
destring yearm, replace force
tab yearm
gen max_maturity = mdy(monthm, daym, yearm)
replace max_maturity = . if maxmaturity == "NA" | yearm <1990
format max_maturity %td
drop yearm monthm daym




* Underwriter
gen underwriter = leadmng 
*857,323










/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
* Generate issuer-year level data
/**********************************************************************/

preserve

gen year1 = year
replace year1 = year - 1 if month <=6

drop if year <= 1990 & month <=6
drop if year >=2014 & month>=7
tab year1
drop year
rename year1 year

* Compute average issuer-year rating, insurance, call, go
sort cusip_6 year

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6 year: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6 year: egen first_offer_date = min(saledate)
by cusip_6 year: egen last_offer_date = max(saledate)
by cusip_6 year: gen num_offer = _N
format first_offer_date last_offer_date %td
by cusip_6 year: egen nvals = total(amountofissue)
gen amountofissue0=amountofissue
replace amountofissue = nvals
drop nvals

* Max maturity

by cusip_6 year: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals
format max_maturity %td



* Drop duplicates
* Keep issuers that issue the highest amount of debt and highest frequency of debt
gsort cusip_6 year issuerdscrp -saledate state uop1
by cusip_6 year issuerdscrp: egen nvals = total(amountofissue0) 
** total amount
by cusip_6 year issuerdscrp: gen nvals1 = _N 
** total number
by cusip_6 year issuerdscrp: gen nvals0 =_n ==1

keep if nvals0 == 1
drop nvals0
gsort cusip_6 year -saledate
by cusip_6 year: egen nvals2 = max(nvals) 
** max total amount of debt by cusip_6 year and issuerdscrp
keep if nvals == nvals2 
** keep issuers that issue the highest total amount of debt.
duplicates report cusip_6 year


by cusip_6 year: egen nvals3 = max(nvals1)
keep if nvals3 == nvals1
duplicates report cusip_6 year


sort cusip_6 year state uop1 issuerdscrp
by cusip_6 year: gen issuerdscrp1 = issuerdscrp[_n+1]
sort cusip_6 year 



keep cusip_6 year moody_rated rated sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state amountofissue issuerdscrp issuerdscrp1 uop1 underwriter issuer
sort cusip_6 year state uop1 issuerdscrp issuerdscrp1
cap drop nvals*
by cusip_6 year: gen nvals = _n==1
keep if nvals == 1
drop nvals

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable rated "issuer is rated"
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"
		label variable amountofissue "total annual issuance amount"
		label variable uop1 "purpose"
		label variable underwriter "underwriter"
		label variable issuer "issuer name"

count 
* 387,174 issuer-year


saveold "${data}/sdc_issuer_year20190530.dta", replace version(12)
restore
















/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/* Issuer level: debt outstanding */
/**********************************************************************/


preserve

keep if saledate <= mdy(06,30,2010) & max_maturity >= mdy(06,30,2010) & max_maturity !=. 
keep if rated ==1
sort cusip_6

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6: egen first_offer_date = min(saledate)
by cusip_6: egen last_offer_date = max(saledate)
by cusip_6: gen num_offer = _N
format first_offer_date last_offer_date %td

* Max maturity

by cusip_6: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals

duplicates drop cusip_6, force
keep cusip_6 moody_rated  sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"

count 
*39,158

saveold "${data}/sdc_debtoustanding_20190530.dta", replace version(12) // only sample itself is used. 

restore





/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/* access market */


preserve

keep if saledate <= mdy(06,30,2010) & saledate >= mdy(07,01,2006) 
sort cusip_6

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6: egen first_offer_date = min(saledate)
by cusip_6: egen last_offer_date = max(saledate)
by cusip_6: gen num_offer = _N
format first_offer_date last_offer_date %td
by cusip_6: egen nvals = total(amountofissue)
gen amountofissue0=amountofissue
replace amountofissue = nvals
drop nvals

* Max maturity

by cusip_6: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals

* Drop duplicates
* Keep issuers that issue the highest amount of debt and highest frequency of debt
gsort cusip_6 issuerdscrp -saledate state uop1 issuer
by cusip_6 issuerdscrp: egen nvals = total(amountofissue0) 
** total amount
by cusip_6 issuerdscrp: gen nvals1 = _N 
** total number
by cusip_6 issuerdscrp: gen nvals0 =_n ==1

keep if nvals0 == 1
drop nvals0
gsort cusip_6 issuerdscrp -saledate state uop1 issuer
by cusip_6: egen nvals2 = max(nvals) 
** max total amount of debt by cusip_6 and issuerdscrp
keep if nvals == nvals2 
** keep issuers that issue the highest total amount of debt.
duplicates report cusip_6
/*
--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |        32362             0
        2 |           18             9
--------------------------------------
*/

by cusip_6: egen nvals3 = max(nvals1)
keep if nvals3 == nvals1
duplicates report cusip_6
/*
--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |        32362             0
        2 |           18             9
--------------------------------------
*/
sort cusip_6 year state uop1 issuerdscrp issuer
by cusip_6 (year state uop1 issuerdscrp issuer): gen issuerdscrp1 = issuerdscrp[_n+1]
cap drop nvals
by cusip_6: gen nvals = _n==1
keep if nvals ==1
drop nvals


keep cusip_6 rated moody_rated sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state issuerdscrp issuerdscrp1 amountofissue uop1  underwriter issuer

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"
		label variable amountofissue "total annual issuance amount"

count 
*32,371


saveold "${data}/sdc_accessmarket_20190530.dta", replace version(12)

restore










/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/

* Issue level

preserve

keep if saledate <= mdy(06,30,2010) & saledate >= mdy(07,01,2006) 
sort cusip_6 saledate 
by cusip_6 saledate: gen nvals =_n==1
keep if nvals ==1
drop nvals

gen issue_id = _n
keep cusip_6 saledate year

saveold "${data}/sdc_issuereg_20190304.dta", replace version(12)

restore









/******************************************************************************/

/*


This file creates the data for financial disclosures. 
Date: 2020.03.13

	
*/


global Data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/Timeliness"
global Data "/Users/szho/Documents/Temp"


* Change formats
forvalues i=2009(1)2014{
	use "${Data}/cd`i'.dta", clear
	saveold "${Data}/cd`i'.dta", replace version(12)
}


























/******************************************************************************/

* STEP 1: CREATE FINANCIAL DISCLOSURE DATA

/******************************************************************************/


* Other disclosures that are CAFR

import excel "${Data}/OtherVoluntaryFinancialDisclosures.xlsx", sheet("Sheet1") firstrow clear
keep othervoluntarydisclosure CAFR
keep if CAFR == 1
sort othervoluntarydisclosure
duplicates drop othervoluntarydisclosure, force
save "${Data}/tempCAFR.dta", replace




* Create disclosure data

forvalues i=2009(1)2014{

	use "${Data}/cd`i'.dta", clear
	
	sort submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure submissiondatetime
	drop if financialdisclosurecat == "NA" // drop non-financial disclosures
	duplicates drop submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure, force // drop duplicates 
	
	gen other = financialdisclosurecat == "OtherFinancialVoluntaryInformation"
	by submissionidentifier: egen nvals = sd(other)
	drop if other == 1 & nvals >0 & nvals !=. // drop footnote disclosures 
	drop nvals other
	
	
	keep submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure submissiondatetime endingdate periodtype
		
	
	* Create disclosure types
	
	gen CAFR = financialdisclosurecat == "AuditedFinancialStatementsOrCAFR15c212"
	gen AnnualFinancial =  financialdisclosurecat == "AnnualFinancialInformationOperatingData15c212"
	gen QMFinancial = financialdisclosurecat == "QuarterlyMonthlyFinancialInformation" | financialdisclosurecat == "InterimAdditionalFinancialInformationOperatingData"
	gen DisclosureFailure = financialdisclosurecat == "FailureToProvideAnnualFinancialInformationAsRequired15c212"
	gen Budget = financialdisclosurecat == "Budget" 

	
	* Create date
	replace submissiondatetime = trim(submissiondatetime)
	gen date = substr(submissiondatetime, 1, 10)
	
	
	keep submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure endingdate periodtype date CAFR AnnualFinancial QMFinancial DisclosureFailure Budget
	
	save "${Data}/tempcd_`i'.dta", replace
	
}

use "${Data}/tempcd_2009.dta", clear
forvalues i=2010(1)2014{
	append using "${Data}/tempcd_`i'.dta"
}


saveold "${Data}/cd2009to2014.dta", replace version(12)
shell rm "${Data}/temp"*






/******************************************************************************/

* STEP 2: DROP DUPLICATE ANNUAL FINANCIAL STATEMENTS

/******************************************************************************/


use "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\Timeliness\cd2009to2014.dta", clear

sort cusip9 date
duplicates tag cusip9 date, gen(tag)

bysort cusip9 date: egen sumcafr=sum(CAFR)
bysort cusip9 date: egen sumannual=sum( AnnualFinancial )

drop if tag>0 & sumcafr==1 & sumannual==1 & AnnualFinancial==1

saveold cd2009to2014_20180725, replace version(12)






/******************************************************************************/

* STEP 3: CREATE FILE TO COUNT NUMBER OF MATERIAL EVENTS

/******************************************************************************/


cd "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\Timeliness"

forvalues i=2009(1)2014{

	use "cd`i'.dta", clear
	
	sort submissionidentifier cusip9 eventdisclosurecat
	drop if eventdisclosurecat == "NA" 
	
	* Create date
	replace submissiondatetime = trim(submissiondatetime)
	gen date = substr(submissiondatetime, 1, 10)
	
	keep submissionidentifier cusip9 eventdisclosurecat submissiondatetime endingdate periodtype date
		
	save "tempcd_`i'.dta", replace
}

use "tempcd_2009.dta", clear
forvalues i=2010(1)2014{
	append using "tempcd_`i'.dta"
}


saveold "events2009to2014.dta", replace version(12)

























/*


* Match financial statement sample cd2009to2014 (produced by FinancialDisclosure.do)
* with the regression sample




*/



global rawdata "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/Timeliness"
global data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Analysis"






********************************************************************************
********************************************************************************

* Regression sample from gsz_20200313.do

********************************************************************************
********************************************************************************

use "${data}/sample_20190530.dta", clear

sort cusip_6 year
egen cusip_id = group(cusip_6)
egen state_id = group(state)
xtset cusip_id year


* ISSUE INDICATOR
gen issue = log_amountofissue >0


* CREATE UNDERWRITER VARIABLES
capture drop leadmng*
sort cusip_6 year
merge 1:1 cusip_6 year using tempunderwriter, keep (1 3) nogen // tempunderwriter is a balanced panel by cusip_6 year
gen missuw = leadmng == ""

foreach var of varlist leadmng leadmng1{
	replace `var' = "NA" if missuw == 1
}
replace mktshr = 0  if missuw == 1
egen uwid1 = group(leadmng1)


* ISSUER TYPE FIXED EFFECTS
egen issuerdscrp_pre_id = group(issuerdscrp_pre)


* SECTOR FIXED EFFECTS
replace uop1_pre="NA" if uop1_pre==""
egen uop1_pre_id = group(uop1_pre)


 
* IMPORT LAGGED MACRO VARIABLES
sort state year
merge m:1 state year using "${data}/tempmacro.dta", keep(1 3) nogen

gen gsp_avg_sc=gsp_avg/10000
gen pci_avg_sc=pci_avg/100
gen hpi_avg_sc=hpi_avg/100








/***********************************************/
* TABLE 3 - MAIN RESULTS
/***********************************************/

capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre ==1 & insurance_pre<1

capture drop samp1
xtreg financials_all treated_post post log_amountofissue call go gsp_avg_sc pci_avg_sc hpi_avg_sc unemploy_avg if sample==1
gen samp1=1 if e(sample)

keep if samp1==1
keep cusip_6
duplicates drop cusip_6, force

sort cusip_6
save "${rawdata}/tempregsample.dta", replace

























********************************************************************************
********************************************************************************

* Load linking table

********************************************************************************
********************************************************************************

import delimited "/Volumes/Frank's Passport for Mac Files/msrb_disclosure/SubmissionFileLink2009.csv", encoding(ISO-8859-1) clear
forvalues i=2010(1)2015{
	preserve 
	import delimited "/Volumes/Frank's Passport for Mac Files/msrb_disclosure/SubmissionFileLink`i'.csv", encoding(ISO-8859-1) clear
	save "${rawdata}/tempSubmissionFileLink`i'", replace
	restore
	append using "${rawdata}/tempSubmissionFileLink`i'"
}
shell rm "${rawdata}/tempSubmissionFileLink"*
capture erase "${rawdata}/tempSubmissionFileLink"*

sort submissionidentifier fileidentifier year month submissiontransactiondatetime
by submissionidentifier fileidentifier: gen nvals=_n==1
keep if nvals ==1
drop nvals
keep submissionidentifier fileidentifier year month
save "${rawdata}/SubmissionFileLink.dta", replace




















********************************************************************************
********************************************************************************


* Merge linking table with regression sample

********************************************************************************
********************************************************************************

use "${rawdata}/cd2009to2014.dta", clear

gen cusip_6 = substr(cusip9, 1, 6)
merge m:1 cusip_6 using  "${rawdata}/tempregsample.dta", keep(3)
duplicates drop submissionidentifier, force

keep submissionidentifier
sort submissionidentifier

merge 1:m submissionidentifier using "${rawdata}/SubmissionFileLink.dta", nogen keep(3)
gen month1 = string(month)
replace month1 = "0" + month1 if strlen(month1)==1

keep if year<=2013 | (year ==2014 & month<=6) 
saveold "${rawdata}/SubmissionFileLinkRegSample.dta", replace version(12)
shell rm "${rawdata}/tempregsample.dta"

capture erase "${rawdata}/tempregsample.dta"















clear all 
set more off 
set matsize 750


global author "/Users/szho/Dropbox/My Projects/Municipal Disclosure" 
cd "${author}/Analysis/Round 3"





/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   CREATE LAGGED MACRO VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

/*

* This file computes the annual macro variables by states and the outputs are later
* merged to our regression sample to compute the lagged macro variables. 


Input files:
	- Macro data in the folder: RawData/Economic_Characteristics

	
Output file:
	- By year measure of economic variables
	
*/

global macrolag "${author}/Rawdata/Economic_Characteristics"


preserve

clear

* GSP
import delimited "${macrolag}/GSP/GSP20160129.csv", encoding(ISO-8859-1)clear
sort state year
save tempgsp, replace

* PCI
import delimited "${macrolag}/Personal_Income/percapitaIncome20170909.csv", encoding(ISO-8859-1)clear
sort state year
save temppci, replace

* HPI
import excel "${macrolag}/HPI/HPI_PO_state.xls", clear first
sort state yr qtr
by state yr: egen hpi_avg = mean(index_sa)
rename yr year
keep state year hpi_avg
duplicates drop state year, force
sort state year
save temphpi, replace

* UNEMP
import delimited "${macrolag}/Unemployment/Unemploy20160908.csv", encoding(ISO-8859-1)clear
sort state year
by state year: egen unemploy_avg = mean(unemploy)
keep state year unemploy_avg
duplicates drop state year, force
sort state year

* Combine outputs
merge 1:1 state year using temphpi
keep if _merge == 3
drop _merge

merge 1:1 state year using temppci
keep if _merge == 3
drop _merge

merge 1:1 state year using tempgsp
keep if _merge == 3
drop _merge

rename gsp gsp_avg
rename percapitaincome pci_avg

replace year = year +1 
* so that year 2008 is matched to year 2009 for our data
sort state year

winsor2 unemploy_avg, replace cuts(1 99)
winsor2 gsp_avg, replace cuts(1 99)
winsor2 pci_avg, replace cuts(1 99)
winsor2 hpi_avg, replace cuts(1 99)

save tempmacro, replace

restore







/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   IMPORT 2NDARY MARKET TRADE VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/


/*
	- RetailTrades20190530, number and volume of trades at the bond-year level, created by 05_retail_trades.sas 
*/

* INDICATOR FOR THE EXISTENCE OF SECONDARY MARKET TRADE in 2009

preserve

use RetailTrades20190530, clear
gen num = inst1_num_2nd + inst2_num_2nd + retail1_num_2nd + retail2_num_2nd
keep if num > 1 & year ==2009
gen trade1=1
keep cusip_6 trade1
duplicates drop
sort cusip_6
save XSretail1, replace

restore



* CHANGES IN INVESTOR BASE VARIABLES

preserve
use RetailTrades20190530, clear 
sort cusip9 year

sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}
gen pct_retail_num_2nd = (retail1_num_2nd_t + retail2_num_2nd_t) / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
gen pct_inst_num_2nd   = (inst2_num_2nd_t)  / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
keep cusip_6 year pct_retail_num_2nd pct_inst_num_2nd
duplicates drop
sort cusip_6 year

save PctTrade, replace

restore



* For alternative control group:

preserve
use RetailTradesAltControl20190530, clear
sort cusip9 year

* Aggregate trading at the issuer level
sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}
gen pct_retail_num_2nd = (retail1_num_2nd_t + retail2_num_2nd_t) / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
gen pct_inst_num_2nd   = (inst2_num_2nd_t)  / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
keep cusip_6 year pct_retail_num_2nd pct_inst_num_2nd
duplicates drop
sort cusip_6 year
rename pct_retail_num_2nd pct_retail_num_2nd_alt
rename pct_inst_num_2nd pct_inst_num_2nd_alt

save PctTrade_altcontrol, replace
restore



preserve

use RetailTrades20190530, clear 

* Aggregate total number of trades at the issuer-year level:

sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}

gen total_trade_2nd = (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)

keep cusip_6 year total_trade_2nd
duplicates drop cusip_6 year, force
sort cusip_6 year

sum total_trade_2nd,d

save TotalTrade, replace

restore














/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   CREATE UNDERWRITER VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/


/*
 
Input file:
		- Underwriters20190530.csv, created by 06_underwriters.R, which is at the cusip_6 by year level. 
		# 1. For each underwriter and year, construct the amount of debt issuance by each underwriter
		# as a percentage of total debt issuance in that year. 	
		# 2. If multiple underwriters are used in a given issue, then assign the issuer's underwriter market share
		# with the largest market share. 
		# For example, for a cusip_6 issuer and 2010, there might be underwriters A, B, C and A has the largest market share in 2010. 
		# We assign the issuer's underwriter market share as A's market share. 
		
*/


* DEFINE UNDERWRITER FE 

preserve

clear

import delimited "Underwriters20190530.csv", encoding(ISO-8859-1) clear

drop v1
duplicates report cusip_6 year
* sanity check, should be zero

egen cusipid = group(cusip_6)
xtset cusipid year

tsfill,full
tab year
count if cusipid == .
* 0

* Fill in data of the missing year
sort cusipid year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist mktshr leadmng leadmng1 numstate{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

gsort cusipid -year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist mktshr leadmng leadmng1 numstate{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

* Check if all non-missing
foreach var of varlist cusip_6 mktshr leadmng leadmng1 numstate{
	count if missing(`var')
}

drop cusipid
sort cusip_6 year
save tempunderwriter, replace

restore



* DEFINE UNDERWRITER XS VARIABLES
preserve

clear

import delimited "Underwriters_2_20190530.csv", encoding(ISO-8859-1) clear

drop v1
duplicates report cusip_6 year
* sanity check, should be zero

egen cusipid = group(cusip_6)
xtset cusipid year

tsfill,full
tab year
count if cusipid == .
* 0

* Fill in data of the missing year
sort cusipid year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

gsort cusipid -year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

* Check if all non-missing
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	count if missing(`var')
}

drop cusipid
sort cusip_6 year
save tempunderwriter_20190530, replace

restore










/******************************************************************************/
/******************************************************************************/
/******************************************************************************/




* Timeliness




/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

global Timeliness  "${author}/Rawdata/Timeliness"

preserve

use "${Timeliness}/cd2009to2014.dta", clear 
// created by 03_disclosures.do 
// Includes only financial disclosures

* Submission date
gen year=substr(date, 1,4)
gen month=substr(date,6,2)
gen day=substr(date,9,2)
destring year month day, replace force
gen date1 = mdy(month, day, year)
keep if date1 !=.

* Fiscal year end date, benchmark year
drop year month day
gen year=substr(endingdate, 1,4)
gen month=substr(endingdate,6,2)
gen day=substr(endingdate,9,2)
destring year month day, replace force
gen fye=mdy(month,day,year)
keep if fye !=.

* Timeliness measure
gen timeliness = date1 - fye // larger, less timely!!!
gen cusip_6 = substr(cusip9,1,6)
keep if cusip_6 !=""
gen year1= year
forvalues i=2010(1)2014{
	replace year = `i'-1 if year1 == `i' & month<=6
}
drop year1 month day
keep if year>=2009 & year<=2013

sort cusip_6 year
by cusip_6 year: egen timeliness2 = min(timeliness) // select the most timely disclosures
duplicates drop cusip_6 year, force
keep cusip_6 year timeliness2

saveold "timeliness2_20190304.dta", replace version(12)

restore












/* COMPUTE AVERAGE ISSUER-LEVEL CREDIT RATING:*/

preserve

use "sample_20190530.dta", clear
keep cusip_6 year treated state moody_rated_pre sp_rated_pre
sort cusip_6 year treated state moody_rated_pre sp_rated_pre
by cusip_6: gen nvals = _n==1
keep if nvals ==1
drop nvals
save "tempsample0.dta", replace

restore

preserve

use "sample_20190530.dta", clear
capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre == 1 & insurance_pre<1
keep if sample ==1
keep cusip_6 year
save "tempratingagency1.dta", replace

restore


global rawdata "${author}/Rawdata/SDC"
use "${rawdata}/SDC_BondSaleDate_19902014.dta", clear

* Sale date
gen year = substr(saledate, 7,2) 
gen month = substr(saledate, 1,2)
gen day = substr(saledate, 4,2)

destring year, replace
destring month, replace
destring day, replace
replace year = 2000+year if year <=14
replace year = 1900+year if year >=90 & year <=99 & year !=.

tab year
tab month
tab day
count // 1,564,258

drop saledate
gen saledate = mdy(month, day, year)
format saledate %td

gen year1 = year
replace year1 = year - 1 if month <=6

drop if year <= 1990 & month <=6
drop if year >=2014 & month>=7
tab year1
drop year
rename year1 year


* Rating agency
replace ratingagency = lower(ratingagency)
tab ratingagency

gen moody_rated = strpos(ratingagency, "moody")>0
gen sp_rated = strpos(ratingagency, "sp")>0
gen fitch_rated = strpos(ratingagency, "fitch")>0
count if moody_rated == 0 & sp_rated == 0 & fitch_rated == 0 & rateddealflag == "Yes" // 0
gen rated = rateddealflag == "Yes"


* Moodys long term underlying rating
gen moody_rate_scale = .
replace moody_rate_scale = 9 if strpos(moodylongtermunderlying, "Aaa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 8 if strpos(moodylongtermunderlying, "Aa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 7 if strpos(moodylongtermunderlying, "A")>0 & moody_rate_scale ==.
replace moody_rate_scale = 6 if strpos(moodylongtermunderlying, "Baa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 5 if strpos(moodylongtermunderlying, "Ba")>0 & moody_rate_scale ==.
replace moody_rate_scale = 4 if strpos(moodylongtermunderlying, "B")>0 & moody_rate_scale ==.
replace moody_rate_scale = 3 if strpos(moodylongtermunderlying, "Caa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 2 if strpos(moodylongtermunderlying, "Ca")>0 & moody_rate_scale ==.
replace moody_rate_scale = 1 if strpos(moodylongtermunderlying, "C")>0 & moody_rate_scale ==.
replace moody_rate_scale = 0 if strpos(moodylongtermunderlying, "D")>0 & moody_rate_scale ==.
replace moody_rate_scale = 0 if strpos(moodylongtermunderlying, "E")>0 & moody_rate_scale ==.
tab moody_rate_scale 

gen sp_rate_scale = .
replace sp_rate_scale = 9 if strpos(splongtermunderlying, "AAA")>0 & sp_rate_scale ==.
replace sp_rate_scale = 8 if strpos(splongtermunderlying, "AA")>0 & sp_rate_scale ==.
replace sp_rate_scale = 7 if strpos(splongtermunderlying, "A")>0 & sp_rate_scale ==.
replace sp_rate_scale = 6 if strpos(splongtermunderlying, "BBB")>0 & sp_rate_scale ==.
replace sp_rate_scale = 5 if strpos(splongtermunderlying, "BB")>0 & sp_rate_scale ==.
replace sp_rate_scale = 4 if strpos(splongtermunderlying, "B")>0 & sp_rate_scale ==.
replace sp_rate_scale = 3 if strpos(splongtermunderlying, "CCC")>0 & sp_rate_scale ==.
replace sp_rate_scale = 2 if strpos(splongtermunderlying, "CC")>0 & sp_rate_scale ==.
replace sp_rate_scale = 1 if strpos(splongtermunderlying, "C")>0 & sp_rate_scale ==.
replace sp_rate_scale = 0 if strpos(splongtermunderlying, "D")>0 & sp_rate_scale ==.
replace sp_rate_scale = 0 if strpos(splongtermunderlying, "E")>0 & sp_rate_scale ==.

gen fitch_rate_scale = .
replace fitch_rate_scale = 9 if strpos(fitchlongtermunderlying, "AAA")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 8 if strpos(fitchlongtermunderlying, "AA")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 7 if strpos(fitchlongtermunderlying, "A")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 6 if strpos(fitchlongtermunderlying, "BBB")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 5 if strpos(fitchlongtermunderlying, "BB")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 4 if strpos(fitchlongtermunderlying, "B")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 3 if strpos(fitchlongtermunderlying, "CCC")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 2 if strpos(fitchlongtermunderlying, "CC")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 1 if strpos(fitchlongtermunderlying, "C")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 0 if strpos(fitchlongtermunderlying, "D")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 0 if strpos(fitchlongtermunderlying, "E")>0 & fitch_rate_scale ==.

egen rating = rowmean(sp_rate_scale fitch_rate_scale moody_rate_scale) // command ignores missing value
tab rating

cap drop state 
gen cusip_6 = substr(cusip1, 1,6)
merge m:1 cusip_6 using "tempsample0.dta", nogen keep(3)
sort cusip1 rating moody_rated sp_rated fitch_rated treated amount creditenhancer year state moody_rated_pre sp_rated_pre

merge m:1 cusip_6 year using "tempratingagency1.dta"
drop if _merge ==2
bys cusip_6: egen nvals = mean(_merge == 3)
keep if nvals >0
drop _merge nvals

keep cusip1 cusip_6 rating moody_rated sp_rated fitch_rated treated amount creditenhancer rated year state moody_rated_pre sp_rated_pre
sort cusip1 rating moody_rated sp_rated fitch_rated treated amount creditenhancer year state moody_rated_pre sp_rated_pre
by cusip1: gen nvals = _n==1
keep if nvals ==1
drop nvals

save "tempsdc.dta", replace // bond characteristics at the bond level, also used for parallel trends for yield. 






* Compute rating at issuer-year level. 
use "tempsdc.dta", clear

keep cusip_6 year rating
sort cusip_6 year
by cusip_6 year: egen avgrating = max(rating)
keep if avgrating !=.
duplicates drop cusip_6 year, force


preserve

use sample_20190530, clear
keep cusip_6 year
sort cusip_6 year
save tempsample1, replace

restore

merge 1:1 cusip_6 year using tempsample1 // to get back to a balanced panel
sort cusip_6 year
replace avgrating = avgrating[_n-1] if cusip_6 == cusip_6[_n-1] & avgrating ==.
keep cusip_6 year avgrating 

saveold IssuerRating20190530, replace version(12)

 
preserve
use IssuerRating20190530, clear
keep if year==2009
save avgrating2009, replace

restore 











/* Parallel trends */


use "sdc_issuer_year20190530.dta", clear // created by 02_sdc_processing.do, unique issuer-year level data

keep cusip_6 year moody_rated sp_rated fitch_rated call go amountofissue
egen cusipid = group(cusip_6)

xtset cusipid year
tsfill, full
sort cusipid year
foreach var of varlist moody_rated sp_rated fitch_rated{
	replace `var' = `var'[_n-1] if `var' ==. & `var'[_n-1] !=. & cusipid == cusipid[_n-1]
	rename `var' `var'_issuer
}
replace cusip_6 = cusip_6[_n-1] if cusipid == cusipid[_n-1] & cusip_6[_n-1] !=""
drop if cusip_6 ==""
sort cusip_6 year

replace moody_rated_issuer = moody_rated >0 & moody_rated !=.
replace sp_rated_issuer = sp_rated >0 & sp_rated !=.
replace fitch_rated_issuer = fitch_rated >0 & fitch_rated !=.

gen log_amountofissue = log(1 + amountofissue)
replace log_amountofissue = 0 if log_amountofissue ==.
gen call_issuer = call >0 & call !=.
gen go_issuer = go >0 & go !=.

keep cusip_6 year moody_rated_issuer sp_rated_issuer fitch_rated_issuer call_issuer go_issuer log_amountofissue
save "temptrends_rating.dta", replace










/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   SAMPLE TO GENERATE TABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/



/***********************************************/

global author "/Users/szho/Dropbox/My Projects/Municipal Disclosure" 


cd "${author}/Analysis/Round 3"
global Timeliness  "${author}/Rawdata/Timeliness" 

* CHOOSE A CLUSTERING DIMENSION
global clustervar cusip_id
// global clustervar state_id



use sample_20190530.dta, clear

sort cusip_6 year
egen cusip_id = group(cusip_6)
egen state_id = group(state)
xtset cusip_id year

* ISSUE INDICATOR
gen issue = log_amountofissue >0


* CREATE UNDERWRITER VARIABLES
capture drop leadmng*
sort cusip_6 year
merge 1:1 cusip_6 year using tempunderwriter, keep (1 3) nogen // tempunderwriter is a balanced panel by cusip_6 year
gen missuw = leadmng == ""

foreach var of varlist leadmng leadmng1{
	replace `var' = "NA" if missuw == 1
}
replace mktshr = 0  if missuw == 1
egen uwid1 = group(leadmng1)


* ISSUER TYPE FIXED EFFECTS
egen issuerdscrp_pre_id = group(issuerdscrp_pre)


* SECTOR FIXED EFFECTS
replace uop1_pre="NA" if uop1_pre==""
egen uop1_pre_id = group(uop1_pre)


* PRE-PERIOD ISSUER RATING FIXED EFFECTS
merge m:1 cusip_6 year using avgrating2009, keep(1 3) nogen
bysort cusip_6 (year): egen avgrating_pre=sum(avgrating)
replace avgrating_pre=. if avgrating_pre==0 & avgrating!=0
replace avgrating_pre=-1 if missing(avgrating_pre) // create -1 group for missing longtermratings
egen avgrating_pre_id = group(avgrating_pre)
drop avgrating

 
* IMPORT LAGGED MACRO VARIABLES
sort state year
merge m:1 state year using tempmacro, keep(1 3) nogen
gen gsp_avg_sc=gsp_avg/10000
gen pci_avg_sc=pci_avg/100
gen hpi_avg_sc=hpi_avg/100


* STATE BY YEAR FIXED EFFECTS
gen stateXyear = year*100+state_id


* SAMPLE FOR OUR ANALYSES
capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre ==1 & insurance_pre<1

capture drop samp1
xtreg financials_all treated_post post log_amountofissue call go  gsp_avg_sc pci_avg_sc hpi_avg_sc unemploy_avg if sample==1
gen samp1=1 if e(sample)
tab samp1

















/******************************************************************************/

/* This file cleans SDC data. */
/*
Date: 2020.03.13


/******************************/		
Input files:
/******************************/		

	- (1) /Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/SDC/SDC_IssuerSaleDate_19902014.csv.
	
cusip_6	saledate	county	issuer	ratingagency
729773	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729778	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729780	01/02/90	Hennepin	Plymouth City-Minnesota	SP
729783	01/02/90	Hennepin	Plymouth City-Minnesota	SP
864048	01/02/90	Door	Sturgeon Bay-Wisconsin	
864056	01/02/90	Door	Sturgeon Bay-Wisconsin	
944048	01/02/90	Kosciusko	Wawasee Comm School Corp	
944050	01/02/90	Kosciusko	Wawasee Comm School Corp	
947644	01/02/90	Weber	Weber Co-Utah	
947648	01/02/90	Weber	Weber Co-Utah	
947651	01/02/90	Weber	Weber Co-Utah	
947659	01/02/90	Weber	Weber Co-Utah	
004590	01/03/90	Franklin-Hardin	Ackley City-Iowa	
004606	01/03/90	Franklin-Hardin	Ackley City-Iowa	
004613	01/03/90	Franklin-Hardin	Ackley City-Iowa	
048339	01/03/90	Atlantic	Atlantic City-New Jersey	
048343	01/03/90	Atlantic	Atlantic City-New Jersey	
048339	01/03/90	Atlantic	Atlantic City-New Jersey	MOODY;SP
048343	01/03/90	Atlantic	Atlantic City-New Jersey	MOODY;SP
	
	
*/
/******************************************************************************/

clear all 
set more off 
set matsize 750

// cd "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\SDC"
cd "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/SDC"
global data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Analysis/Round 2 new"



***********************************************************************/
/* SDC data cleaning */
**********************************************************************/



use SDC_IssuerSaleDate_19902014, clear // created by 01_sdc_issuer.R


* Sale date
gen year = substr(saledate, 7,2) 
gen month = substr(saledate, 1,2)
gen day = substr(saledate, 4,2)

destring year, replace
destring month, replace
destring day, replace
replace year = 2000+year if year <=14
replace year = 1900+year if year >=90 & year <=99 & year !=.

tab year
tab month
tab day
count 
*857,323

drop saledate
gen saledate = mdy(month, day, year)
format saledate %td

* Ratinng agency
replace ratingagency = lower(ratingagency)
tab ratingagency

gen moody_rated = strpos(ratingagency, "moody")>0
gen sp_rated = strpos(ratingagency, "sp")>0
gen fitch_rated = strpos(ratingagency, "fitch")>0
count if moody_rated == 0 & sp_rated == 0 & fitch_rated == 0 & rateddealflag == "Yes" 
*0

gen rated = rateddealflag == "Yes"

* Credit enhancement
gen insurance = creditenhancetype != ""

* Callable
gen call = callable == "Yes"
tab call

* Go vs reveneu
gen go = security == "GO"


* amount of issue
destring amountofissue, replace force 
*forced is used because missing amount is denoted by NA.


* Maturity

gen yearm = substr(maxmaturity, 1,4)
gen monthm = substr(maxmaturity, 6,2)
gen daym = substr(maxmaturity,9,2)
destring monthm daym, replace
destring yearm, replace force
tab yearm
gen max_maturity = mdy(monthm, daym, yearm)
replace max_maturity = . if maxmaturity == "NA" | yearm <1990
format max_maturity %td
drop yearm monthm daym




* Underwriter
gen underwriter = leadmng 
*857,323










/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
* Generate issuer-year level data
/**********************************************************************/

preserve

gen year1 = year
replace year1 = year - 1 if month <=6

drop if year <= 1990 & month <=6
drop if year >=2014 & month>=7
tab year1
drop year
rename year1 year

* Compute average issuer-year rating, insurance, call, go
sort cusip_6 year

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6 year: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6 year: egen first_offer_date = min(saledate)
by cusip_6 year: egen last_offer_date = max(saledate)
by cusip_6 year: gen num_offer = _N
format first_offer_date last_offer_date %td
by cusip_6 year: egen nvals = total(amountofissue)
gen amountofissue0=amountofissue
replace amountofissue = nvals
drop nvals

* Max maturity

by cusip_6 year: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals
format max_maturity %td



* Drop duplicates
* Keep issuers that issue the highest amount of debt and highest frequency of debt
gsort cusip_6 year issuerdscrp -saledate state uop1
by cusip_6 year issuerdscrp: egen nvals = total(amountofissue0) 
** total amount
by cusip_6 year issuerdscrp: gen nvals1 = _N 
** total number
by cusip_6 year issuerdscrp: gen nvals0 =_n ==1

keep if nvals0 == 1
drop nvals0
gsort cusip_6 year -saledate
by cusip_6 year: egen nvals2 = max(nvals) 
** max total amount of debt by cusip_6 year and issuerdscrp
keep if nvals == nvals2 
** keep issuers that issue the highest total amount of debt.
duplicates report cusip_6 year


by cusip_6 year: egen nvals3 = max(nvals1)
keep if nvals3 == nvals1
duplicates report cusip_6 year


sort cusip_6 year state uop1 issuerdscrp
by cusip_6 year: gen issuerdscrp1 = issuerdscrp[_n+1]
sort cusip_6 year 



keep cusip_6 year moody_rated rated sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state amountofissue issuerdscrp issuerdscrp1 uop1 underwriter issuer
sort cusip_6 year state uop1 issuerdscrp issuerdscrp1
cap drop nvals*
by cusip_6 year: gen nvals = _n==1
keep if nvals == 1
drop nvals

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable rated "issuer is rated"
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"
		label variable amountofissue "total annual issuance amount"
		label variable uop1 "purpose"
		label variable underwriter "underwriter"
		label variable issuer "issuer name"

count 
* 387,174 issuer-year


saveold "${data}/sdc_issuer_year20190530.dta", replace version(12)
restore
















/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/* Issuer level: debt outstanding */
/**********************************************************************/


preserve

keep if saledate <= mdy(06,30,2010) & max_maturity >= mdy(06,30,2010) & max_maturity !=. 
keep if rated ==1
sort cusip_6

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6: egen first_offer_date = min(saledate)
by cusip_6: egen last_offer_date = max(saledate)
by cusip_6: gen num_offer = _N
format first_offer_date last_offer_date %td

* Max maturity

by cusip_6: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals

duplicates drop cusip_6, force
keep cusip_6 moody_rated  sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"

count 
*39,158

saveold "${data}/sdc_debtoustanding_20190530.dta", replace version(12) // only sample itself is used. 

restore





/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/* access market */


preserve

keep if saledate <= mdy(06,30,2010) & saledate >= mdy(07,01,2006) 
sort cusip_6

capture drop nvals
foreach var of varlist moody_rated sp_rated fitch_rated rated insurance call go {
	by cusip_6: egen nvals = mean(`var')
	replace `var' = nvals
	drop nvals
}

* Offer
by cusip_6: egen first_offer_date = min(saledate)
by cusip_6: egen last_offer_date = max(saledate)
by cusip_6: gen num_offer = _N
format first_offer_date last_offer_date %td
by cusip_6: egen nvals = total(amountofissue)
gen amountofissue0=amountofissue
replace amountofissue = nvals
drop nvals

* Max maturity

by cusip_6: egen nvals = max(max_maturity)
replace max_maturity = nvals
drop nvals

* Drop duplicates
* Keep issuers that issue the highest amount of debt and highest frequency of debt
gsort cusip_6 issuerdscrp -saledate state uop1 issuer
by cusip_6 issuerdscrp: egen nvals = total(amountofissue0) 
** total amount
by cusip_6 issuerdscrp: gen nvals1 = _N 
** total number
by cusip_6 issuerdscrp: gen nvals0 =_n ==1

keep if nvals0 == 1
drop nvals0
gsort cusip_6 issuerdscrp -saledate state uop1 issuer
by cusip_6: egen nvals2 = max(nvals) 
** max total amount of debt by cusip_6 and issuerdscrp
keep if nvals == nvals2 
** keep issuers that issue the highest total amount of debt.
duplicates report cusip_6
/*
--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |        32362             0
        2 |           18             9
--------------------------------------
*/

by cusip_6: egen nvals3 = max(nvals1)
keep if nvals3 == nvals1
duplicates report cusip_6
/*
--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |        32362             0
        2 |           18             9
--------------------------------------
*/
sort cusip_6 year state uop1 issuerdscrp issuer
by cusip_6 (year state uop1 issuerdscrp issuer): gen issuerdscrp1 = issuerdscrp[_n+1]
cap drop nvals
by cusip_6: gen nvals = _n==1
keep if nvals ==1
drop nvals


keep cusip_6 rated moody_rated sp_rated fitch_rated first_offer_date last_offer_date num_offer max_maturity insurance call go state issuerdscrp issuerdscrp1 amountofissue uop1  underwriter issuer

		label variable cusip_6 "6 digit cusip"
		label variable moody_rated "issuer is rated by moody's"
		label variable sp_rated "issuer is rated by sp"
		label variable fitch_rated "issuer is rated by fitch
		label variable first_offer_date "issuer's first offering"
		label variable last_offer_date "issuer's last offering"
		label variable num_offer "issuer's total number of offering"
		label variable max_maturity "issuer's longest maturity bond offered"
		label variable insurance "percentage of insured bonds"
		label variable call "percentage of callable issues"
		label variable go "percentage of go issues"
		label variable amountofissue "total annual issuance amount"

count 
*32,371


saveold "${data}/sdc_accessmarket_20190530.dta", replace version(12)

restore










/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/

* Issue level

preserve

keep if saledate <= mdy(06,30,2010) & saledate >= mdy(07,01,2006) 
sort cusip_6 saledate 
by cusip_6 saledate: gen nvals =_n==1
keep if nvals ==1
drop nvals

gen issue_id = _n
keep cusip_6 saledate year

saveold "${data}/sdc_issuereg_20190304.dta", replace version(12)

restore










/******************************************************************************/

/*


This file creates the data for financial disclosures. 
Date: 2020.03.13

	
*/


global Data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/Timeliness"
global Data "/Users/szho/Documents/Temp"


* Change formats
forvalues i=2009(1)2014{
	use "${Data}/cd`i'.dta", clear
	saveold "${Data}/cd`i'.dta", replace version(12)
}


























/******************************************************************************/

* STEP 1: CREATE FINANCIAL DISCLOSURE DATA

/******************************************************************************/


* Other disclosures that are CAFR

import excel "${Data}/OtherVoluntaryFinancialDisclosures.xlsx", sheet("Sheet1") firstrow clear
keep othervoluntarydisclosure CAFR
keep if CAFR == 1
sort othervoluntarydisclosure
duplicates drop othervoluntarydisclosure, force
save "${Data}/tempCAFR.dta", replace




* Create disclosure data

forvalues i=2009(1)2014{

	use "${Data}/cd`i'.dta", clear
	
	sort submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure submissiondatetime
	drop if financialdisclosurecat == "NA" // drop non-financial disclosures
	duplicates drop submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure, force // drop duplicates 
	
	gen other = financialdisclosurecat == "OtherFinancialVoluntaryInformation"
	by submissionidentifier: egen nvals = sd(other)
	drop if other == 1 & nvals >0 & nvals !=. // drop footnote disclosures 
	drop nvals other
	
	
	keep submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure submissiondatetime endingdate periodtype
		
	
	* Create disclosure types
	
	gen CAFR = financialdisclosurecat == "AuditedFinancialStatementsOrCAFR15c212"
	gen AnnualFinancial =  financialdisclosurecat == "AnnualFinancialInformationOperatingData15c212"
	gen QMFinancial = financialdisclosurecat == "QuarterlyMonthlyFinancialInformation" | financialdisclosurecat == "InterimAdditionalFinancialInformationOperatingData"
	gen DisclosureFailure = financialdisclosurecat == "FailureToProvideAnnualFinancialInformationAsRequired15c212"
	gen Budget = financialdisclosurecat == "Budget" 

	
	* Create date
	replace submissiondatetime = trim(submissiondatetime)
	gen date = substr(submissiondatetime, 1, 10)
	
	
	keep submissionidentifier cusip9 financialdisclosurecat othervoluntarydisclosure endingdate periodtype date CAFR AnnualFinancial QMFinancial DisclosureFailure Budget
	
	save "${Data}/tempcd_`i'.dta", replace
	
}

use "${Data}/tempcd_2009.dta", clear
forvalues i=2010(1)2014{
	append using "${Data}/tempcd_`i'.dta"
}


saveold "${Data}/cd2009to2014.dta", replace version(12)
shell rm "${Data}/temp"*






/******************************************************************************/

* STEP 2: DROP DUPLICATE ANNUAL FINANCIAL STATEMENTS

/******************************************************************************/


use "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\Timeliness\cd2009to2014.dta", clear

sort cusip9 date
duplicates tag cusip9 date, gen(tag)

bysort cusip9 date: egen sumcafr=sum(CAFR)
bysort cusip9 date: egen sumannual=sum( AnnualFinancial )

drop if tag>0 & sumcafr==1 & sumannual==1 & AnnualFinancial==1

saveold cd2009to2014_20180725, replace version(12)






/******************************************************************************/

* STEP 3: CREATE FILE TO COUNT NUMBER OF MATERIAL EVENTS

/******************************************************************************/


cd "C:\Users\dsamuels\Dropbox (MIT)\Municipal Disclosure\Rawdata\Timeliness"

forvalues i=2009(1)2014{

	use "cd`i'.dta", clear
	
	sort submissionidentifier cusip9 eventdisclosurecat
	drop if eventdisclosurecat == "NA" 
	
	* Create date
	replace submissiondatetime = trim(submissiondatetime)
	gen date = substr(submissiondatetime, 1, 10)
	
	keep submissionidentifier cusip9 eventdisclosurecat submissiondatetime endingdate periodtype date
		
	save "tempcd_`i'.dta", replace
}

use "tempcd_2009.dta", clear
forvalues i=2010(1)2014{
	append using "tempcd_`i'.dta"
}


saveold "events2009to2014.dta", replace version(12)


























/*


* Match financial statement sample cd2009to2014 (produced by FinancialDisclosure.do)
* with the regression sample




*/



global rawdata "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Rawdata/Timeliness"
global data "/Users/szho/Dropbox/My Projects/Municipal Disclosure/Analysis"






********************************************************************************
********************************************************************************

* Regression sample from gsz_20200313.do

********************************************************************************
********************************************************************************

use "${data}/sample_20190530.dta", clear

sort cusip_6 year
egen cusip_id = group(cusip_6)
egen state_id = group(state)
xtset cusip_id year


* ISSUE INDICATOR
gen issue = log_amountofissue >0


* CREATE UNDERWRITER VARIABLES
capture drop leadmng*
sort cusip_6 year
merge 1:1 cusip_6 year using tempunderwriter, keep (1 3) nogen // tempunderwriter is a balanced panel by cusip_6 year
gen missuw = leadmng == ""

foreach var of varlist leadmng leadmng1{
	replace `var' = "NA" if missuw == 1
}
replace mktshr = 0  if missuw == 1
egen uwid1 = group(leadmng1)


* ISSUER TYPE FIXED EFFECTS
egen issuerdscrp_pre_id = group(issuerdscrp_pre)


* SECTOR FIXED EFFECTS
replace uop1_pre="NA" if uop1_pre==""
egen uop1_pre_id = group(uop1_pre)


 
* IMPORT LAGGED MACRO VARIABLES
sort state year
merge m:1 state year using "${data}/tempmacro.dta", keep(1 3) nogen

gen gsp_avg_sc=gsp_avg/10000
gen pci_avg_sc=pci_avg/100
gen hpi_avg_sc=hpi_avg/100








/***********************************************/
* TABLE 3 - MAIN RESULTS
/***********************************************/

capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre ==1 & insurance_pre<1

capture drop samp1
xtreg financials_all treated_post post log_amountofissue call go gsp_avg_sc pci_avg_sc hpi_avg_sc unemploy_avg if sample==1
gen samp1=1 if e(sample)

keep if samp1==1
keep cusip_6
duplicates drop cusip_6, force

sort cusip_6
save "${rawdata}/tempregsample.dta", replace

























********************************************************************************
********************************************************************************

* Load linking table

********************************************************************************
********************************************************************************

import delimited "/Volumes/Frank's Passport for Mac Files/msrb_disclosure/SubmissionFileLink2009.csv", encoding(ISO-8859-1) clear
forvalues i=2010(1)2015{
	preserve 
	import delimited "/Volumes/Frank's Passport for Mac Files/msrb_disclosure/SubmissionFileLink`i'.csv", encoding(ISO-8859-1) clear
	save "${rawdata}/tempSubmissionFileLink`i'", replace
	restore
	append using "${rawdata}/tempSubmissionFileLink`i'"
}
shell rm "${rawdata}/tempSubmissionFileLink"*
capture erase "${rawdata}/tempSubmissionFileLink"*

sort submissionidentifier fileidentifier year month submissiontransactiondatetime
by submissionidentifier fileidentifier: gen nvals=_n==1
keep if nvals ==1
drop nvals
keep submissionidentifier fileidentifier year month
save "${rawdata}/SubmissionFileLink.dta", replace




















********************************************************************************
********************************************************************************


* Merge linking table with regression sample

********************************************************************************
********************************************************************************

use "${rawdata}/cd2009to2014.dta", clear

gen cusip_6 = substr(cusip9, 1, 6)
merge m:1 cusip_6 using  "${rawdata}/tempregsample.dta", keep(3)
duplicates drop submissionidentifier, force

keep submissionidentifier
sort submissionidentifier

merge 1:m submissionidentifier using "${rawdata}/SubmissionFileLink.dta", nogen keep(3)
gen month1 = string(month)
replace month1 = "0" + month1 if strlen(month1)==1

keep if year<=2013 | (year ==2014 & month<=6) 
saveold "${rawdata}/SubmissionFileLinkRegSample.dta", replace version(12)
shell rm "${rawdata}/tempregsample.dta"

capture erase "${rawdata}/tempregsample.dta"
















clear all 
set more off 
set matsize 750


global author "/Users/szho/Dropbox/My Projects/Municipal Disclosure" 
cd "${author}/Analysis/Round 3"





/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   CREATE LAGGED MACRO VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

/*

* This file computes the annual macro variables by states and the outputs are later
* merged to our regression sample to compute the lagged macro variables. 


Input files:
	- Macro data in the folder: RawData/Economic_Characteristics

	
Output file:
	- By year measure of economic variables
	
*/

global macrolag "${author}/Rawdata/Economic_Characteristics"


preserve

clear

* GSP
import delimited "${macrolag}/GSP/GSP20160129.csv", encoding(ISO-8859-1)clear
sort state year
save tempgsp, replace

* PCI
import delimited "${macrolag}/Personal_Income/percapitaIncome20170909.csv", encoding(ISO-8859-1)clear
sort state year
save temppci, replace

* HPI
import excel "${macrolag}/HPI/HPI_PO_state.xls", clear first
sort state yr qtr
by state yr: egen hpi_avg = mean(index_sa)
rename yr year
keep state year hpi_avg
duplicates drop state year, force
sort state year
save temphpi, replace

* UNEMP
import delimited "${macrolag}/Unemployment/Unemploy20160908.csv", encoding(ISO-8859-1)clear
sort state year
by state year: egen unemploy_avg = mean(unemploy)
keep state year unemploy_avg
duplicates drop state year, force
sort state year

* Combine outputs
merge 1:1 state year using temphpi
keep if _merge == 3
drop _merge

merge 1:1 state year using temppci
keep if _merge == 3
drop _merge

merge 1:1 state year using tempgsp
keep if _merge == 3
drop _merge

rename gsp gsp_avg
rename percapitaincome pci_avg

replace year = year +1 
* so that year 2008 is matched to year 2009 for our data
sort state year

winsor2 unemploy_avg, replace cuts(1 99)
winsor2 gsp_avg, replace cuts(1 99)
winsor2 pci_avg, replace cuts(1 99)
winsor2 hpi_avg, replace cuts(1 99)

save tempmacro, replace

restore







/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   IMPORT 2NDARY MARKET TRADE VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/


/*
	- RetailTrades20190530, number and volume of trades at the bond-year level, created by 05_retail_trades.sas 
*/

* INDICATOR FOR THE EXISTENCE OF SECONDARY MARKET TRADE in 2009

preserve

use RetailTrades20190530, clear
gen num = inst1_num_2nd + inst2_num_2nd + retail1_num_2nd + retail2_num_2nd
keep if num > 1 & year ==2009
gen trade1=1
keep cusip_6 trade1
duplicates drop
sort cusip_6
save XSretail1, replace

restore



* CHANGES IN INVESTOR BASE VARIABLES

preserve
use RetailTrades20190530, clear 
sort cusip9 year

sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}
gen pct_retail_num_2nd = (retail1_num_2nd_t + retail2_num_2nd_t) / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
gen pct_inst_num_2nd   = (inst2_num_2nd_t)  / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
keep cusip_6 year pct_retail_num_2nd pct_inst_num_2nd
duplicates drop
sort cusip_6 year

save PctTrade, replace

restore



* For alternative control group:

preserve
use RetailTradesAltControl20190530, clear
sort cusip9 year

* Aggregate trading at the issuer level
sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}
gen pct_retail_num_2nd = (retail1_num_2nd_t + retail2_num_2nd_t) / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
gen pct_inst_num_2nd   = (inst2_num_2nd_t)  / (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)
keep cusip_6 year pct_retail_num_2nd pct_inst_num_2nd
duplicates drop
sort cusip_6 year
rename pct_retail_num_2nd pct_retail_num_2nd_alt
rename pct_inst_num_2nd pct_inst_num_2nd_alt

save PctTrade_altcontrol, replace
restore



preserve

use RetailTrades20190530, clear 

* Aggregate total number of trades at the issuer-year level:

sort cusip_6 year
foreach var of varlist retail1_num_2nd retail2_num_2nd inst1_num_2nd inst2_num_2nd {
	by cusip_6 year: egen `var'_t = total(`var')
}

gen total_trade_2nd = (retail1_num_2nd_t + retail2_num_2nd_t + inst1_num_2nd_t + inst2_num_2nd_t)

keep cusip_6 year total_trade_2nd
duplicates drop cusip_6 year, force
sort cusip_6 year

sum total_trade_2nd,d

save TotalTrade, replace

restore














/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   CREATE UNDERWRITER VARIABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/


/*
 
Input file:
		- Underwriters20190530.csv, created by 06_underwriters.R, which is at the cusip_6 by year level. 
		# 1. For each underwriter and year, construct the amount of debt issuance by each underwriter
		# as a percentage of total debt issuance in that year. 	
		# 2. If multiple underwriters are used in a given issue, then assign the issuer's underwriter market share
		# with the largest market share. 
		# For example, for a cusip_6 issuer and 2010, there might be underwriters A, B, C and A has the largest market share in 2010. 
		# We assign the issuer's underwriter market share as A's market share. 
		
*/


* DEFINE UNDERWRITER FE 

preserve

clear

import delimited "Underwriters20190530.csv", encoding(ISO-8859-1) clear

drop v1
duplicates report cusip_6 year
* sanity check, should be zero

egen cusipid = group(cusip_6)
xtset cusipid year

tsfill,full
tab year
count if cusipid == .
* 0

* Fill in data of the missing year
sort cusipid year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist mktshr leadmng leadmng1 numstate{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

gsort cusipid -year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist mktshr leadmng leadmng1 numstate{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

* Check if all non-missing
foreach var of varlist cusip_6 mktshr leadmng leadmng1 numstate{
	count if missing(`var')
}

drop cusipid
sort cusip_6 year
save tempunderwriter, replace

restore



* DEFINE UNDERWRITER XS VARIABLES
preserve

clear

import delimited "Underwriters_2_20190530.csv", encoding(ISO-8859-1) clear

drop v1
duplicates report cusip_6 year
* sanity check, should be zero

egen cusipid = group(cusip_6)
xtset cusipid year

tsfill,full
tab year
count if cusipid == .
* 0

* Fill in data of the missing year
sort cusipid year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

gsort cusipid -year
by cusipid: replace cusip_6 = cusip_6[_n-1] if cusip_6 ==""
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	by cusipid: replace `var'=`var'[_n-1] if missing(`var')
}

* Check if all non-missing
foreach var of varlist leadmng1 mktshr numstate pctgo pctrev mktsharestate herfin{
	count if missing(`var')
}

drop cusipid
sort cusip_6 year
save tempunderwriter_20190530, replace

restore










/******************************************************************************/
/******************************************************************************/
/******************************************************************************/




* Timeliness




/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

global Timeliness  "${author}/Rawdata/Timeliness"

preserve

use "${Timeliness}/cd2009to2014.dta", clear 
// created by 03_disclosures.do 
// Includes only financial disclosures

* Submission date
gen year=substr(date, 1,4)
gen month=substr(date,6,2)
gen day=substr(date,9,2)
destring year month day, replace force
gen date1 = mdy(month, day, year)
keep if date1 !=.

* Fiscal year end date, benchmark year
drop year month day
gen year=substr(endingdate, 1,4)
gen month=substr(endingdate,6,2)
gen day=substr(endingdate,9,2)
destring year month day, replace force
gen fye=mdy(month,day,year)
keep if fye !=.

* Timeliness measure
gen timeliness = date1 - fye // larger, less timely!!!
gen cusip_6 = substr(cusip9,1,6)
keep if cusip_6 !=""
gen year1= year
forvalues i=2010(1)2014{
	replace year = `i'-1 if year1 == `i' & month<=6
}
drop year1 month day
keep if year>=2009 & year<=2013

sort cusip_6 year
by cusip_6 year: egen timeliness2 = min(timeliness) // select the most timely disclosures
duplicates drop cusip_6 year, force
keep cusip_6 year timeliness2

saveold "timeliness2_20190304.dta", replace version(12)

restore












/* COMPUTE AVERAGE ISSUER-LEVEL CREDIT RATING:*/

preserve

use "sample_20190530.dta", clear
keep cusip_6 year treated state moody_rated_pre sp_rated_pre
sort cusip_6 year treated state moody_rated_pre sp_rated_pre
by cusip_6: gen nvals = _n==1
keep if nvals ==1
drop nvals
save "tempsample0.dta", replace

restore

preserve

use "sample_20190530.dta", clear
capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre == 1 & insurance_pre<1
keep if sample ==1
keep cusip_6 year
save "tempratingagency1.dta", replace

restore


global rawdata "${author}/Rawdata/SDC"
use "${rawdata}/SDC_BondSaleDate_19902014.dta", clear

* Sale date
gen year = substr(saledate, 7,2) 
gen month = substr(saledate, 1,2)
gen day = substr(saledate, 4,2)

destring year, replace
destring month, replace
destring day, replace
replace year = 2000+year if year <=14
replace year = 1900+year if year >=90 & year <=99 & year !=.

tab year
tab month
tab day
count // 1,564,258

drop saledate
gen saledate = mdy(month, day, year)
format saledate %td

gen year1 = year
replace year1 = year - 1 if month <=6

drop if year <= 1990 & month <=6
drop if year >=2014 & month>=7
tab year1
drop year
rename year1 year


* Rating agency
replace ratingagency = lower(ratingagency)
tab ratingagency

gen moody_rated = strpos(ratingagency, "moody")>0
gen sp_rated = strpos(ratingagency, "sp")>0
gen fitch_rated = strpos(ratingagency, "fitch")>0
count if moody_rated == 0 & sp_rated == 0 & fitch_rated == 0 & rateddealflag == "Yes" // 0
gen rated = rateddealflag == "Yes"


* Moodys long term underlying rating
gen moody_rate_scale = .
replace moody_rate_scale = 9 if strpos(moodylongtermunderlying, "Aaa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 8 if strpos(moodylongtermunderlying, "Aa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 7 if strpos(moodylongtermunderlying, "A")>0 & moody_rate_scale ==.
replace moody_rate_scale = 6 if strpos(moodylongtermunderlying, "Baa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 5 if strpos(moodylongtermunderlying, "Ba")>0 & moody_rate_scale ==.
replace moody_rate_scale = 4 if strpos(moodylongtermunderlying, "B")>0 & moody_rate_scale ==.
replace moody_rate_scale = 3 if strpos(moodylongtermunderlying, "Caa")>0 & moody_rate_scale ==.
replace moody_rate_scale = 2 if strpos(moodylongtermunderlying, "Ca")>0 & moody_rate_scale ==.
replace moody_rate_scale = 1 if strpos(moodylongtermunderlying, "C")>0 & moody_rate_scale ==.
replace moody_rate_scale = 0 if strpos(moodylongtermunderlying, "D")>0 & moody_rate_scale ==.
replace moody_rate_scale = 0 if strpos(moodylongtermunderlying, "E")>0 & moody_rate_scale ==.
tab moody_rate_scale 

gen sp_rate_scale = .
replace sp_rate_scale = 9 if strpos(splongtermunderlying, "AAA")>0 & sp_rate_scale ==.
replace sp_rate_scale = 8 if strpos(splongtermunderlying, "AA")>0 & sp_rate_scale ==.
replace sp_rate_scale = 7 if strpos(splongtermunderlying, "A")>0 & sp_rate_scale ==.
replace sp_rate_scale = 6 if strpos(splongtermunderlying, "BBB")>0 & sp_rate_scale ==.
replace sp_rate_scale = 5 if strpos(splongtermunderlying, "BB")>0 & sp_rate_scale ==.
replace sp_rate_scale = 4 if strpos(splongtermunderlying, "B")>0 & sp_rate_scale ==.
replace sp_rate_scale = 3 if strpos(splongtermunderlying, "CCC")>0 & sp_rate_scale ==.
replace sp_rate_scale = 2 if strpos(splongtermunderlying, "CC")>0 & sp_rate_scale ==.
replace sp_rate_scale = 1 if strpos(splongtermunderlying, "C")>0 & sp_rate_scale ==.
replace sp_rate_scale = 0 if strpos(splongtermunderlying, "D")>0 & sp_rate_scale ==.
replace sp_rate_scale = 0 if strpos(splongtermunderlying, "E")>0 & sp_rate_scale ==.

gen fitch_rate_scale = .
replace fitch_rate_scale = 9 if strpos(fitchlongtermunderlying, "AAA")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 8 if strpos(fitchlongtermunderlying, "AA")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 7 if strpos(fitchlongtermunderlying, "A")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 6 if strpos(fitchlongtermunderlying, "BBB")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 5 if strpos(fitchlongtermunderlying, "BB")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 4 if strpos(fitchlongtermunderlying, "B")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 3 if strpos(fitchlongtermunderlying, "CCC")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 2 if strpos(fitchlongtermunderlying, "CC")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 1 if strpos(fitchlongtermunderlying, "C")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 0 if strpos(fitchlongtermunderlying, "D")>0 & fitch_rate_scale ==.
replace fitch_rate_scale = 0 if strpos(fitchlongtermunderlying, "E")>0 & fitch_rate_scale ==.

egen rating = rowmean(sp_rate_scale fitch_rate_scale moody_rate_scale) // command ignores missing value
tab rating

cap drop state 
gen cusip_6 = substr(cusip1, 1,6)
merge m:1 cusip_6 using "tempsample0.dta", nogen keep(3)
sort cusip1 rating moody_rated sp_rated fitch_rated treated amount creditenhancer year state moody_rated_pre sp_rated_pre

merge m:1 cusip_6 year using "tempratingagency1.dta"
drop if _merge ==2
bys cusip_6: egen nvals = mean(_merge == 3)
keep if nvals >0
drop _merge nvals

keep cusip1 cusip_6 rating moody_rated sp_rated fitch_rated treated amount creditenhancer rated year state moody_rated_pre sp_rated_pre
sort cusip1 rating moody_rated sp_rated fitch_rated treated amount creditenhancer year state moody_rated_pre sp_rated_pre
by cusip1: gen nvals = _n==1
keep if nvals ==1
drop nvals

save "tempsdc.dta", replace // bond characteristics at the bond level, also used for parallel trends for yield. 






* Compute rating at issuer-year level. 
use "tempsdc.dta", clear

keep cusip_6 year rating
sort cusip_6 year
by cusip_6 year: egen avgrating = max(rating)
keep if avgrating !=.
duplicates drop cusip_6 year, force


preserve

use sample_20190530, clear
keep cusip_6 year
sort cusip_6 year
save tempsample1, replace

restore

merge 1:1 cusip_6 year using tempsample1 // to get back to a balanced panel
sort cusip_6 year
replace avgrating = avgrating[_n-1] if cusip_6 == cusip_6[_n-1] & avgrating ==.
keep cusip_6 year avgrating 

saveold IssuerRating20190530, replace version(12)

 
preserve
use IssuerRating20190530, clear
keep if year==2009
save avgrating2009, replace

restore 











/* Parallel trends */


use "sdc_issuer_year20190530.dta", clear // created by 02_sdc_processing.do, unique issuer-year level data

keep cusip_6 year moody_rated sp_rated fitch_rated call go amountofissue
egen cusipid = group(cusip_6)

xtset cusipid year
tsfill, full
sort cusipid year
foreach var of varlist moody_rated sp_rated fitch_rated{
	replace `var' = `var'[_n-1] if `var' ==. & `var'[_n-1] !=. & cusipid == cusipid[_n-1]
	rename `var' `var'_issuer
}
replace cusip_6 = cusip_6[_n-1] if cusipid == cusipid[_n-1] & cusip_6[_n-1] !=""
drop if cusip_6 ==""
sort cusip_6 year

replace moody_rated_issuer = moody_rated >0 & moody_rated !=.
replace sp_rated_issuer = sp_rated >0 & sp_rated !=.
replace fitch_rated_issuer = fitch_rated >0 & fitch_rated !=.

gen log_amountofissue = log(1 + amountofissue)
replace log_amountofissue = 0 if log_amountofissue ==.
gen call_issuer = call >0 & call !=.
gen go_issuer = go >0 & go !=.

keep cusip_6 year moody_rated_issuer sp_rated_issuer fitch_rated_issuer call_issuer go_issuer log_amountofissue
save "temptrends_rating.dta", replace










/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

//   SAMPLE TO GENERATE TABLES

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/



/***********************************************/

global author "/Users/szho/Dropbox/My Projects/Municipal Disclosure" 


cd "${author}/Analysis/Round 3"
global Timeliness  "${author}/Rawdata/Timeliness" 

* CHOOSE A CLUSTERING DIMENSION
global clustervar cusip_id
// global clustervar state_id



use sample_20190530.dta, clear

sort cusip_6 year
egen cusip_id = group(cusip_6)
egen state_id = group(state)
xtset cusip_id year

* ISSUE INDICATOR
gen issue = log_amountofissue >0


* CREATE UNDERWRITER VARIABLES
capture drop leadmng*
sort cusip_6 year
merge 1:1 cusip_6 year using tempunderwriter, keep (1 3) nogen // tempunderwriter is a balanced panel by cusip_6 year
gen missuw = leadmng == ""

foreach var of varlist leadmng leadmng1{
	replace `var' = "NA" if missuw == 1
}
replace mktshr = 0  if missuw == 1
egen uwid1 = group(leadmng1)


* ISSUER TYPE FIXED EFFECTS
egen issuerdscrp_pre_id = group(issuerdscrp_pre)


* SECTOR FIXED EFFECTS
replace uop1_pre="NA" if uop1_pre==""
egen uop1_pre_id = group(uop1_pre)


* PRE-PERIOD ISSUER RATING FIXED EFFECTS
merge m:1 cusip_6 year using avgrating2009, keep(1 3) nogen
bysort cusip_6 (year): egen avgrating_pre=sum(avgrating)
replace avgrating_pre=. if avgrating_pre==0 & avgrating!=0
replace avgrating_pre=-1 if missing(avgrating_pre) // create -1 group for missing longtermratings
egen avgrating_pre_id = group(avgrating_pre)
drop avgrating

 
* IMPORT LAGGED MACRO VARIABLES
sort state year
merge m:1 state year using tempmacro, keep(1 3) nogen
gen gsp_avg_sc=gsp_avg/10000
gen pci_avg_sc=pci_avg/100
gen hpi_avg_sc=hpi_avg/100


* STATE BY YEAR FIXED EFFECTS
gen stateXyear = year*100+state_id


* SAMPLE FOR OUR ANALYSES
capture drop sample
gen sample=1 if ( (treated==1 )|(treated==0 & sp_rated_pre==1 & moody_rated_pre==0) ) & fitch_rated_pre==0 & rated_pre ==1 & insurance_pre<1

capture drop samp1
xtreg financials_all treated_post post log_amountofissue call go  gsp_avg_sc pci_avg_sc hpi_avg_sc unemploy_avg if sample==1
gen samp1=1 if e(sample)
tab samp1


















brainstormingobra/superpowersYou MUST use this before any creative work - creating features, building components, adding functionality, modifying behavior, or planning anything new, in software or out of it (a talk, a business, a renovation).Stars: 323kUpdated Claude CodeCodexVerifiedInstall
brand-guidelinesanthropics/skillsApplies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
frontend-designanthropics/skillsGuidance for distinctive, intentional visual design when building new UI or reshaping an existing one. Helps with aesthetic direction, typography, and making choices that don't read as templated defaults.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
mcp-builderanthropics/skillsGuide for creating high-quality MCP (Model Context Protocol) servers that enable LLMs to interact with external services through well-designed tools. Use when building MCP servers to integrate external APIs or services, whether in Python (FastMCP) or Node/TypeScript (MCP SDK).Stars: 198kUpdated Claude CodeCodexVerifiedInstall