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

	Updates to Margo Schlanger, Trends in Prisoner Litigation, as the 
	PLRA Enters Adulthood, 5 U.C. Irvine L. Rev. 153 (2015)
	
	Part 5: Creating Table F: Incidence of Court Orders, Local Jails and State
	Prisons, 1983-2006
	
	Updated: April 25, 2021

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

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

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

cd "$project\Data"

clear
use "jail_prison_census_court_orders_1983-2006.dta", clear

gen yearnum = year
recode yearnum (1983 = 1) (1988 = 2) (1993 = 3) (1999 = 4) (2006 = 5) ///
	(1984 = 6) (1990 = 7) (1995 = 8) (2000 = 9) (2005 = 10)

cd "$project\Output"

putexcel set "TableF-IncidenceofCourtOrdersinJailsandPrisons.xlsx", modify sheet(Sheet1)

putexcel A1 = "Table F: Incidence of Court Orders, Local Jails and State Prisons, 1983-2016"
putexcel C2 = "(a)"
putexcel D2 = "(b)"
putexcel F2 = "(c)"
putexcel G2 = "(d)"
putexcel B3 = "Year"
putexcel C3 = "Total Facilities"
putexcel D3 = "Facilities with Orders"
putexcel F3 = "Total Population (ADP)"
putexcel G3 = "Population Housed in Facilities with Orders (ADP)"
putexcel D4 = "N"
putexcel E4 = "%"
putexcel G4 = "N"
putexcel H4 = "%"
putexcel A5 = "Local Jails"
putexcel A10 = "State Prisons"

local row = 5
forvalues i=1/10 {
	sum year if yearnum == `i'
	putexcel B`row' = `r(mean)'
	sum n if fed == 0 & community == 0 & yearnum == `i'
	local totalfac = `r(sum)'
	putexcel C`row' = `totalfac'
	sum n if fed == 0 & community ==0 & ordsm == 1 & yearnum == `i'
	local ordfac = `r(sum)'
	putexcel D`row' = `ordfac'
	putexcel E`row' = `ordfac'/`totalfac'
	sum adp if fed == 0 & community == 0 & yearnum == `i'
	local totalpop = `r(sum)'
	putexcel F`row' = `totalpop'
	sum adp if fed == 0 & community ==0 & ordsm == 1 & yearnum == `i'
	local ordpop = `r(sum)'
	putexcel G`row' = `ordpop'
	putexcel H`row' = `ordpop'/`totalpop'
	local ++row
	}
	
********************************************************************************