/*******************************************************************************
	
	Updates to Margo Schlanger, Trends in Prisoner Litigation, as the 
	PLRA Enters Adulthood, 5 U.C. Irvine L. Rev. 153 (2015)
	
	Part 2: Updating Prison/Jail Population and Civil Rights Filings Dataset
	
	Updated: April 21, 2021

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

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

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

					Part 1: Preparing raw data files
	
*******************************************************************************/


/*******************************************************************************
********************************************************************************
	
	I. Local Jail Data from Vera Institute of Justice
	
********************************************************************************
*******************************************************************************/

/* 	1970-2018 Incarceration Trends Dataset from Vera Institute of Justice 
	Data last modified by Vera on September 25, 2020 
	Downloaded from: https://github.com/vera-institute/incarceration-trends
	Download date: October 6, 2020 
*/

clear
cd "$project\Data\Raw Data\Vera Institute\Incarceration Trends Dataset"
import excel "incarceration_trends_jail_jurisdiction.xlsx", firstrow

gen statefips=substr(jid,1,2)
order statefips, after(jid)
destring statefips, replace
sort statefips

/*	State FIPS codes source: 
	https://www.nrcs.usda.gov/wps/portal/nrcs/detail/?cid=nrcs143_013696 
*/

gen statenum=.
order statenum, after(statefips)
/* Alabama */ replace statenum=1 if statefips==1
/* Alaska */ replace statenum=2 if statefips==2
/* Arizona */ replace statenum=3 if statefips==4
/* Arkansas */ replace statenum=4 if statefips==5
/* California */ replace statenum=5 if statefips==6
/* Colorado */ replace statenum=6 if statefips==8
/* Connecticut */ replace statenum=7 if statefips==9
/* Delaware */ replace statenum=8 if statefips==10
/* District of Columbia */ replace statenum=9 if statefips==11
/* Florida */ replace statenum=10 if statefips==12
/* Georgia */ replace statenum=11 if statefips==13
/* Hawaii */ replace statenum=12 if statefips==15
/* Idaho */ replace statenum=13 if statefips==16
/* Illinois */ replace statenum=14 if statefips==17
/* Indiana */ replace statenum=15 if statefips==18
/* Iowa */ replace statenum=16 if statefips==19
/* Kansas */ replace statenum=17 if statefips==20
/* Kentucky */ replace statenum=18 if statefips==21
/* Louisiana */ replace statenum=19 if statefips==22
/* Maine */ replace statenum=20 if statefips==23
/* Maryland */ replace statenum=21 if statefips==24
/* Massachusetts */ replace statenum=22 if statefips==25
/* Michigan */ replace statenum=23 if statefips==26
/* Minnesota */ replace statenum=24 if statefips==27
/* Mississippi */ replace statenum=25 if statefips==28
/* Missouri */ replace statenum=26 if statefips==29
/* Montana */ replace statenum=27 if statefips==30
/* Nebraska */ replace statenum=28 if statefips==31
/* Nevada */ replace statenum=29 if statefips==32
/* New Hampshire */ replace statenum=30 if statefips==33
/* New Jersey */ replace statenum=31 if statefips==34
/* New Mexico */ replace statenum=32 if statefips==35
/* New York */ replace statenum=33 if statefips==36
/* North Carolina */ replace statenum=34 if statefips==37
/* North Dakota */ replace statenum=35 if statefips==38
/* Ohio */ replace statenum=36 if statefips==39
/* Oklahoma */ replace statenum=37 if statefips==40
/* Oregon */ replace statenum=38 if statefips==41
/* Pennsylvania */ replace statenum=39 if statefips==42
/* Rhode Island */ replace statenum=40 if statefips==44
/* South Carolina */ replace statenum=41 if statefips==45
/* South Dakota */ replace statenum=42 if statefips==46
/* Tennessee */ replace statenum=43 if statefips==47
/* Texas */ replace statenum=44 if statefips==48
/* Utah */ replace statenum=45 if statefips==49
/* Vermont */ replace statenum=46 if statefips==50
/* Virginia */ replace statenum=47 if statefips==51
/* Washington */ replace statenum=48 if statefips==53
/* West Virginia */ replace statenum=49 if statefips==54
/* Wisconsin */ replace statenum=50 if statefips==55
/* Wyoming */ replace statenum=51 if statefips==56

cd "$project\Data"
putexcel set "Jail Pop by State - Vera Incarceration Trends.xlsx", modify

/* Note: total_jail_pop = Average Daily Population */
/* Note: following code repeats because column output to the Excel spreadsheet 
	is limited by the letters in the alphabet string. Since the 2018 counts are 
	placed on Column AX in the spreadsheet, the code can be used as-is for two
	more years' worth of data; once 2021 data are available, the code must be 
	copied once more, making sure to replace the 
	"forvalues y=1995/`latestyear' {" line with 
	"forvalues y=1995/2020 {" in the original and 
	"forvalues y=2021/`latestyear' {" in the second copy, as well as replace the 
	"putexcel A`col'`row'=`roundtotal'" line with 
	"putexcel B`col'`row'=`roundtotal'" to work with Excel's column naming 
	scheme. 
*/

sum year
local latestyear=`r(max)'

/*	U.S. Counts: */
local row=2
local ncol=2
forvalues y=1970/1994 {
	local col: word `ncol' of `c(ALPHA)'
	sum total_jail_pop if year==`y'
	local sum=`r(sum)'
	local roundtotal=round(`sum',1)
	putexcel `col'`row'=`roundtotal'
	local ++ncol
	}

local row=2
local ncol=1
forvalues y=1995/`latestyear' {
	local col: word `ncol' of `c(ALPHA)'
	sum total_jail_pop if year==`y'
	local sum=`r(sum)'
	local roundtotal=round(`sum',1)
	putexcel A`col'`row'=`roundtotal'
	local ++ncol
	}

/* State-by-State Counts: */	
local row=3
forvalues i=1/51 {
	local ncol=2
	forvalues y=1970/1994 {
		local col: word `ncol' of `c(ALPHA)'
		sum total_jail_pop if statenum==`i' & year==`y'
		local sum=`r(sum)'
		local roundtotal=round(`sum',1)
		putexcel `col'`row'=`roundtotal'
		local ++ncol
		}
	local ++row
	}

local row=3
forvalues i=1/51 {
	local ncol=1
	forvalues y=1995/`latestyear' {
		local col: word `ncol' of `c(ALPHA)'
		sum total_jail_pop if statenum==`i' & year==`y'
		local sum=`r(sum)'
		local roundtotal=round(`sum',1)
		putexcel A`col'`row'=`roundtotal'
		local ++ncol
		}
	local ++row
	}

/*******************************************************************************
********************************************************************************
	
	II. State Prison Data from Bureau of Justice Statistics
	
********************************************************************************
*******************************************************************************/

/*	1970-1977 state prison counts are re-used from the previous dataset, as 
	those counts originate from published DOJ reports and are not retroactively
	updated. See Data Notes document for more details.

	1978-1998 state prison year-end custody population by state from the Quick
	Tables in the Corrections Statistical Analysis Tool (CSAT) from the Bureau
	of Justice Statistics
	Specific Quick Table Name: Inmates in Custody of State or Federal 
	Correctional Facilities, Excluding Private Prison Facilities, December 31,
	1978-2019
	Downloaded from: https://www.bjs.gov/nps/resources/documents/QT_custody%20population%20without%20private%20prisons_total.xlsx
	Download date: April 20, 2021
*/

cd "$project\Data\Raw Data\BJS CSAT"

clear
import excel "QT_custody population without private prisons_total.xlsx", allstring clear

format A %50s
drop if missing(C)
replace B=A if missing(B)
drop A

/* Dropping all empty variables */
foreach var of varlist _all {
	capture assert mi(`var')
	if !_rc {
		drop `var'
		}
	}
/* Changing variable names in first row */
. ds B, not
foreach var of varlist `r(varlist)' {
	fre `var' if B=="Jurisdiction"
	replace `var'=`"prison`r(lab_valid)'"' if B=="Jurisdiction"
	replace `var'=subinstr(`var',`"""',"",.) if B=="Jurisdiction"
	replace `var'=subinstr(`var',"`","",.) if B=="Jurisdiction"
	replace `var'=subinstr(`var',"'","",.) if B=="Jurisdiction"
	replace `var'=subinstr(`var',"/b","",.) if B=="Jurisdiction"
	}

export excel using "State Prison Counts by State w-o Private.xlsx", replace

clear
import excel "State Prison Counts by State w-o Private.xlsx", firstrow clear

drop if Jurisdiction=="U.S. total" | Jurisdiction=="Federal"
replace Jurisdiction="United States" if Jurisdiction=="State"
rename Jurisdiction statename
gen US=statename=="United States"
sort US statename
gen state_num=_n
replace state_num=-1 if US==1
drop US
order state_num, first

. ds state_num statename, not
foreach var of varlist `r(varlist)' {
	destring `var', replace ignore ("-")
	}

