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

	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, 2019
	
	Original do file created: April 25, 2021
	Do file author: German Marquez Alcala
	
	Updated: April 15, 2022
	Reason for update: Adding newly published data from 2019 Census of Jails
	and estimated ranges from Table 2 of the Nov. 2021 BJS report named "Census 
	of State and Federal Adult Correctional Facilities, 2019 - Statistical Tables"

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

global project "C:\Users\gmarquez\Dropbox (University of Michigan)\ERS\Margo Schlanger\Empirical Research Service\Population and Filings Replication"

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

cd "$project\Data"

clear
use "jail_prison_census_court_orders_1983-2019.dta", clear

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

cd "$project\Output\April 2022 Update"

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

putexcel A1 = "Table F: Incidence of Court Orders, Local Jails and State Prisons, 1983-2006, 2019"
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 A11 = "State Prisons"

local row = 5
forvalues i=1/11 {
	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
	}
	
/* Adding 2019 estimated ranges from summary tables published by BJS in the Nov. 
	2021 report named "Census of State and Federal Adult Correctional Facilities,
	2019 - Statistical Tables, located here: https://bjs.ojp.gov/library/publications/census-state-and-federal-adult-correctional-facilities-2019-statistical-tables */
	
	putexcel B16 = "2019*"
	putexcel C16 = 1050
	putexcel D16 = "227–250"
	putexcel E16 = "22–24%"
	putexcel F16 = 1156379
	putexcel G16 = "N/A"
	putexcel H16 = "22–23%"
	
********************************************************************************