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

	Updates to Margo Schlanger, Trends in Prisoner Litigation, as the 
	PLRA Enters Adulthood, 5 U.C. Irvine L. Rev. 153 (2015)
	
	Part 3: Creating Updated Tables
	
	Updated: April 28, 2021
	
*******************************************************************************/

global project "C:\Users\gmarquez\Box\Empirical Research Service\Margo Schlanger\Empirical Research Service\Population and Filings Replication"

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

/* 	Table A: Prison and Jail Population and Prisoner Civil Rights Filings in
	Federal District Court, Fiscal Years 1970-Present */

cd "$project\Data"

clear
use "population_and_filings_1970-2020.dta", clear

cd "$project\Output"
putexcel set "TableAIncarceratedPopulationandPrisonJailCivilRightsFilings.xlsx", sheet("Sheet1") modify

sum year
local latestyear=`r(max)'
#delimit ;
putexcel A1="Table A: Incarcerated Population and Prison/Jail Civil Rights Filings in Federal District Court, Fiscal Years 1970-`latestyear'"
	B2="Incarcerated Population"
	F2="Prisoner Civil Rights / Conditions Filings in Fed. District Court"
	A3="Fiscal Year"
	B3="Total Incar. Pop."
	C3="State Prison Pop."
	D3="Federal Prison Pop."
	E3="Local Jail Pop."
	F3="Total Filings"
	G3="Non-federal Def'ts"
	H3="Federal Def'ts"
	I3="Filings / 1,000 Incar. Pop.";