/* Dropping all counts after 1998 */
forvalues i=1999/2019 {
	drop prison`i'
	}

tempfile statepris1978_98
save `statepris1978_98'

/*	1999-2019 state prison year-end custody population by state from the 
	Quick Tables in the Corrections Statistical Analysis Tool (CSAT) from the 
	Bureau of Justice Statistics
	Specific Quick Table Name: Inmates in Custody of State or Federal 
	Correctional Facilities, Including Private Prison Facilities, December 31,
	1999-2019
	Downloaded from: https://www.bjs.gov/nps/resources/documents/QT_custody%20population%20including%20private%20prisons_total.xlsx
	Download date: April 20, 2021
*/

clear
import excel "QT_custody population including private prisons_total.xlsx", allstring clear

format A %50s
drop if missing(C)
replace B=A if missing(B)
drop A

/* Dropping all empty variables */
foreach var of varlist _all {
	capture assert mi(`var')
	if !_rc {
		drop `var'
		}
	}
/* Changing variable names in first row */
. ds B, not
foreach var of varlist `r(varlist)' {
	fre `var' if B=="Jurisdiction"
	replace `var'=`"prison`r(lab_valid)'"' if B=="Jurisdiction"
	replace `var'=subinstr(`var',`"""',"",.) if B=="Jurisdiction"
	replace `var'=subinstr(`var',"`","",.) if B=="Jurisdiction"
	replace `var'=subinstr(`var',"'","",.) if B=="Jurisdiction"
	}

export excel using "State Prison Counts by State w Private.xlsx", replace

clear
import excel "State Prison Counts by State w Private.xlsx", firstrow clear

drop if Jurisdiction=="U.S. total" | Jurisdiction=="Federal"
replace Jurisdiction="United States" if Jurisdiction=="State"
rename Jurisdiction statename
gen US=statename=="United States"
sort US statename
gen state_num=_n
replace state_num=-1 if US==1
drop US
order state_num, first

. ds state_num statename, not
foreach var of varlist `r(varlist)' {
	destring `var', replace ignore ("-")
	}
	
rename statename statename2

tempfile statepris1999_2019
save `statepris1999_2019'

clear
use `statepris1978_98'
merge 1:1 state_num using `statepris1999_2019'

replace statename=statename2
drop statename2 _merge

cd "$project\Data\Raw Data"

export excel using "State Prison Counts by State.xlsx", firstrow(variables) replace

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

	III. Federal Prison Data from Bureau of Prisons
	
********************************************************************************
*******************************************************************************/

/*	1994-2013 data received via email from Jennifer Batchelder, Supervisory 
	Research Analyst at BOP, on June 13, 2014
	
	2014-2020 data scraped from the BOP website, 
	https://www.bop.gov/about/statistics/population_statistics.jsp, using the
	Internet Archive Wayback Machine, https://archive.org/web
*/

cd "$project\Data\Raw Data\Federal Bureau of Prisons"

/*	Importing 1994-2013 Data */

clear	
import excel "YearlyPopulationNumbers_199409-201309.xlsx", sheet("USE THIS") firstrow clear

rename FacilityCode facilitycode
rename FacilityDescription facilityname
rename NumberofInmates fedpop
rename Year year
rename State state
rename Priv private

drop if missing(facilitycode)

gsort facilitycode -state
replace state=state[_n-1] if facilitycode == facilitycode[_n-1]

replace state = "PA" if facilitycode == "ALW" & facilityname == "Allenwood FPC"
replace state = "CA" if facilitycode == "BRN" & facilityname == "Boron FPC"
replace state = "CA" if facilitycode == "CAL" & facilityname == "California City Ci"
replace state = "FL" if facilitycode == "EGL" & facilityname == "Eglin FPC"
replace state = "AZ" if facilitycode == "ELO" & facilityname == "Eloy Dc"
replace state = "TX" if facilitycode == "ELP" & facilityname == "El Paso FPC"
replace state = "TX" if facilitycode == "LIM" & facilityname == "Limestone Dc"
replace state = "TN" if facilitycode == "MLL" & facilityname == "Millington FPC"
replace state = "NV" if facilitycode == "NEL" & facilityname == "Nellis FPC"
replace state = "LA" if facilitycode == "PPR" & facilityname == "Pine Prairie CI"
replace state = "NC" if facilitycode == "SEY" & facilityname == "Seymour Johnsn FPC"
replace state = "SC" if facilitycode == "EDG" & facilityname == "Edgefield FCI"

replace private = 0
replace private = 1 if regexm(facilityname," CI *$") == 1
replace private = 1 if regexm(facilityname," Ci *$") == 1

gen facname=upper(facilityname)
replace private=1 if facname=="ELOY DC" | facname=="LIMESTONE DC" ///
	| facname=="REEVES DC"
drop facname

tempfile fedprisonpop1994_2013
save `fedprisonpop1994_2013'

/*	Importing 2014-2020 Data */

clear
import excel "Federal Prisoners by Facility - BOP Site 2014-2020.xlsx", sheet("12Jun2014") firstrow clear

rename FacilityDescription facilityname
rename ST_ABB state
rename fedpop2014 fedpop
rename Priv private
rename Comm community
set obs `=_N+1'
replace facilityname="Total" if missing(facilityname)
sum fedpop if community==0
replace fedpop=`r(sum)' if facilityname=="Total"
drop if community==1
drop community
gen year=2014

tempfile fedprisonpop2014
save `fedprisonpop2014'

clear
import excel "Federal Prisoners by Facility - BOP Site 2014-2020.xlsx", sheet("28Jan2016") firstrow clear

rename FacilityDescription facilityname
rename ST_ABB state
rename fedpop2015 fedpop
rename Priv private
rename Comm community
set obs `=_N+1'
replace facilityname="Total" if missing(facilityname)
sum fedpop if community==0
replace fedpop=`r(sum)' if facilityname=="Total"
drop if community==1
drop community
gen year=2015

tempfile fedprisonpop2015
save `fedprisonpop2015'

clear
import excel "Federal Prisoners by Facility - BOP Site 2014-2020.xlsx", sheet("15Dec2016") firstrow clear

rename FacilityDescription facilityname
rename ST_ABB state
rename fedpop2016 fedpop
rename Priv private
rename Comm community
set obs `=_N+1'
replace facilityname="Total" if missing(facilityname)
sum fedpop if community==0
replace fedpop=`r(sum)' if facilityname=="Total"
drop if community==1
drop community
gen year=2016

tempfile fedprisonpop2016
save `fedprisonpop2016'

clear
import excel "Federal Prisoners by Facility - BOP Site 2014-2020.xlsx", sheet("28Sep2017") firstrow clear

rename FacilityDescription facilityname
rename ST_ABB state
rename fedpop2017 fedpop
rename Priv private
rename Comm community
set obs `=_N+1'
replace facilityname="Total" if missing(facilityname)
sum fedpop if community==0
replace fedpop=`r(sum)' if facilityname=="Total"
drop if community==1
drop community
gen year=2017

tempfile fedprisonpop2017
save `fedprisonpop2017'

clear
import excel "Federal Prisoners by Facility - BOP Site 2014-2020.xlsx", sheet("11Oct2018") firstrow clear

rename FacilityDescription facilityname
rename ST_ABB state
rename fedpop2018 fedpop
rename Priv private
rename Comm community
set obs `=_N+1'
replace facilityname="Total" if missing(facilityname)
sum fedpop if community==0
replace fedpop=`r(sum)' if facilityname=="Total"
drop if community==1
drop community
gen year=2018

tempfile fedprisonpop2018
save `fedprisonpop2018'

clear
import excel "Federal Prisoners by Facility - BOP Site 2014-2020.xlsx", sheet("17Oct2019") firstrow clear

rename FacilityDescription facilityname
rename ST_ABB state
rename fedpop2019 fedpop
rename Priv private
rename Comm community
set obs `=_N+1'
replace facilityname="Total" if missing(facilityname)
sum fedpop if community==0
replace fedpop=`r(sum)' if facilityname=="Total"
drop if community==1
drop community
gen year=2019

tempfile fedprisonpop2019
save `fedprisonpop2019'

clear
import excel "Federal Prisoners by Facility - BOP Site 2014-2020.xlsx", sheet("08Oct2020") firstrow clear

rename FacilityDescription facilityname
rename ST_ABB state
rename fedpop2020 fedpop
rename Priv private
rename Comm community
set obs `=_N+1'
replace facilityname="Total" if missing(facilityname)
sum fedpop if community==0
replace fedpop=`r(sum)' if facilityname=="Total"
drop if community==1
drop community
gen year=2020

tempfile fedprisonpop2020
save `fedprisonpop2020'

/*	Appending Data and reformatting */

clear
append using `fedprisonpop1994_2013' `fedprisonpop2014' `fedprisonpop2015' ///
	`fedprisonpop2016' `fedprisonpop2017' `fedprisonpop2018' ///
	`fedprisonpop2019' `fedprisonpop2020'

replace facilityname="Total" if facilitycode=="Total" & missing(facilityname)
replace private=0 if facilityname=="Total" & missing(private)
replace state="USA" if facilityname=="Total"