#delimit cr
local row=4
forvalues i=1970/`latestyear' {
	sum year if year==`i' & state_num==-1
	putexcel A`row'=`r(mean)'
	sum incar if year==`i' & state_num==-1
	capture putexcel B`row'=`r(mean)'
		if _rc!=0 {
		putexcel B`row'="N/A"
		}
	sum prison if year==`i' & state_num==-1
	capture putexcel C`row'=`r(mean)'
		if _rc!=0 {
		putexcel C`row'="N/A"
		}
	sum fed if year==`i' & state_num==-1
	capture putexcel D`row'=`r(mean)'
		if _rc!=0 {
		putexcel D`row'="N/A"
		}
	sum jail if year==`i' & state_num==-1
	capture putexcel E`row'=`r(mean)'
		if _rc!=0 {
		putexcel E`row'="N/A"
		}
	sum filings if year==`i' & state_num==-1
	putexcel F`row'=`r(mean)'
	sum statefi if year==`i' & state_num==-1
	putexcel G`row'=`r(mean)'
	sum fedfi if year==`i' & state_num==-1
	putexcel H`row'=`r(mean)'
	sum FiRate if year==`i' & state_num==-1
	capture putexcel I`row'=`r(mean)'
		if _rc!=0 {
		putexcel I`row'="N/A"
		}
	local ++row
	}

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

/*	Table B: Pro Se Litigation in U.S. District Courts by Case Type, Cases 
	Terminated Fiscal Years 1996-Present  */

cd "C:\Users\gmarquez\Box\Empirical Research Service\Margo Schlanger\Empirical Research Service\Federal Judicial Center IDB"

clear
use "FJC Civil Cases 1970-31Dec2020.dta", clear
	
drop if maisano==1

/*	Note: the interest is in accounting for plaintiffs who litigated without
	counsel; this is described by prose==1 (Pro Se plaintiff, but no Pro Se
	defendants) and prose==3 (both Pro Se plaintiffs & defendants) */

/*	Creating case type variable in descending order of pro se litigation 
	percentages in FY2020 */
gen casetype=.
replace casetype=1 /* Prisoner Civil Rights */ if nossum==6
replace casetype=2 /* Habeas, Quasi Crim. */ if nossum==10
replace casetype=3 /* Bankruptcy */ if nossum==11
replace casetype=4 /* Civil Rights */ if nossum==4
replace casetype=5 /* Immigration */ if nossum==12
replace casetype=6 /* Other */ if nossum==13
replace casetype=7 /* Civil Rights, Empl. */ if nossum==5
replace casetype=8 /* Torts (Nonproduct) */ if nossum==2
replace casetype=9 /* Statutory Actions */ if nossum==8
replace casetype=10 /* Contract */ if nossum==1
replace casetype=11 /* Product Liability */ if nossum==3
replace casetype=12 /* U.S. Plaintiff */ if nossum==9
replace casetype=13 /* Labor and Empl. */ if nossum==7

cd "$project\Output"
putexcel set "TableB-ProSeLitigationinUSDistrictCourtsbyCaseType.xlsx", sheet("Sheet1") modify

/*	Note: table output is limited to complete fiscal years (i.e., most recent 
	fiscal year's numbers only included if data are available up to September 
	30 of that year) */
sum fyterm if month(termdate)==9 & (day(termdate)==28 | day(termdate)==29 ///
	| day(termdate)==30)
local latestyear=`r(max)'

#delimit ;
putexcel A1="Table B: Pro Se Litigation in U.S. District Courts, by Case Type, Cases Terminated Fiscal Years 1996-`latestyear'"
	A2="Case Category"
	B2="Prisoner Civ. Rights/ Conditions"
	C2="Habeas, Quasi-Criminal"
	D2="Bankr."
	E2="Civil Rights (Non-Emp.)"
	F2="Immig."
	G2="Other"
	H2="Civil Rights, Emp."
	I2="Torts (Non-product)"
	J2="Statutory Actions"
	K2="Contract"
	L2="Product Liability"
	M2="U.S. Plaintiff"
	N2="Labor & Emp."
	O2="Total"
	P2="Total w/o Prisoner or Habeas Cases";
#delimit cr
local row=3
forvalues y=1996/`latestyear' {
	putexcel A`row'=`y'
	count if fyterm==`y'
	local casetotal=`r(N)'
	count if (prose==1 | prose==3) & fyterm==`y'
	putexcel O`row'=`r(N)'/`casetotal'
	count if fyterm==`y' & casetype!=1 & casetype!=2
	local casetotal=`r(N)'
	count if (prose==1 | prose==3) & fyterm==`y' & casetype!=1 & casetype!=2
	putexcel P`row'=`r(N)'/`casetotal'
	local ncol=2
	forvalues i=1/13 {
		local col: word `ncol' of `c(ALPHA)'
		count if fyterm==`y' & casetype==`i'
		local casetypetotal=`r(N)'
		count if (prose==1 | prose==3) & fyterm==`y' & casetype==`i'
		putexcel `col'`row'=`r(N)'/`casetypetotal'
		local ++ncol
		}
	local ++row
	}
/* Replacing values for 1998, which is missing almost all pro se data */	
local ncol=2
forvalues i=1/15 {
	local col: word `ncol' of `c(ALPHA)'
	putexcel `col'5="No Data"
	local ++ncol
	}

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

/*	Table C. Outcomes in Prisoner Civil Rights Cases in Federal District Court,
	Fiscal Years 1988-Present	*/
	
cd "C:\Users\gmarquez\Box\Empirical Research Service\Margo Schlanger\Empirical Research Service\Federal Judicial Center IDB"

clear
use "FJC Civil Cases 1970-31Dec2020.dta", clear

/*	Note: Filing counts are only for origin codes 1, 2, and 7. Termination 
	counts and percentages use all origin codes */
keep if nos==550 | nos==555
drop if maisano==1
gen origin127=origin==1 | origin==2 | origin==7	

/*	Note: table output is limited to complete fiscal years (i.e., most recent 
	fiscal year's numbers only included if data are available up to September 
	30 of that year) */
sum fyterm if month(termdate)==9 & (day(termdate)==28 | day(termdate)==29 ///
	| day(termdate)==30)
local latestyear=`r(max)'
	
cd "$project\Output"
putexcel set "TableCOutcomesinPrisonerCivilRightsCasesFY1988-`latestyear'.xlsx", sheet("Sheet1") modify

#delimit ;
putexcel A1="Table C: Outcomes in Prisoner Civil Rights Cases in Federal District Court, by Fiscal Year of Termination, FY 1988-`latestyear'"
	E2="Outcomes, as % of Judgment Dispositions"
	K2="Timing of Settlements, as % of Settlements, per Vol. Dismissals"
	B3="(a)"
	C3="(b)"
	D3="(c)"
	E3="(d)"
	F3="(e)"
	G3="(f)"
	H3="(g)"
	I3="(h)"
	J3="(i)"
	K3="(j)"
	L3="(k)"
	M3="(l)"
	A4="Fiscal Year"
	B4="Filings"
	C4="Terminations"
	D4="Judgments, as % of Terminations"
	E4="Pretrial Decisions for Deft."
	F4="Pretrial Decisions for Plaint."
	G4="Settled"
	H4="Vol. Dismissals"
	I4="Trials"
	J4="Plain. Trial Vict., as % of Trials"
	K4="Before Issue Joined"
	L4="After Issue Joined"
	M4="All Plaint. Successes, as % of Judgments";
#delimit cr
local row=5
forvalues i=1988/`latestyear' {
	sum fyfile if fyfile==`i' & origin127==1
	putexcel A`row'=`r(mean)'
	putexcel B`row'=`r(N)'
	sum fyterm if fyterm==`i'
	local terminations=`r(N)'
	putexcel C`row'=`terminations'
	count if outsum!=-8 & outsum!=0 & fyterm==`i'
	local judgments=`r(N)'
	local judgmt_pct=`judgments'/`terminations'
	putexcel D`row'=`judgmt_pct'
	count if outsum==1 & fyterm==`i'
	local pretrial_def=`r(N)'
	local pretrial_def_pct=`pretrial_def'/`judgments'
	putexcel E`row'=`pretrial_def_pct'
	count if outsum==6 & fyterm==`i'
	local pretrial_plt=`r(N)'
	local pretrial_plt_pct=`pretrial_plt'/`judgments'
	putexcel F`row'=`pretrial_plt_pct'
	count if outsum==3 & fyterm==`i'
	local settlements=`r(N)'
	putexcel G`row'=`settlements'/`judgments'
	count if outsum==2 & fyterm==`i'
	local vol_dismissals=`r(N)'
	putexcel H`row'=`vol_dismissals'/`judgments'
	count if outsum==4 & fyterm==`i'
	local trials=`r(N)'
	putexcel I`row'=`trials'/`judgments'
	count if outsum==4 & (outctot==9 | outctot==11 | outctot==15) & fyterm==`i'
	local plt_trial_vict=`r(N)'
	putexcel J`row'=`plt_trial_vict'/`trials'
	count if proc_sum==1 & (outsum==2 | outsum==3) & fyterm==`i'
	local sett_vol_dism=`settlements' + `vol_dismissals'
	putexcel K`row'=`r(N)'/`sett_vol_dism'
	count if proc_sum==2 & (outsum==2 | outsum==3) & fyterm==`i'
	putexcel L`row'=`r(N)'/`sett_vol_dism'
	count if (outsum==2 | outsum==3 | outsum==6 ) & fyterm==`i'
	local plt_nontrial_vict=`r(N)'
	local plt_vict_tot=`plt_trial_vict' + `plt_nontrial_vict'
	putexcel M`row'=`plt_vict_tot'/`judgments'
	local ++row
	}

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

/*	Table D: Outcomes in Federal District Court Cases by Case Type, Cases 
	Terminated in the Latest Fiscal Year */

clear
cd "C:\Users\gmarquez\Box\Empirical Research Service\Margo Schlanger\Empirical Research Service\Federal Judicial Center IDB"
use "FJC Civil Cases 1970-31Dec2020.dta", clear

drop if maisano==1
gen origin127=origin==1 | origin==2 | origin==7	

gen casetype=.
replace casetype=1 /* Habeas, Quasi Crim. */ if nossum==10
replace casetype=2 /* Prisoner Civil Rights */ if nossum==6
replace casetype=3 /* Bankruptcy */ if nossum==11
replace casetype=4 /* Immigration */ if nossum==12
replace casetype=5 /* Civil Rights */ if nossum==4
replace casetype=6 /* Statutory Actions */ if nossum==8
replace casetype=7 /* Other */ if nossum==13
replace casetype=8 /* Civil Rights, Empl. */ if nossum==5
replace casetype=9 /* Torts (Nonproduct) */ if nossum==2
replace casetype=10 /* U.S. Plaintiff */ if nossum==9
replace casetype=11 /* Contract */ if nossum==1
replace casetype=12 /* Product Liability */ if nossum==3
replace casetype=13 /* Labor and Empl. */ if nossum==7

gen casetypelabel=""
replace casetypelabel="Habeas, Quasi-Criminal" if casetype==1
replace casetypelabel="Prisoner Civ. Rights" if casetype==2
replace casetypelabel="Bankr." if casetype==3
replace casetypelabel="Immig." if casetype==4
replace casetypelabel="Civil Rights" if casetype==5
replace casetypelabel="Stat. Actions" if casetype==6
replace casetypelabel="Other" if casetype==7
replace casetypelabel="Civil Rights, Emp." if casetype==8
replace casetypelabel="Torts (Nonproduct)" if casetype==9
replace casetypelabel="U.S. Plaintiff" if casetype==10
replace casetypelabel="Contract" if casetype==11
replace casetypelabel="Product Liability" if casetype==12
replace casetypelabel="Labor and Emp." if casetype==13

/*	Note: table output is limited to complete fiscal years (i.e., most recent 
	fiscal year's numbers only included if data are available up to September 
	30 of that year) */
sum fyterm if month(termdate)==9 & (day(termdate)==28 | day(termdate)==29 ///
	| day(termdate)==30)
local latestyear=`r(max)'
	
cd "$project\Output"
putexcel set "TableDOutcomesinFedDistCourtCasesbyCaseType-FY`latestyear'.xlsx", sheet("Sheet1") modify

#delimit ;
putexcel A1="Table D: Outcomes in Federal District Court Cases by Case Type, Cases Terminated FY `latestyear'"
	E2="Outcomes, as % of Judgment Dispositions"
	K2="Timing of Settlements, as % of Settlements, per Vol. Dismissals"
	B3="(a)"
	C3="(b)"
	D3="(c)"
	E3="(d)"
	F3="(e)"
	G3="(f)"
	H3="(g)"
	I3="(h)"
	J3="(i)"
	K3="(j)"
	L3="(k)"
	M3="(l)"
	B4="Filings"
	C4="Terminations"
	D4="Judgments, as % of Terminations"
	E4="Pretrial Decisions for Deft."
	F4="Pretrial Decisions for Plaint."
	G4="Settled"
	H4="Vol. Dismissals"
	I4="Trials"
	J4="Plain. Trial Vict., as % of Trials"
	K4="Before Issue Joined"
	L4="After Issue Joined"
	M4="All Plaint. Successes, as % of Judgments";
#delimit cr
local row=6
forvalues i=1/13 {
	fre casetypelabel if casetype==`i'
	putexcel A`row'=`r(lab_valid)'
	sum fyfile if casetype==`i' & fyfile==`latestyear' & origin127==1
	putexcel B`row'=`r(N)'
	sum fyterm if casetype==`i' & fyterm==`latestyear'
	local terminations=`r(N)'
	putexcel C`row'=`terminations'
	count if outsum!=-8 & outsum!=0 & casetype==`i' & fyterm==`latestyear'
	local judgments=`r(N)'
	local judgmt_pct=`judgments'/`terminations'
	putexcel D`row'=`judgmt_pct'
	count if outsum==1 & casetype==`i' & fyterm==`latestyear'
	local pretrial_def=`r(N)'
	local pretrial_def_pct=`pretrial_def'/`judgments'
	putexcel E`row'=`pretrial_def_pct'
	count if outsum==6 & casetype==`i' & fyterm==`latestyear'
	local pretrial_plt=`r(N)'
	local pretrial_plt_pct=`pretrial_plt'/`judgments'
	putexcel F`row'=`pretrial_plt_pct'
	count if outsum==3 & casetype==`i' & fyterm==`latestyear'
	local settlements=`r(N)'
	putexcel G`row'=`settlements'/`judgments'
	count if outsum==2 & casetype==`i' & fyterm==`latestyear'
	local vol_dismissals=`r(N)'
	putexcel H`row'=`vol_dismissals'/`judgments'
	count if outsum==4 & casetype==`i' & fyterm==`latestyear'
	local trials=`r(N)'
	putexcel I`row'=`trials'/`judgments'
	count if outsum==4 & (outctot==9 | outctot==11 | outctot==15) & casetype==`i' & fyterm==`latestyear'
	local plt_trial_vict=`r(N)'
	putexcel J`row'=`plt_trial_vict'/`trials'
	count if proc_sum==1 & (outsum==2 | outsum==3) & casetype==`i' & fyterm==`latestyear'
	local sett_vol_dism=`settlements' + `vol_dismissals'
	putexcel K`row'=`r(N)'/`sett_vol_dism'
	count if proc_sum==2 & (outsum==2 | outsum==3) & casetype==`i' & fyterm==`latestyear'
	putexcel L`row'=`r(N)'/`sett_vol_dism'
	count if (outsum==2 | outsum==3 | outsum==6 ) & casetype==`i' & fyterm==`latestyear'
	local plt_nontrial_vict=`r(N)'
	local plt_vict_tot=`plt_trial_vict' + `plt_nontrial_vict'
	putexcel M`row'=`plt_vict_tot'/`judgments'
	local ++row
	}

/* All Civil Cases */
	local row=5
	putexcel A`row'="All"
	sum fyfile if fyfile==`latestyear' & origin127==1
	putexcel B`row'=`r(N)'
	sum fyterm if fyterm==`latestyear'
	local terminations=`r(N)'
	putexcel C`row'=`terminations'
	count if outsum!=-8 & outsum!=0 & fyterm==`latestyear'
	local judgments=`r(N)'
	local judgmt_pct=`judgments'/`terminations'
	putexcel D`row'=`judgmt_pct'
	count if outsum==1 & fyterm==`latestyear'
	local pretrial_def=`r(N)'
	local pretrial_def_pct=`pretrial_def'/`judgments'
	putexcel E`row'=`pretrial_def_pct'
	count if outsum==6 & fyterm==`latestyear'
	local pretrial_plt=`r(N)'
	local pretrial_plt_pct=`pretrial_plt'/`judgments'
	putexcel F`row'=`pretrial_plt_pct'
	count if outsum==3 & fyterm==`latestyear'
	local settlements=`r(N)'
	putexcel G`row'=`settlements'/`judgments'
	count if outsum==2 & fyterm==`latestyear'
	local vol_dismissals=`r(N)'
	putexcel H`row'=`vol_dismissals'/`judgments'
	count if outsum==4 & fyterm==`latestyear'
	local trials=`r(N)'
	putexcel I`row'=`trials'/`judgments'
	count if outsum==4 & (outctot==9 | outctot==11 | outctot==15) & fyterm==`latestyear'
	local plt_trial_vict=`r(N)'
	putexcel J`row'=`plt_trial_vict'/`trials'
	count if proc_sum==1 & (outsum==2 | outsum==3) & fyterm==`latestyear'
	local sett_vol_dism=`settlements' + `vol_dismissals'
	putexcel K`row'=`r(N)'/`sett_vol_dism'
	count if proc_sum==2 & (outsum==2 | outsum==3) & fyterm==`latestyear'
	putexcel L`row'=`r(N)'/`sett_vol_dism'
	count if (outsum==2 | outsum==3 | outsum==6 ) & fyterm==`latestyear'
	local plt_nontrial_vict=`r(N)'
	local plt_vict_tot=`plt_trial_vict' + `plt_nontrial_vict'
	putexcel M`row'=`plt_vict_tot'/`judgments'

********************************************************************************
	
/*	Table G: Change in Prisoner Filings in U.S. District Court and Filing Rates
	by State, Fiscal Years 1995 vs. 2012 vs. present */

cd "$project\Data"

clear
use "population_and_filings_1970-2020.dta", clear

sum year if !missing(incar)
local latestyear=`r(max)'

cd "$project\Output"

putexcel set "TableG-ChangeinPrisonerFilingsbyState1995vs`latestyear'.xlsx", sheet("Sheet1") modify

gen year95=year==1995 & state_num>0 & state_num<=51 & state_num!=9
gsort -year95 -FiRate
gen raterank95=_n if year95==1

bysort state: egen raterank_filler=mean(raterank95)
replace raterank95=raterank_filler if missing(raterank95)
drop raterank_filler

gen rate95=FiRate if year95==1 | (state_num==-1 & year==1995)
bysort state: egen rate_filler=mean(rate95)
replace rate95=rate_filler if missing(rate95)
drop rate_filler

gen year12=year==2012 & state_num>0 & state_num<=51 & state_num!=9
gsort -year12 -FiRate
gen raterank12=_n if year12==1

bysort state: egen raterank_filler=mean(raterank12)
replace raterank12=raterank_filler if missing(raterank12)
drop raterank_filler

gen rate12=FiRate if year12==1 | (state_num==-1 & year==2012)
bysort state: egen rate_filler=mean(rate12)
replace rate12=rate_filler if missing(rate12)
drop rate_filler

gen latestyear=year==`latestyear' & state_num>0 & state_num<=51 & state_num!=9
gsort -latestyear -FiRate
gen rateranklatest=_n if latestyear==1

bysort state: egen raterank_filler=mean(rateranklatest)
replace rateranklatest=raterank_filler if missing(rateranklatest)
drop raterank_filler

gen ratelatest=FiRate if latestyear==1 | (state_num==-1 & year==`latestyear')
bysort state: egen rate_filler=mean(ratelatest)
replace ratelatest=rate_filler if missing(ratelatest)
drop rate_filler

gen rateChangeN1=round(rate12-rate95,.1)
gen rateChangePct1=round((100*rateChangeN1)/rate95,.1)
gen rankChange1=raterank12-raterank95

gen rateChangeN2=round(ratelatest-rate12,.1)
gen rateChangePct2=round((100*rateChangeN2)/rate12,.1)
gen rankChange2=rateranklatest-raterank12

gen rateChangeN3=round(ratelatest-rate95,.1)
gen rateChangePct3=round((100*rateChangeN3)/rate95,.1)
gen rankChange3=rateranklatest-raterank95

#delimit ;
putexcel A1="Table G: Change in Prisoner Civil Rights Filings in U.S. District Court and Filing Rates, by State, Fiscal Years 1995 vs. 2012, 2012 vs. `latestyear', and 1995 vs. `latestyear'"
	B2="1995"
	F2="2012"
	J2=`latestyear'
	N2="1995-2012"
	Q2="2012-`latestyear'"
	T2="1995-`latestyear'"
	A3="State"
	B3="Incar. Pop."
	C3="Pris. Civ. Rights/ Cond. Filings"
	D3="Filings/ 1,000 Incar. Pop."
	E3="State Ranking by Filing Rate"
	F3="Incar. Pop."
	G3="Pris. Civ. Rights/ Cond. Filings"
	H3="Filings/ 1,000 Incar. Pop."
	I3="State Ranking by Filing Rate"
	J3="Incar. Pop."
	K3="Pris. Civ. Rights/ Cond. Filings"
	L3="Filings/ 1,000 Incar. Pop."
	M3="State Ranking by Filing Rate"
	N3="Rate Change"
	P3="Rank Change"
	Q3="Rate Change"
	S3="Rank Change"
	T3="Rate Change"
	V3="Rank Change"
	N4="N"
	O4="%"
	Q4="N"
	R4="%"
	T4="N"
	U4="%";
#delimit cr

/* U.S. counts and rates */
putexcel A5="All U.S."
sum incar if state_num==-1 & year==1995
putexcel B5=`r(mean)'
sum filings if state_num==-1 & year==1995
putexcel C5=`r(mean)'
sum FiRate if state_num==-1 & year==1995
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel D5=`roundrate'

sum incar if state_num==-1 & year==2012
putexcel F5=`r(mean)'
sum filings if state_num==-1 & year==2012
putexcel G5=`r(mean)'
sum FiRate if state_num==-1 & year==2012
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel H5=`roundrate'

sum incar if state_num==-1 & year==`latestyear'
putexcel J5=`r(mean)'
sum filings if state_num==-1 & year==`latestyear'
putexcel K5=`r(mean)'
sum FiRate if state_num==-1 & year==`latestyear'
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel L5=`roundrate'

sum rateChangeN1 if state_num==-1
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel N5=`roundrate'
sum rateChangePct1 if state_num==-1
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel O5="`roundrate'%"

sum rateChangeN2 if state_num==-1
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel Q5=`roundrate'
sum rateChangePct2 if state_num==-1
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel R5="`roundrate'%"

sum rateChangeN3 if state_num==-1
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel T5=`roundrate'
sum rateChangePct3 if state_num==-1
	local rate=`r(mean)'
	local roundrate=round(`rate',.1)
putexcel U5="`roundrate'%"

/* State counts and rates in descending order of filing rates in 1995 */
decode state, gen(statename)
gen statelabel = ""
replace statelabel = subinstr(statename, " (-1)", "", .) if state_num==-1
forvalues i=1/52 {
	replace statelabel = subinstr(statename, " (`i')", "", .) if state_num==`i'
	}
drop statename

local row=6
forvalues i=1/50 {
	fre statelabel if raterank95==`i'
	putexcel A`row'=`r(lab_valid)'
	sum incar if raterank95==`i' & year==1995
	putexcel B`row'=`r(mean)'
	sum filings if raterank95==`i' & year==1995
	putexcel C`row'=`r(mean)'
	sum FiRate if raterank95==`i' & year==1995
		local rate=`r(mean)'
		local roundrate=round(`rate',.1)
	putexcel D`row'=`roundrate'
	sum raterank95 if raterank95==`i'
	putexcel E`row'=`r(mean)'
	sum incar if raterank95==`i' & year==2012
	putexcel F`row'=`r(mean)'
	sum filings if raterank95==`i' & year==2012
	putexcel G`row'=`r(mean)'
	sum FiRate if raterank95==`i' & year==2012
		local rate=`r(mean)'
		local roundrate=round(`rate',.1)
	putexcel H`row'=`roundrate'
	sum raterank12 if raterank95==`i'
	putexcel I`row'=`r(mean)'
	sum incar if raterank95==`i' & year==`latestyear'
	putexcel J`row'=`r(mean)'
	sum filings if raterank95==`i' & year==`latestyear'
	putexcel K`row'=`r(mean)'
	sum FiRate if raterank95==`i' & year==`latestyear'
		local rate=`r(mean)'
		local roundrate=round(`rate',.1)
	putexcel L`row'=`roundrate'
	sum rateranklatest if raterank95==`i'
	putexcel M`row'=`r(mean)'
	sum rateChangeN1 if raterank95==`i'
		local rate=`r(mean)'
		local roundrate=round(`rate',.1)
	putexcel N`row'=`roundrate'
	sum rateChangePct1 if raterank95==`i'
		local rate=`r(mean)'
		local roundrate=round(`rate',.1 )
	putexcel O`row'=`roundrate'
	sum rankChange1 if raterank95==`i'
	putexcel P`row'=`r(mean)'
	sum rateChangeN2 if raterank95==`i'
		local rate=`r(mean)'
		local roundrate=round(`rate',.1)
	putexcel Q`row'=`roundrate'
	sum rateChangePct2 if raterank95==`i'
		local rate=`r(mean)'
		local roundrate=round(`rate',.1 )
	putexcel R`row'=`roundrate'
	sum rankChange2 if raterank95==`i'
	putexcel S`row'=`r(mean)'
	sum rateChangeN3 if raterank95==`i'
		local rate=`r(mean)'
		local roundrate=round(`rate',.1)
	putexcel T`row'=`roundrate'
	sum rateChangePct3 if raterank95==`i'
		local rate=`r(mean)'
		local roundrate=round(`rate',.1 )
	putexcel U`row'=`roundrate'
	sum rankChange3 if raterank95==`i'
	putexcel V`row'=`r(mean)'
	local ++ row
	}

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

/*	Table H: Days to Disposition: District Court Cases by Fiscal Year of Filing,
	Fiscal Years 1988-Present */

cd "C:\Users\gmarquez\Box\Empirical Research Service\Margo Schlanger\Empirical Research Service\Federal Judicial Center IDB"

clear
use "FJC Civil Cases 1970-31Dec2020.dta", clear

keep if (origin==1 | origin==2 | origin==7)
drop if maisano==1
gen t2disp=termdate-filedate
replace t2disp=999999 if outsum==-1 /*(outsum=-1 if case is pending)*/

/*	Note: table output is limited to complete fiscal years (i.e., most recent 
	fiscal year's numbers only included if data are available up to September 
	30 of that year) */
sum fyfile if month(termdate)==9 & (day(termdate)==28 | day(termdate)==29 ///
	| day(termdate)==30)
local latestyear=`r(max)'

cd "$project\Output"
putexcel set "TableH-DaystoDispositionbyFYofFiling1988-`latestyear'.xlsx", sheet(Sheet1) modify

#delimit ;
putexcel A1="Table H: Days to Disposition: District Court Cases by Fiscal Year of Filing 1988-`latestyear'"
	B2="Prisoner Civil Rights/Conditions Cases"
	E2=`""Other" Civil Rights Cases"'
	H2="All Cases Except Prisoner Civil Rights/ Conditions"
	A3="Fiscal Year"
	B3="25th Percentile of Docket"
	C3="50th Percentile of Docket"
	D3="75th Percentile of Docket"
	E3="25th Percentile of Docket"
	F3="50th Percentile of Docket"
	G3="75th Percentile of Docket"
	H3="25th Percentile of Docket"
	I3="50th Percentile of Docket"
	J3="75th Percentile of Docket";
#delimit cr
local row=4
forvalues i=1988/`latestyear' {
	putexcel A`row'=`i'
	sum t2disp if fyfile==`i' & (nos==550 | nos==555) & origin127==1, detail
	local p25th=`r(p25)'
	local p50th=`r(p50)'
	local p75th=`r(p75)'
	capture putexcel B`row'="`p25th' days"
		if `p25th'==999999 {
			putexcel B`row'="Pending"
			}
	capture putexcel C`row'="`p50th' days"
		if `p50th'==999999 {
			putexcel C`row'="Pending"
			}
	capture putexcel D`row'="`p75th' days"
		if `p75th'==999999 {
			putexcel D`row'="Pending"
			}
	sum t2disp if fyfile==`i' & nos==440 & origin127==1, detail
	local p25th=`r(p25)'
	local p50th=`r(p50)'
	local p75th=`r(p75)'
	capture putexcel E`row'="`p25th' days"
		if `p25th'==999999 {
			putexcel E`row'="Pending"
			}
	capture putexcel F`row'="`p50th' days"
		if `p50th'==999999 {
			putexcel F`row'="Pending"
			}
	capture putexcel G`row'="`p75th' days"
		if `p75th'==999999 {
			putexcel G`row'="Pending"
			}
	sum t2disp if fyfile==`i' & nos!=550 & nos!=555 & origin127==1, detail
	local p25th=`r(p25)'
	local p50th=`r(p50)'
	local p75th=`r(p75)'
	capture putexcel H`row'="`p25th' days"
		if `p25th'==999999 {
			putexcel H`row'="Pending"
			}
	capture putexcel I`row'="`p50th' days"
		if `p50th'==999999 {
			putexcel I`row'="Pending"
			}
	capture putexcel J`row'="`p75th' days"
		if `p75th'==999999 {
			putexcel J`row'="Pending"
			}
	local ++row
	}

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

/*	Figure A: Prison and Jail Populations and Civil Rights/Conditions Filing 
	Rate per 1,000 Incarcerated People, Fiscal Years 1970-Present */

/*	Note: to successfully run the following code, install 'grstyle' and 
	'plotplain' commands using the following code:

	ssc install grstyle, replace
	ssc install blindschemes
	
	These commands are necessary to exactly duplicate this graph's formatting; 
	if that is not desired, comment out lines starting with "set scheme" and 
	"grstyle" below.
*/
	
	
cd "$project\Data"

clear
use "population_and_filings_1970-2020.dta", clear

cd "$project\Output"
	
sum year if !missing(incar)
local latestyear=`r(max)'
keep if state==-1 & year<=`latestyear'
sort year

/* Combining state and federal prison populations */
gen long prison_fed = prison + fed

/* Adding small delta to year for graphing bars side by side */
gen year0 = year - .15
gen year1 = year + .15

set scheme plotplain
grstyle init
grstyle set legend 10, inside
grstyle set size 6pt: legend
grstyle set linewidth 1pt: plineplot

local bar_settings "yaxis(1) barw(.3) lwidth(thin)"
local line_settings "lwidth(medthick) lstyle(line) lcolor(gs0) yaxis(2)"

#delimit ;
twoway 
/* Prison Population Bar Chart */
(bar prison_fed year0 if year >= 1970, 
	lcolor("0 68 136") 
	fcolor("0 68 136") 
	`bar_settings')
/* Jail Population Bar Chart */
(bar jail year1 if year >= 1970, 
	lcolor("187 85 102") 
	fcolor("187 85 102") 
	`bar_settings')
/* Filing Rate Line Chart */
(connected FiRate year if year >= 1970, 
	mlwidth(none) 
	`line_settings'),
/* PLRA Vertical Line at Year 1996 */
	xline(1996, lp("-") noextend lc(gs0)) 
	text(1570000 1996 "PLRA", box bc(gs16) lw(medium)) 
/* X-Axis Settings */ 
	xsize(4) 
	ysize(2.5) 
	xtitle("") 
	xlab(1970(2)`latestyear', nogrid angle(90) labsize(small)) 
	xmtick(1970(1)`latestyear')
/* Left Y-Axis Settings */ 
	ytitle("Jail and Prison Population", axis(1))
	ylab(0(200000)1600000, labsize(small) axis(1) format(%12.0gc) angle(0))
	ysca(axis(1) r(0 1600000)) 
/* Right Y-Axis Settings */ 
	ytitle("Filings per 1,000 Incarcerated People", axis(2))
	ylab(0(5)36, labsize(small) axis(2) format(%12.0gc) angle(0))
	ysca(axis(2) r(0 36)) 
/* Legend Settings */ 		
	legend(
	symx(*.65) rows(3) region(color(none))
	label(1 "Prison Population")  label(2 "Jail Population")
	label(3 "Civil Rights/Conditions Filings" "per 1,000 Incarcerated People"))
/* Plot and Graph Region Settings */
	plotregion(color(gs16) margin(b=0)) graphregion(color(gs16) margin(0))
	name(figA_color, replace);
graph export "Figure_A_Incar_Pop_and_Civil_Rights_Filing_Rate_1970-`latestyear'.emf", 
replace fontface("Garamond");

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