gen state_num=.
replace state_num=-1 if state=="USA"
replace state_num=1  if state=="AL"
replace state_num=2  if state=="AK"
replace state_num=3  if state=="AZ"
replace state_num=4  if state=="AR"
replace state_num=5  if state=="CA"
replace state_num=6  if state=="CO"
replace state_num=7  if state=="CT"
replace state_num=8  if state=="DE"
replace state_num=9  if state=="DC"
replace state_num=10 if state=="FL"
replace state_num=11 if state=="GA"
replace state_num=12 if state=="HI"
replace state_num=13 if state=="ID"
replace state_num=14 if state=="IL"
replace state_num=15 if state=="IN"
replace state_num=16 if state=="IA"
replace state_num=17 if state=="KS"
replace state_num=18 if state=="KY"
replace state_num=19 if state=="LA"
replace state_num=20 if state=="ME"
replace state_num=21 if state=="MD"
replace state_num=22 if state=="MA"
replace state_num=23 if state=="MI"
replace state_num=24 if state=="MN"
replace state_num=25 if state=="MS"
replace state_num=26 if state=="MO"
replace state_num=27 if state=="MT"
replace state_num=28 if state=="NE"
replace state_num=29 if state=="NV"
replace state_num=30 if state=="NH"
replace state_num=31 if state=="NJ"
replace state_num=32 if state=="NM"
replace state_num=33 if state=="NY"
replace state_num=34 if state=="NC"
replace state_num=35 if state=="ND"
replace state_num=36 if state=="OH"
replace state_num=37 if state=="OK"
replace state_num=38 if state=="OR"
replace state_num=39 if state=="PA"
replace state_num=40 if state=="RI"
replace state_num=41 if state=="SC"
replace state_num=42 if state=="SD"
replace state_num=43 if state=="TN"
replace state_num=44 if state=="TX"
replace state_num=45 if state=="UT"
replace state_num=46 if state=="VT"
replace state_num=47 if state=="VA"
replace state_num=48 if state=="WA"
replace state_num=49 if state=="WV"
replace state_num=50 if state=="WI"
replace state_num=51 if state=="WY"
replace state_num=52 if (state=="PR" | state=="RQ")

rename state state_abb
replace state_abb="PR" if state_num==52

save "BOP Federal Prison Population by Facility 1994-Present.dta", replace

/* Creating Federal Prison Populations by State */

putexcel set "Federal Prisoners by State 1994-Present.xlsx", sheet("Sheet1") modify

/* Note: following code repeats because column output to the Excel spreadsheet 
	is limited by the letters in the alphabet string. */

/*	U.S. Counts: */
putexcel A2=-1
putexcel B2="USA"
local row=2
local ncol=3
forvalues y=1994/2017 {
	local col: word `ncol' of `c(ALPHA)'
	sum fedpop if year==`y' & state_num==-1
	putexcel `col'`row'=`r(sum)'
	local ++ncol
	}
sum year
local latestyear=`r(max)'
local row=2
local ncol=1
forvalues y=2018/`latestyear' {
	local col: word `ncol' of `c(ALPHA)'
	sum fedpop if year==`y' & state_num==-1
	putexcel A`col'`row'=`r(sum)'
	local ++ncol
	}
	
/* State-by-State Counts: */	
local row=3
forvalues i=1/52 {
	local ncol=3
	sum state_num if state_num==`i'
	capture putexcel A`row'=`r(mean)' 
	fre state_abb if state_num==`i'
	capture putexcel B`row'=`r(lab_valid)'
	forvalues y=1994/2017 {
		local col: word `ncol' of `c(ALPHA)'
		sum fedpop if state_num==`i' & year==`y'
		putexcel `col'`row'=`r(sum)'
		local ++ncol
		}
	local ++row
	}
	
local row=3
forvalues i=1/52 {
	local ncol=1
	forvalues y=2018/`latestyear' {
		local col: word `ncol' of `c(ALPHA)'
		sum fedpop if state_num==`i' & year==`y'
		putexcel A`col'`row'=`r(sum)'
		local ++ncol
		}
	local ++row
	}
	
/*	Adding annual population totals from all BOP facilities, including public
	facilities, private facilities on contract with BOP, and community housing,
	including home confinement */

clear
import excel "BOP_pastPopulationTotals.xlsx", firstrow clear

replace A="year" if A=="FY"
replace B="fed_BOP" if B=="Total Population"
drop C
gen D="-1"
replace D="state_num" if A=="year"

export excel using "US Total BOP Inmate Population 1980-2020.xlsx", replace

/*******************************************************************************
********************************************************************************
	
	IV. Prisoner Civil Rights Lawsuit Filings in Federal Court from Federal 
	Judicial Center Integrated Database (IDB)
	
********************************************************************************
*******************************************************************************/

/*	1970-2020 Civil Integrated Database from Federal Judicial Center
	
	1970-1987 Data Downloaded from: 
	https://www.fjc.gov/research/idb/civil-cases-filed-and-terminated-sy-1970-through-sy-1987
	Data last modified by FJC on January 18, 2017
	Download date: July 27, 2020
	
	1988-2021 Data Downloaded from:
	https://www.fjc.gov/research/idb/civil-cases-filed-terminated-and-pending-sy-1988-present
	Data last modified by FJC on February 11, 2021
	Download date: March 2, 2021
	
	The two parts of the IDB are merged and cleaned in a separate do file, 
	named "00 Appending and Reformatting FJC Dataset.do," which is included 
	in this project's folder, but calls data files from other folders.
*/

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

keep nos origin filedate plt def maisano termdate tdateuse tapeyear fyfile ///
fyterm juris circuit district office residenc state state_label_string

keep if nos==550 | nos==555

keep if origin==1 | origin==2 | origin==7	

drop if maisano==1

gen feddef=juris==2
label var feddef "Federal Defendant (Juris=2)"

sort state fyfile

/* All Filings (Both Federal and Non-Federal Defendants) */
cd "$project\Data"
putexcel set "FJC Civil Rights Filings by State.xlsx", sheet("All Filings") modify

/* Note: following code repeats because column output to the Excel spreadsheet 
	is limited by the letters in the alphabet string. Since the 2018 counts are 
	placed on Column AX in the spreadsheet, the code can be used as-is for two
	more years' worth of data; once 2021 data are available, the code must be 
	copied once more, making sure to replace the "putexcel A`col'`row'=`r(N)'"
	line with "putexcel B`col'`row'=`r(N)'" to work with Excel's column
	naming scheme. */

/* U.S. Counts: */
/* Note: U.S. Counts include filings in all 50 States, D.C., and territories */

local row=2
local ncol=2
forvalues y=1970/1994 {
	local col: word `ncol' of `c(ALPHA)'
	count if fyfile==`y'
	putexcel `col'`row'=`r(N)'
	local ++ncol
	}
	
local row=2
local ncol=1
forvalues y=1995/2020 {
	local col: word `ncol' of `c(ALPHA)'
	count if fyfile==`y'
	putexcel A`col'`row'=`r(N)'
	local ++ncol
	}
	
/* State-by-State Counts: */	
local row=3
forvalues i=1/52 {
	local ncol=2
	forvalues y=1970/1994 {
		local col: word `ncol' of `c(ALPHA)'
		count if state==`i' & fyfile==`y'
		putexcel `col'`row'=`r(N)'
		local ++ncol
		}
	local ++row
	}
	
local row=3
forvalues i=1/52 {
	local ncol=1
	forvalues y=1995/2020 {
		local col: word `ncol' of `c(ALPHA)'
		count if state==`i' & fyfile==`y'
		putexcel A`col'`row'=`r(N)'
		local ++ncol
		}
	local ++row
	}

/* Filings with Non-Federal Defendants */

putexcel set "FJC Civil Rights Filings by State.xlsx", sheet("Non-Fed Def Filings") modify

/* Note: following code repeats because column output to the Excel spreadsheet 
	is limited by the letters in the alphabet string. Since the 2018 counts are 
	placed on Column AX in the spreadsheet, the code can be used as-is for two
	more years' worth of data; once 2021 data are available, the code must be 
	copied once more, making sure to replace the "putexcel A`col'`row'=`r(N)'"
	line with "putexcel B`col'`row'=`r(N)'" to work with Excel's column
	naming scheme. */

/* U.S. Counts: */
/* Note: U.S. Counts include filings in all 50 States, D.C., and territories */

local row=2
local ncol=2
forvalues y=1970/1994 {
	local col: word `ncol' of `c(ALPHA)'
	count if fyfile==`y' & feddef==0
	putexcel `col'`row'=`r(N)'
	local ++ncol
	}
	
local row=2
local ncol=1
forvalues y=1995/2020 {
	local col: word `ncol' of `c(ALPHA)'
	count if fyfile==`y' & feddef==0
	putexcel A`col'`row'=`r(N)'
	local ++ncol
	}
	
/* State-by-State Counts: */	
local row=3
forvalues i=1/52 {
	local ncol=2
	forvalues y=1970/1994 {
		local col: word `ncol' of `c(ALPHA)'
		count if state==`i' & fyfile==`y' & feddef==0
		putexcel `col'`row'=`r(N)'
		local ++ncol
		}
	local ++row
	}
	
local row=3
forvalues i=1/52 {
	local ncol=1
	forvalues y=1995/2020 {
		local col: word `ncol' of `c(ALPHA)'
		count if state==`i' & fyfile==`y' & feddef==0
		putexcel A`col'`row'=`r(N)'
		local ++ncol
		}
	local ++row
	}

/* Filings with Federal Defendants */

putexcel set "FJC Civil Rights Filings by State.xlsx", sheet("Fed Def Filings") modify

/* Note: following code repeats because column output to the Excel spreadsheet 
	is limited by the letters in the alphabet string. Since the 2018 counts are 
	placed on Column AX in the spreadsheet, the code can be used as-is for two
	more years' worth of data; once 2021 data are available, the code must be 
	copied once more, making sure to replace the "putexcel A`col'`row'=`r(N)'"
	line with "putexcel B`col'`row'=`r(N)'" to work with Excel's column
	naming scheme. */

/* U.S. Counts: */
/* Note: U.S. Counts include filings in all 50 States, D.C., and territories */

local row=2
local ncol=2
forvalues y=1970/1994 {
	local col: word `ncol' of `c(ALPHA)'
	count if fyfile==`y' & feddef==1
	putexcel `col'`row'=`r(N)'
	local ++ncol
	}
	
local row=2
local ncol=1
forvalues y=1995/2020 {
	local col: word `ncol' of `c(ALPHA)'
	count if fyfile==`y' & feddef==1
	putexcel A`col'`row'=`r(N)'
	local ++ncol
	}
	
/* State-by-State Counts: */	
local row=3
forvalues i=1/52 {
	local ncol=2
	forvalues y=1970/1994 {
		local col: word `ncol' of `c(ALPHA)'
		count if state==`i' & fyfile==`y' & feddef==1
		putexcel `col'`row'=`r(N)'
		local ++ncol
		}
	local ++row
	}
	
local row=3
forvalues i=1/52 {
	local ncol=1
	forvalues y=1995/2020 {
		local col: word `ncol' of `c(ALPHA)'
		count if state==`i' & fyfile==`y' & feddef==1
		putexcel A`col'`row'=`r(N)'
		local ++ncol
		}
	local ++row
	}

/*******************************************************************************
********************************************************************************
	
	V. U.S. and State Population Counts and Estimates from U.S. Census Bureau
	
********************************************************************************
*******************************************************************************/

cd "$project\Data\Raw Data\Census Bureau"

/*	1970-1979 U.S. and State Intercensal Population Estimates from Census Bureau
	
	Data source: https://www.census.gov/data/tables/time-series/demo/popest/1970s-state.html
	Table named "1970-1979 Intercensal State Estimates by Age, Sex, Race (PE-19)"
	Download Date: March 31, 2021
*/

clear
import excel "pe-19.xls", firstrow allstring clear

rename Tablewithrowheadersincolumn A
format A %30s
drop if missing(A)
drop if regexm(A, "Estimates of the")
drop if regexm(A, "Source:")
drop if regexm(A, "Internet")
replace A="year" if A=="Year of Estimate"
replace B="statefips" if B=="FIPS State Code"
replace C="statename" if A=="year"
replace E="pop05" if A=="year"
replace F="pop59" if A=="year"
replace G="pop1014" if A=="year"
replace H="pop1519" if A=="year"
replace I="pop2024" if A=="year"
replace J="pop2529" if A=="year"
replace K="pop3034" if A=="year"
replace L="pop3539" if A=="year"
replace M="pop4044" if A=="year"
replace N="pop4549" if A=="year"
replace O="pop5054" if A=="year"
replace P="pop5559" if A=="year"
replace Q="pop6064" if A=="year"
replace R="pop6569" if A=="year"
replace S="pop7074" if A=="year"
replace T="pop7579" if A=="year"
replace U="pop8084" if A=="year"
replace V="pop85" if A=="year"

export excel using "Census Pop Estimates by State 1970-1979.xlsx", replace
clear
import excel "Census Pop Estimates by State 1970-1979.xlsx", firstrow clear

destring year statefips pop*, replace

gen state_num=.
order state_num, after(statefips)
/* Alabama */ replace state_num=1 if statefips==1
/* Alaska */ replace state_num=2 if statefips==2
/* Arizona */ replace state_num=3 if statefips==4
/* Arkansas */ replace state_num=4 if statefips==5
/* California */ replace state_num=5 if statefips==6
/* Colorado */ replace state_num=6 if statefips==8
/* Connecticut */ replace state_num=7 if statefips==9
/* Delaware */ replace state_num=8 if statefips==10
/* District of Columbia */ replace state_num=9 if statefips==11
/* Florida */ replace state_num=10 if statefips==12
/* Georgia */ replace state_num=11 if statefips==13
/* Hawaii */ replace state_num=12 if statefips==15
/* Idaho */ replace state_num=13 if statefips==16
/* Illinois */ replace state_num=14 if statefips==17
/* Indiana */ replace state_num=15 if statefips==18
/* Iowa */ replace state_num=16 if statefips==19
/* Kansas */ replace state_num=17 if statefips==20
/* Kentucky */ replace state_num=18 if statefips==21
/* Louisiana */ replace state_num=19 if statefips==22
/* Maine */ replace state_num=20 if statefips==23
/* Maryland */ replace state_num=21 if statefips==24
/* Massachusetts */ replace state_num=22 if statefips==25
/* Michigan */ replace state_num=23 if statefips==26
/* Minnesota */ replace state_num=24 if statefips==27
/* Mississippi */ replace state_num=25 if statefips==28
/* Missouri */ replace state_num=26 if statefips==29
/* Montana */ replace state_num=27 if statefips==30
/* Nebraska */ replace state_num=28 if statefips==31
/* Nevada */ replace state_num=29 if statefips==32
/* New Hampshire */ replace state_num=30 if statefips==33
/* New Jersey */ replace state_num=31 if statefips==34
/* New Mexico */ replace state_num=32 if statefips==35
/* New York */ replace state_num=33 if statefips==36
/* North Carolina */ replace state_num=34 if statefips==37
/* North Dakota */ replace state_num=35 if statefips==38
/* Ohio */ replace state_num=36 if statefips==39
/* Oklahoma */ replace state_num=37 if statefips==40
/* Oregon */ replace state_num=38 if statefips==41
/* Pennsylvania */ replace state_num=39 if statefips==42
/* Rhode Island */ replace state_num=40 if statefips==44
/* South Carolina */ replace state_num=41 if statefips==45
/* South Dakota */ replace state_num=42 if statefips==46
/* Tennessee */ replace state_num=43 if statefips==47
/* Texas */ replace state_num=44 if statefips==48
/* Utah */ replace state_num=45 if statefips==49
/* Vermont */ replace state_num=46 if statefips==50
/* Virginia */ replace state_num=47 if statefips==51
/* Washington */ replace state_num=48 if statefips==53
/* West Virginia */ replace state_num=49 if statefips==54
/* Wisconsin */ replace state_num=50 if statefips==55
/* Wyoming */ replace state_num=51 if statefips==56

egen poptotal_racesex=rowtotal(pop*)

bysort state_num year: egen poptotal=sum(poptotal_racesex)

keep year state_num statename poptotal
duplicates drop 
rename poptotal pop

export excel using "Census Pop Estimates by State 1970-1979.xlsx", firstrow(variables) replace

/*	1980 U.S. and State Populations from Census Counts (Not Intercensal Estimates)

	https://www.census.gov/data/datasets/time-series/demo/popest/1980s-state.html
	Data file:
	https://www2.census.gov/programs-surveys/popest/datasets/1980-1990/counties/totals/comp8090.zip
	Documentation file:
	https://www2.census.gov/programs-surveys/popest/technical-documentation/file-layouts/1980-1990/comp8090_doc.txt
	Download Date: March 31, 2021
*/

clear
import delimited "comp8090.txt", delimiters(" ", collapse) stringcols(_all)

rename v1 rowtype
rename v2 fips

tempfile alldata
save `alldata'

drop if rowtype=="B"
rename v3 pop1980
rename v4 pop1990
rename v5 popdiff8090
rename v6 poppc8090
rename v7 totalbirths
rename v8 totaldeaths
rename v9 residchange
rename v10 percresidc
rename v11 state_abb
rename v12 areaname1
rename v13 areaname2
rename v14 areaname3
rename v15 areaname4
rename v16 areaname5
rename v17 areaname6
drop v18 v19 v20 v21 v22 rowtype

tempfile rowAdata
save `rowAdata'

clear
use `alldata'

drop if rowtype=="A"
rename v3 births81
rename v4 births82
rename v5 births83
rename v6 births84
rename v7 births85
rename v8 births86
rename v9 births87
rename v10 births88
rename v11 births89
rename v12 births90
rename v13 deaths81
rename v14 deaths82
rename v15 deaths83
rename v16 deaths84
rename v17 deaths85
rename v18 deaths86
rename v19 deaths87
rename v20 deaths88
rename v21 deaths89
rename v22 deaths90
destring births* deaths*, replace
drop rowtype

tempfile rowBdata
save `rowBdata'

clear
use `rowAdata'
merge 1:1 fips using `rowBdata'

drop _merge
gen statefips=substr(fips,1,2)
gen countyfips=substr(fips,3,.)
order statefips countyfips, first
keep if countyfips=="000"
keep statefips pop1980
destring statefips pop1980, replace
gen state_num=.
order state_num, after(statefips)
/* Alabama */ replace state_num=1 if statefips==1
/* Alaska */ replace state_num=2 if statefips==2
/* Arizona */ replace state_num=3 if statefips==4
/* Arkansas */ replace state_num=4 if statefips==5
/* California */ replace state_num=5 if statefips==6
/* Colorado */ replace state_num=6 if statefips==8
/* Connecticut */ replace state_num=7 if statefips==9
/* Delaware */ replace state_num=8 if statefips==10
/* District of Columbia */ replace state_num=9 if statefips==11
/* Florida */ replace state_num=10 if statefips==12
/* Georgia */ replace state_num=11 if statefips==13
/* Hawaii */ replace state_num=12 if statefips==15
/* Idaho */ replace state_num=13 if statefips==16
/* Illinois */ replace state_num=14 if statefips==17
/* Indiana */ replace state_num=15 if statefips==18
/* Iowa */ replace state_num=16 if statefips==19
/* Kansas */ replace state_num=17 if statefips==20
/* Kentucky */ replace state_num=18 if statefips==21
/* Louisiana */ replace state_num=19 if statefips==22
/* Maine */ replace state_num=20 if statefips==23
/* Maryland */ replace state_num=21 if statefips==24
/* Massachusetts */ replace state_num=22 if statefips==25
/* Michigan */ replace state_num=23 if statefips==26
/* Minnesota */ replace state_num=24 if statefips==27
/* Mississippi */ replace state_num=25 if statefips==28
/* Missouri */ replace state_num=26 if statefips==29
/* Montana */ replace state_num=27 if statefips==30
/* Nebraska */ replace state_num=28 if statefips==31
/* Nevada */ replace state_num=29 if statefips==32
/* New Hampshire */ replace state_num=30 if statefips==33
/* New Jersey */ replace state_num=31 if statefips==34
/* New Mexico */ replace state_num=32 if statefips==35
/* New York */ replace state_num=33 if statefips==36
/* North Carolina */ replace state_num=34 if statefips==37
/* North Dakota */ replace state_num=35 if statefips==38
/* Ohio */ replace state_num=36 if statefips==39
/* Oklahoma */ replace state_num=37 if statefips==40
/* Oregon */ replace state_num=38 if statefips==41
/* Pennsylvania */ replace state_num=39 if statefips==42
/* Rhode Island */ replace state_num=40 if statefips==44
/* South Carolina */ replace state_num=41 if statefips==45
/* South Dakota */ replace state_num=42 if statefips==46
/* Tennessee */ replace state_num=43 if statefips==47
/* Texas */ replace state_num=44 if statefips==48
/* Utah */ replace state_num=45 if statefips==49
/* Vermont */ replace state_num=46 if statefips==50
/* Virginia */ replace state_num=47 if statefips==51
/* Washington */ replace state_num=48 if statefips==53
/* West Virginia */ replace state_num=49 if statefips==54
/* Wisconsin */ replace state_num=50 if statefips==55
/* Wyoming */ replace state_num=51 if statefips==56

replace state_num=-1 if statefips==0
reshape long pop, i(state_num) j(year)
drop statefips

export excel using "Census Pop Estimates by State 1980.xlsx", firstrow(variables) replace


/* 	1981-1989 U.S. and State Intercensal Population Estimates from Census Bureau 

	Data source: https://www.census.gov/data/datasets/time-series/demo/popest/1980s-state.html
	Data file: https://www2.census.gov/programs-surveys/popest/datasets/1980-1990/state/asrh/st_int_asrh.txt
	Documentation file: https://www2.census.gov/programs-surveys/popest/technical-documentation/file-layouts/1980-1990/st_int_asrh_doc.txt
	Download Date: March 31, 2021
*/

clear
import delimited "st_int_asrh.txt", delimiters(" ", collapse) stringcols(_all)

gen statefips=substr(v1,1,2)
gen yearcode=substr(v1,3,1)
gen racecode=substr(v1,4,1)
gen sexcode=substr(v1,5,1)
order statefips yearcode racecode sexcode, first
drop v1

rename v2 pop04
rename v3 pop59
rename v4 pop1014
rename v5 pop1519
rename v6 pop2024
rename v7 pop2529
rename v8 pop3034
rename v9 pop3539
rename v10 pop4044
rename v11 pop4549
rename v12 pop5054
rename v13 pop5559
rename v14 pop6064
rename v15 pop6569
rename v16 pop7074
rename v17 pop7579
rename v18 pop8084
rename v19 pop85

destring statefips, replace
gen state_num=.
order state_num, after(statefips)
/* Alabama */ replace state_num=1 if statefips==1
/* Alaska */ replace state_num=2 if statefips==2
/* Arizona */ replace state_num=3 if statefips==4
/* Arkansas */ replace state_num=4 if statefips==5
/* California */ replace state_num=5 if statefips==6
/* Colorado */ replace state_num=6 if statefips==8
/* Connecticut */ replace state_num=7 if statefips==9
/* Delaware */ replace state_num=8 if statefips==10
/* District of Columbia */ replace state_num=9 if statefips==11
/* Florida */ replace state_num=10 if statefips==12
/* Georgia */ replace state_num=11 if statefips==13
/* Hawaii */ replace state_num=12 if statefips==15
/* Idaho */ replace state_num=13 if statefips==16
/* Illinois */ replace state_num=14 if statefips==17
/* Indiana */ replace state_num=15 if statefips==18
/* Iowa */ replace state_num=16 if statefips==19
/* Kansas */ replace state_num=17 if statefips==20
/* Kentucky */ replace state_num=18 if statefips==21
/* Louisiana */ replace state_num=19 if statefips==22
/* Maine */ replace state_num=20 if statefips==23
/* Maryland */ replace state_num=21 if statefips==24
/* Massachusetts */ replace state_num=22 if statefips==25
/* Michigan */ replace state_num=23 if statefips==26
/* Minnesota */ replace state_num=24 if statefips==27
/* Mississippi */ replace state_num=25 if statefips==28
/* Missouri */ replace state_num=26 if statefips==29
/* Montana */ replace state_num=27 if statefips==30
/* Nebraska */ replace state_num=28 if statefips==31
/* Nevada */ replace state_num=29 if statefips==32
/* New Hampshire */ replace state_num=30 if statefips==33
/* New Jersey */ replace state_num=31 if statefips==34
/* New Mexico */ replace state_num=32 if statefips==35
/* New York */ replace state_num=33 if statefips==36
/* North Carolina */ replace state_num=34 if statefips==37
/* North Dakota */ replace state_num=35 if statefips==38
/* Ohio */ replace state_num=36 if statefips==39
/* Oklahoma */ replace state_num=37 if statefips==40
/* Oregon */ replace state_num=38 if statefips==41
/* Pennsylvania */ replace state_num=39 if statefips==42
/* Rhode Island */ replace state_num=40 if statefips==44
/* South Carolina */ replace state_num=41 if statefips==45
/* South Dakota */ replace state_num=42 if statefips==46
/* Tennessee */ replace state_num=43 if statefips==47
/* Texas */ replace state_num=44 if statefips==48
/* Utah */ replace state_num=45 if statefips==49
/* Vermont */ replace state_num=46 if statefips==50
/* Virginia */ replace state_num=47 if statefips==51
/* Washington */ replace state_num=48 if statefips==53
/* West Virginia */ replace state_num=49 if statefips==54
/* Wisconsin */ replace state_num=50 if statefips==55
/* Wyoming */ replace state_num=51 if statefips==56

destring pop*, replace
egen poptotal_racesex=rowtotal(pop*)

gen year="198"+yearcode
order year, after(yearcode)
destring year, replace

bysort state_num year: egen poptotal=sum(poptotal_racesex)

keep year state_num poptotal
duplicates drop 
rename poptotal pop

export excel using "Census Pop Estimates by State 1981-1989.xlsx", firstrow(variables) replace

/*	1990-1999 Intercensal State Population Estimates from Census Bureau

	Data from: https://www.census.gov/data/datasets/time-series/demo/popest/intercensal-1990-2000-state-and-county-characteristics.html
	Dataset named "Intercensal State and County Characteristics Population
	Estimates with 1990-Base Race Groups
	Download Date: April 1, 2021
	Documentation: https://www2.census.gov/programs-surveys/popest/technical-documentation/file-layouts/1990-2000/stch-intercensal_layout.txt
	1990 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1990.txt
	1991 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1991.txt
	1992 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1992.txt
	1993 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1993.txt
	1994 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1994.txt
	1995 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1995.txt
	1996 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1996.txt
	1997 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1997.txt
	1998 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1998.txt	
	1999 File: https://www2.census.gov/programs-surveys/popest/tables/1990-2000/intercensal/st-co/stch-icen1999.txt
*/

clear
import delimited "stch-icen1990.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1990
save `pop1990'

clear
import delimited "stch-icen1991.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1991
save `pop1991'

clear
import delimited "stch-icen1992.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1992
save `pop1992'

clear
import delimited "stch-icen1993.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1993
save `pop1993'

clear
import delimited "stch-icen1994.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1994
save `pop1994'

clear
import delimited "stch-icen1995.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1995
save `pop1995'

clear
import delimited "stch-icen1996.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1996
save `pop1996'

clear
import delimited "stch-icen1997.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1997
save `pop1997'

clear
import delimited "stch-icen1998.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1998
save `pop1998'

clear
import delimited "stch-icen1999.txt", delimiters(" ", collapse) stringcols(_all)

tempfile pop1999
save `pop1999'

clear
append using `pop1990' `pop1991' `pop1992' `pop1993' `pop1994' `pop1995' ///
	`pop1996' `pop1997' `pop1998' `pop1999'

gen year="19"+v1
order year, first
destring year, replace
drop v1
rename v2 fips
gen statefips=substr(fips,1,2)
gen countyfips=substr(fips,3,.)
order statefips countyfips, after(fips)
rename v3 agecode
rename v4 racesexcode
rename v5 ethnicorigcode
rename v6 grouppop

destring statefips, replace
gen state_num=.
order state_num, after(statefips)
/* Alabama */ replace state_num=1 if statefips==1
/* Alaska */ replace state_num=2 if statefips==2
/* Arizona */ replace state_num=3 if statefips==4
/* Arkansas */ replace state_num=4 if statefips==5
/* California */ replace state_num=5 if statefips==6
/* Colorado */ replace state_num=6 if statefips==8
/* Connecticut */ replace state_num=7 if statefips==9
/* Delaware */ replace state_num=8 if statefips==10
/* District of Columbia */ replace state_num=9 if statefips==11
/* Florida */ replace state_num=10 if statefips==12
/* Georgia */ replace state_num=11 if statefips==13
/* Hawaii */ replace state_num=12 if statefips==15
/* Idaho */ replace state_num=13 if statefips==16
/* Illinois */ replace state_num=14 if statefips==17
/* Indiana */ replace state_num=15 if statefips==18
/* Iowa */ replace state_num=16 if statefips==19
/* Kansas */ replace state_num=17 if statefips==20
/* Kentucky */ replace state_num=18 if statefips==21
/* Louisiana */ replace state_num=19 if statefips==22
/* Maine */ replace state_num=20 if statefips==23
/* Maryland */ replace state_num=21 if statefips==24
/* Massachusetts */ replace state_num=22 if statefips==25
/* Michigan */ replace state_num=23 if statefips==26
/* Minnesota */ replace state_num=24 if statefips==27
/* Mississippi */ replace state_num=25 if statefips==28
/* Missouri */ replace state_num=26 if statefips==29
/* Montana */ replace state_num=27 if statefips==30
/* Nebraska */ replace state_num=28 if statefips==31
/* Nevada */ replace state_num=29 if statefips==32
/* New Hampshire */ replace state_num=30 if statefips==33
/* New Jersey */ replace state_num=31 if statefips==34
/* New Mexico */ replace state_num=32 if statefips==35
/* New York */ replace state_num=33 if statefips==36
/* North Carolina */ replace state_num=34 if statefips==37
/* North Dakota */ replace state_num=35 if statefips==38
/* Ohio */ replace state_num=36 if statefips==39
/* Oklahoma */ replace state_num=37 if statefips==40
/* Oregon */ replace state_num=38 if statefips==41
/* Pennsylvania */ replace state_num=39 if statefips==42
/* Rhode Island */ replace state_num=40 if statefips==44
/* South Carolina */ replace state_num=41 if statefips==45
/* South Dakota */ replace state_num=42 if statefips==46
/* Tennessee */ replace state_num=43 if statefips==47
/* Texas */ replace state_num=44 if statefips==48
/* Utah */ replace state_num=45 if statefips==49
/* Vermont */ replace state_num=46 if statefips==50
/* Virginia */ replace state_num=47 if statefips==51
/* Washington */ replace state_num=48 if statefips==53
/* West Virginia */ replace state_num=49 if statefips==54
/* Wisconsin */ replace state_num=50 if statefips==55
/* Wyoming */ replace state_num=51 if statefips==56

destring grouppop, replace
bysort state_num year: egen poptotal=sum(grouppop)

keep year state_num poptotal
duplicates drop 
rename poptotal pop

export excel using "Census Pop Estimates by State 1990-1999.xlsx", firstrow(variables) replace

/*	2000-2009 U.S. and State Intercensal Population Estimates from Census Bureau
	
	Data source: https://www.census.gov/data/tables/time-series/demo/popest/intercensal-2000-2010-state.html
	Table name: Intercensal Estimates of the Resident Population for the United
	States, Regions, States, and Puerto Rico: April 1, 2000 to July 1, 2010"
	Download Date: April 1, 2021
*/

clear
import excel "st-est00int-01.xls", firstrow allstring clear

rename tablewithrowheadersincolumn A
format A %30s
replace A="statename" if C=="2000"
drop B M N
drop if regexm(A, "Table 1.")
drop if regexm(A, "1 The April")
drop if regexm(A, "2 The data")
drop if regexm(A, "3 The values")
drop if regexm(A, "Note:")
drop if regexm(A, "Suggested Citation:")
drop if regexm(A, "Source:")
drop if regexm(A, "Release Date:")
drop if regexm(A, "Geographic")
drop if missing(A)
drop if regexm(A, "Northeast")
drop if regexm(A, "Midwest")
drop if A=="South"
drop if A=="West"
replace A=subinstr(A, ".", "", .)
replace C="pop2000" if A=="statename"
replace D="pop2001" if A=="statename"
replace E="pop2002" if A=="statename"
replace F="pop2003" if A=="statename"
replace G="pop2004" if A=="statename"
replace H="pop2005" if A=="statename"
replace I="pop2006" if A=="statename"
replace J="pop2007" if A=="statename"
replace K="pop2008" if A=="statename"
replace L="pop2009" if A=="statename"

gen order=_n
gen us=0
replace us=1 if A=="Puerto Rico"
replace us=2 if A=="statename" | A=="United States"
sort us A
gen state_num=_n
sort order
gen state_num_string=string(state_num)
replace state_num_string="state_num" if A=="statename"
replace state_num_string="-1" if A=="United States"
drop order us state_num
rename state_num_string state_num

export excel using "Census Pop Estimates by State 2000-2009.xlsx", replace

/*	2010-2019 U.S. and State Intercensal Population Estimates from Census Bureau
	
	Data source: https://www.census.gov/data/tables/time-series/demo/popest/2010s-state-total.html
	Table name: "Annual Estimates of the Resident Population for the United 
	States, Regions, and Puerto Rico: April 1, 2010 to July 1, 2019 
	(NST-EST2019-01)" 
	Download Date: March 31, 2021
*/
	
clear
import excel "nst-est2019-01.xlsx", firstrow allstring clear

rename tablewithrowheadersincolumn A
format A %30s
format D %9s
replace A="statename" if B=="Census"
drop if missing(A)
drop if regexm(A, "Table 1.")
drop if regexm(A, "Geographic")
drop if regexm(A, "Note:")
drop if regexm(A, "Suggested")
drop if regexm(A, "Source:")
drop if regexm(A, "Release Date:")
drop if regexm(A, "Northeast")
drop if regexm(A, "Midwest")
drop if A=="South"
drop if A=="West"
drop B C 
replace A=subinstr(A, ".", "", .)
replace D="pop2010" if A=="statename"
replace E="pop2011" if A=="statename"
replace F="pop2012" if A=="statename"
replace G="pop2013" if A=="statename"
replace H="pop2014" if A=="statename"
replace I="pop2015" if A=="statename"
replace J="pop2016" if A=="statename"
replace K="pop2017" if A=="statename"
replace L="pop2018" if A=="statename"
replace M="pop2019" if A=="statename"

gen order=_n
gen us=0
replace us=1 if A=="Puerto Rico"
replace us=2 if A=="statename" | A=="United States"
sort us A
gen state_num=_n
sort order
gen state_num_string=string(state_num)
replace state_num_string="state_num" if A=="statename"
replace state_num_string="-1" if A=="United States"
drop order us state_num
rename state_num_string state_num

export excel using "Census Pop Estimates by State 2010-2019.xlsx", replace

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

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

					Part 2: Updating the Final Dataset
	
*******************************************************************************/

/*	Note: This code adds new data to the population and filings dataset created
	in 2015 for the UC Irvine Law Review article referenced above.
	
	Updated local jail data come from the Incarceration Trends dataset from
	the Vera Institute of Justice. These data are average daily populations. 
	This code replaces the previous jail population counts and estimates with 
	Vera ADP numbers for 1970-2018.
	
	Updated state prison data come from the Quick Tables named "Inmates in 
	Custody of State or Federal Correctional Facilities, Excluding Private 
	Prison Facilities, December 31, 1978-2019" for 1978-1998, and "Inmates in 
	Custody of State or Federal Correctional Facilities, Including Private 
	Prison Facilities, December 31, 1999-2019" for 1999-2019; both come from the 
	Bureau of Justice Statistics. These data come from the BJS National Prisoner 
	Statistics dataset, based on a survey administered by state prison 
	authorities on December 31 of each year. This code replaces the previous 
	state prison counts for 1978-2012 and adds new counts for 2013-2019.
	
	Updated federal prison data come from three sources: Total U.S. federal 
	prison counts come from the same BJS source as the state prison counts.
	Those counts include federal prisoner counts from all public BOP 
	institutions, all private institutions on contract with BOP, and contract
	community-based facilities with the exception of home confinement. This code
	replaces the previous U.S. federal prisoner counts for 1978-2012 and adds 
	new counts for 2013-2019.
	
	Updated federal prisoner counts by state come from reports from the Federal 
	Bureau of Prisons and include counts of all public BOP institutions and 
	private institutions on contract with BOP. The 2013 counts originate from 
	the same official data release from a BOP statistician as the 1994-2012 
	counts. The 2014-2020 counts were obtained directly from the BOP website 
	(BOP publishes updated facilty population counts weekly) using the Wayback 
	Machine from the Internet Archive, using release dates that approximate the 
	late-September dates from the 1994-2013 data as closely as possible. This
	code adds new counts for 2013-2020. 
	
	Updated U.S. and state population data come from intercensal estimates
	published by the U.S. Census Bureau, with the exception of U.S. and state
	populations in 1980, which are the actual census counts. Intercensal 
	estimates are projections of the general population on July 1 of each year.
	Census counts are from April 1. This code replaces all previous counts for
	2000-2012 and adds new counts for 1970-1999 and 2013-2019.
	*/
	
cd "$project\Data\Raw Data"

clear
use "population_and_filings_1970-2012.dta", clear

tempfile originaldata
save `originaldata'

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

	I. Updating Local Jail Populations by State
	
********************************************************************************
*******************************************************************************/

clear
cd "$project\Data"
import excel "Jail Pop by State - Vera Incarceration Trends.xlsx", firstrow clear

gen state_num=substr(State,1,2)
order state_num, first
destring state_num, replace
rename Vera_adp* Vera_jail*
reshape long Vera_jail, i(state_num) j(year)

tempfile jailpop_Vera
save `jailpop_Vera'

clear
use `originaldata'
merge 1:1 state_num year using `jailpop_Vera'

replace state=state_num if _merge==2
drop _merge State
sort state year
order Vera_jail, before(jail)
drop jail
rename Vera_jail jail
sum year if !missing(jail)
local max=`r(max)'
replace jail_source=.
replace jail_source=1 if year<=`max' & !missing(jail)
#delimit ;
label define jaillabel
	1  "Incar. Trends 1970-2018, Vera Inst. Justice (2020)", replace;
#delimit cr
label values jail_source jaillabel
replace jail_units=.
replace jail_units=1 if year<=`max' & !missing(jail)
#delimit ;
label define jailunitlabel
	1 "ADP", replace;
#delimit cr
label values jail_units jailunitlabel
drop estimate jail_intrp intrp

tempfile pop_filings_updated1
save `pop_filings_updated1'

/*******************************************************************************
********************************************************************************
	
	II. Updating State Prison Populations by State
	
********************************************************************************
*******************************************************************************/

clear
cd "$project\Data\Raw Data"
import excel "State Prison Counts by State.xlsx", firstrow clear

reshape long prison, i(state_num) j(year)
rename prison prison_updated

tempfile stateprisonpop
save `stateprisonpop'

clear
use `pop_filings_updated1'
merge 1:1 state_num year using `stateprisonpop'

replace prison=prison_updated if year>=1999
drop statename prison_updated _merge
sum year if !missing(prison)
local max=`r(max)'
replace prison_units=2 if year>=1999 & year<=`max'
replace prison_source=.
replace prison_source=1 if year==1970
replace prison_source=2 if year>=1971 & year<=1973
replace prison_source=3 if year==1974
replace prison_source=4 if year==1975
replace prison_source=5 if year==1976
replace prison_source=6 if year==1977
replace prison_source=7 if year>=1978 & year<=1998
replace prison_source=8 if year>=1999 & year<=`max'
replace prison_units=. if missing(prison)
replace prison_source=. if missing(prison)
#delimit ;
label define prisonunitslabel
	1 "ADP"
	2 "Count on December 31", replace;
#delimit cr
label values prison_units prisonunitslabel
#delimit ;
label define prisonsourcelabel
	1 "Prisoners in St & Fed Insts 1968-1970, tbl. 1, p. 11"
	2 "Prisoners in St & Fed Insts 1974, App. I, tbl. 1, p. 14"
	3 "Prisoners in St & Fed Insts 1974, App. II, tbl. 1, p. 36"
	4 "Prisoners in St & Fed Insts 1975, App. II, tbl. 1, p. 36"
	5 "Prisoners in St & Fed Insts 1976, App. II, tbl. 1, p. 32"
	6 "Prisoners in St & Fed Insts 1978, App. III, Sp. tbl., p. 42"
	7 "Inmates St & Fed Corr. 1978-2019 (excl. priv)"
	8 "Inmates St & Fed Corr. 1999-2019 (incl. priv)", replace;
#delimit cr
label values prison_source prisonsourcelabel

tempfile pop_filings_updated2
save `pop_filings_updated2'

/*******************************************************************************
********************************************************************************
	
	III. Updating Federal Prison Populations by State
	
********************************************************************************
*******************************************************************************/

/* Part 1: Using Federal Prison counts from BJS CSAT Quick Table on State Prison 
	Inmates by state */

cd "$project\Data\Raw Data\BJS CSAT"
clear
import excel "State Prison Counts by State w-o Private.xlsx", allstring firstrow clear
forvalues i=1999/2019 {
	drop prison`i'
	}
keep if Jurisdiction=="Federal"

tempfile statepris_US7898
save `statepris_US7898'

clear
import excel "State Prison Counts by State w Private.xlsx", allstring firstrow clear
keep if Jurisdiction=="Federal"

tempfile statepris_US9919
save `statepris_US9919'

clear
use `statepris_US7898'
merge 1:1 Jurisdiction using `statepris_US9919'
drop _merge
rename Jurisdiction statename
rename prison* fed_BJS*
replace statename="United States" if statename=="Federal"
gen state_num=-1
order state_num, first
reshape long fed_BJS, i(state_num) j(year)
destring fed_BJS, replace

tempfile usfedprisonpop
save `usfedprisonpop'

clear
use `pop_filings_updated2'
merge 1:1 state_num year using `usfedprisonpop'

order fed_BJS, after(fed)
replace fed=fed_BJS if state_num==-1 & year>=1999
drop statename _merge fed_BJS
replace fed_source=10 if state_num==-1 & year>=1999
replace fed_units=2 if state_num==-1 & year>=2013
sum year
local latestyear=`r(max)'

tempfile pop_filings_updated3
save `pop_filings_updated3'

/* Part 2: Using Bureau of Prisons annual federal inmate population counts by state */

clear
cd "$project\Data\Raw Data\Federal Bureau of Prisons"
import excel "Federal Prisoners by State 1994-Present.xlsx", sheet("Sheet1") firstrow clear

reshape long fed, i(state_num) j(year)
rename fed fed_BOP

tempfile states_fedprisonpop
save `states_fedprisonpop'

clear
use `pop_filings_updated3'
merge 1:1 state_num year using `states_fedprisonpop'

order fed_BOP, after(fed)
replace fed=fed_BOP if state_num!=-1 & year>=1994
drop state_abb fed_BOP _merge
replace fed_source=.
replace fed_source=1 if state_num==-1 & year==1970
replace fed_source=2 if state_num==-1 & year>=1971 & year<=1973
replace fed_source=3 if state_num==-1 & year==1974
replace fed_source=4 if state_num==-1 & year==1975
replace fed_source=5 if state_num==-1 & year==1976
replace fed_source=6 if state_num==-1 & year==1977
replace fed_source=7 if state_num==-1 & year>=1978 & year<=1998
replace fed_source=8 if state_num==-1 & year>=1999
replace fed_source=9 if state_num!=-1 & year>=1970 & year<=1993
replace fed_source=10 if state_num!=-1 & year>=1994 & year<=2013
replace fed_source=11 if state_num!=-1 & year>=2014
replace fed_source=. if missing(fed)
replace fed_units=3 if state_num!=-1 & year==2013
replace fed_units=4 if state_num!=-1 & year==2014
replace fed_units=5 if state_num!=-1 & year==2015
replace fed_units=6 if state_num!=-1 & year==2016
replace fed_units=7 if state_num!=-1 & year==2017
replace fed_units=8 if state_num!=-1 & year==2018
replace fed_units=9 if state_num!=-1 & year==2019
replace fed_units=10 if state_num!=-1 & year==2020
replace fed_units=. if missing(fed)
#delimit ;
label define fedsourcelabel
	1 "Prisoners in St & Fed Insts 1968-1970, tbl. 1, p. 11"
	2 "Prisoners in St & Fed Insts 1974, App. I, tbl. 1, p. 14"
	3 "Prisoners in St & Fed Insts 1974, App. II, tbl. 1, p. 36"
	4 "Prisoners in St & Fed Insts 1975, App. II, tbl. 1, p. 36"
	5 "Prisoners in St & Fed Insts 1976, App. II, tbl. 1, p. 32"
	6 "Prisoners in St & Fed Insts 1978, App. III, Sp. tbl., p. 42"
	7 "Inmates St & Fed Corr. 1978-2019 (excl. priv)"
	8 "Inmates St & Fed Corr. 1999-2019 (incl. priv)"
	9 "Federal BOP, Statistical Report (annual)"
	10 "BOP Yearly Facility Population Data"
	11 "Downloaded from BOP population stats website", replace;
#delimit cr
label values fed_source fedsourcelabel
#delimit ;
label define fedunitslabel
	1 "Units from Source Unclear"
	2 "Count on December 31"
	3 "Count on September 31"
	4 "Count on Jun. 12, 2014"
	5 "Count on Jan. 28, 2016"
	6 "Count on Dec. 15, 2016"
	7 "Count on Sep. 28, 2017"
	8 "Count on Oct. 11, 2018"
	9 "Count on Oct. 17, 2019"
	10 "Count on Oct. 08, 2020", replace;
#delimit cr
label values fed_units fedunitslabel

tempfile pop_filings_updated4
save `pop_filings_updated4'

/*	Part 3: Adding US Total BOP Inmate Population Counts (including community
	housing) */
	
clear
import excel "US Total BOP Inmate Population 1980-2020.xlsx", firstrow clear

destring year fed_BOP state_num, replace

tempfile BOP_total_counts
save `BOP_total_counts'

clear
use `pop_filings_updated4'
merge 1:1 year state_num using `BOP_total_counts'

order fed_BOP, after(fed)
*label var fed_BOP "US Total Federal Inmates Housed in Public, Private, and Comm. Housing"
drop _merge fed_BOP

tempfile pop_filings_updated5
save `pop_filings_updated5'

/*******************************************************************************
********************************************************************************
	
	IV. Updating Civil Rights Federal Lawsuit Filing Counts by State

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

/*	Importing All Civil Rights Federal Lawsuit Filings */

clear
cd "$project\Data"
import excel "FJC Civil Rights Filings by State.xlsx", sheet("All Filings") firstrow clear	

gen state_num=substr(State,1,2)
order state_num, first
destring state_num, replace
rename State statename
reshape long filings, i(state_num) j(year)
rename filings filings_updated

tempfile allfilings
save `allfilings'

/*	Importing Civil Rights Federal Lawsuit Filings with Non-Federal Defendants */

clear
cd "$project\Data"
import excel "FJC Civil Rights Filings by State.xlsx", sheet("Non-Fed Def Filings") firstrow clear	

gen state_num=substr(State,1,2)
order state_num, first
destring state_num, replace
rename State statename
reshape long statefi, i(state_num) j(year)
rename statefi statefi_updated

tempfile nonfeddeffilings
save `nonfeddeffilings'

/*	Importing Civil Rights Federal Lawsuit Filings with Federal Defendants */

clear
cd "$project\Data"
import excel "FJC Civil Rights Filings by State.xlsx", sheet("Fed Def Filings") firstrow clear	

gen state_num=substr(State,1,2)
order state_num, first
destring state_num, replace
rename State statename
reshape long fedfi, i(state_num) j(year)
rename fedfi fedfi_updated

tempfile feddeffilings
save `feddeffilings'

/*	Merging All Lawsuit Filing Counts with Jail and Prison Population Counts */

clear
use `pop_filings_updated5'
merge 1:1 state_num year using `allfilings'

drop _merge
order filings_updated, after(filings)
replace filings=filings_updated
drop filings_updated

merge 1:1 state_num year using `nonfeddeffilings'

drop _merge
order statefi_updated, after(statefi)
replace statefi=statefi_updated
drop statefi_updated

merge 1:1 state_num year using `feddeffilings'

drop _merge
order fedfi_updated, after(fedfi)
replace fedfi=fedfi_updated
drop fedfi_updated
replace state=state_num if missing(state)
drop statename
bysort state: egen circuit_filler=mean(circuit)
replace circuit=circuit_filler if missing(circuit)
drop circuit_filler

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

tempfile pop_filings_updated6
save `pop_filings_updated6'

/*******************************************************************************
********************************************************************************
	
	V. Updating U.S. and State Population Variable
	
********************************************************************************
*******************************************************************************/

cd "$project\Data\Raw Data\Census Bureau"

clear
import excel "Census Pop Estimates by State 1970-1979.xlsx", firstrow clear

drop statename

tempfile pop7079
save `pop7079'

clear
import excel "Census Pop Estimates by State 1980.xlsx", firstrow clear

tempfile pop80
save `pop80'

clear
import excel "Census Pop Estimates by State 1981-1989.xlsx", firstrow clear

tempfile pop8189
save `pop8189'

clear
import excel "Census Pop Estimates by State 1990-1999.xlsx", firstrow clear

rename poptotal pop

tempfile pop9099
save `pop9099'

clear
import excel "Census Pop Estimates by State 2000-2009.xlsx", firstrow clear

destring pop* state_num, replace
reshape long pop, i(state_num) j(year)
drop statename

tempfile pop0009
save `pop0009'

clear
import excel "Census Pop Estimates by State 2010-2019.xlsx", firstrow clear

destring state_num pop*, replace
reshape long pop, i(state_num) j(year)
drop statename

tempfile pop1019
save `pop1019'

clear
append using `pop7079' `pop80' `pop8189' `pop9099' `pop0009' `pop1019'

rename pop pop_updated

tempfile censusdata
save `censusdata'

clear
use `pop_filings_updated6'
merge 1:1 state_num year using `censusdata'

order pop_updated, after(pop)
gen uspoptotal=.
recast long uspoptotal
order uspoptotal, after(pop_updated)
forvalues i=1970/2018 {
	sum pop_updated if year==`i' & state_num!=-1 & state_num!=52
	replace uspoptotal=`r(sum)' if year==`i'
	}
sort state year
replace pop_updated=uspoptotal if state_num==-1 & missing(pop_updated) & ///
	year<=`latestyear'
	
replace pop=pop_updated
drop pop_updated uspoptotal _merge
replace pop_source=1 if year>=1970 & year<=1979
replace pop_source=2 if year==1980
replace pop_source=3 if year>=1981 & year<=1989
replace pop_source=4 if year>=1990 & year<=1999
replace pop_source=5 if year>=2000 & year<=2009
sum year if !missing(pop)
local latestyear=`r(max)'
replace pop_source=6 if year>=2010 & year<=`latestyear'
#delimit ;
label define popsourcelabel
	1 "1970-1979 U.S. and State Intercensal Estimates"
	2 "1980 Census U.S. and State Population Counts"
	3 "1981-1989 U.S. and State Intercensal Estimates"
	4 "1990-1999 U.S. and State Intercensal Estimates"
	5 "2000-2009 U.S. and State Intercensal Estimates"
	6 "2010-2019 U.S. and State Intercensal Estimates", replace;
#delimit cr
label values pop_source popsourcelabel

gen pop_units=.
order pop_units, before(pop_source)
replace pop_units=1 if year>=1970 & year<=`latestyear' & year!=1980 ///
	& state_num!=-1 & state_num!=52
replace pop_units=2 if year==1980 & state_num!=-1 & state_num!=52
replace pop_units=3 if year>=1970 & year<=`latestyear' & year!=1980 ///
	& state_num==-1
replace pop_units=4 if year==1980 & state_num==-1
replace pop_units=5 if year>=1970 & year<=`latestyear' & year!=1980 ///
	& state_num==52
replace pop_units=6 if year==1980 & state_num==52
#delimit ;	
label define popunitslabel
	1 "Intercensal Estimate, July 1"
	2 "Census Count, April 1"
	3 "Intercensal Est. of 50 States + D.C., July 1"
	4 "Census Count of 50 States + D.C., April 1"
	5 "Puerto Rico Only - Intercensal Est., July 1"
	6 "Puerto Rico Only - Census Count, April 1", replace;
#delimit cr
label values pop_units popunitslabel

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

	VI. Updating Calculated Variables

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

/*	Total Incarcerated Population by State */
egen incar_updated=rowtotal(prison fed jail)
replace incar_updated=. if missing(jail)
order incar_updated, after(incar)
replace incar=incar_updated
replace incar=. if incar==0

/*	Lawsuit Filing Rates by State */
gen fedFiRate_updated = (1000*fedfi)/fed
order fedFiRate_updated, after(fedFiRate)
gen nonfedFiRate_updated = (1000*statefi)/(prison + jail)
order nonfedFiRate_updated, after(nonfedFiRate)
gen FiRate_updated = (1000*filings)/(incar)
order FiRate_updated, after(FiRate)
replace fedFiRate=fedFiRate_updated
replace nonfedFiRate=nonfedFiRate_updated
replace FiRate=FiRate_updated
drop *_updated

format pop incar prison fed jail filings statefi fedfi %13.0gc

label var state "State where incarcerated pop housed & civil rights case filed"
label var state_num "Duplicate of 'state' variable without labels"
label var circuit "Federal Circuit in which state is grouped"
label var year "Calendar year of incar counts; Tape year of civil rights filings"
label var pop "General population estimate for region defined by 'state' var"
label var incar "Total Incarcerated Population (Fed Prisons + State Prisons + Jails)"
label var prison "State Prison Population"
label var fed "Federal Incarcerated Population"
label var jail "Jail Population"
label var filings "Federal Civil Rights Filing Counts"
label var statefi "Federal Civil Rights Filings with Non-federal Defendants"
label var fedfi "Federal Civil Rights Filings with Federal Defendants"
label var nonfedFiRate "Non-fed filing rate per 1,000 prisoners (jail, state prison)"
label var fedFiRate "Fed filing rate per 1,000 prisoners (fed prison)"
label var FiRate "Total filing rate per 1,000 prisoners (jail, state & fed prison)"
label var prison_units "Whether state prison figure is ADP, count, or estimate"
label var prison_source "Source of state prison population figure"
label var fed_units "Whether federal prison figure is ADP, count, or estimate"
label var fed_source "Source of federal prison population figure"
label var jail_units "Whether jail figure is ADP, count, or estimate"
label var jail_source "Source of local jail population figure"
label var pop_units "Whether population figure is Census count or estimate"
label var pop_source "Source of population figure within U.S. Census Bureau"

cd "$project\Data"
sum year
local present=`r(max)'
save "population_and_filings_1970-`present'.dta", replace

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