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

	Creating dataset of restricted housing variables in the Census of State 
	Adult Correctional Facilities (1979, 1984) & the Census of State and Federal 
	Adult Correctional Facilities (1990, 1995, 2000, 2005)

	Do File Author: German Marquez Alcala
	Data Downloaded: 03-30-2020
	Last Edited: 07-22-2020
	
	Data Sources:
	1979 Census: https://www.icpsr.umich.edu/web/ICPSR/studies/7852
	1984 Census: https://www.icpsr.umich.edu/web/ICPSR/studies/8444
	1990 Census: https://www.icpsr.umich.edu/web/ICPSR/studies/9908
	1995 Census: https://www.icpsr.umich.edu/web/ICPSR/studies/6953
	2000 Census: https://www.icpsr.umich.edu/web/ICPSR/studies/4021
	2005 Census: https://www.icpsr.umich.edu/web/ICPSR/studies/24642
	
*******************************************************************************/

clear
global project "C:/Users/gmarquez/Box/Empirical Research Service/Margo Schlanger/Empirical Research Service/Restricted Housing"

cd "$project\Data\Raw Data\Census of State and Federal Adult Correctional Facilities\1979"
use "07852-0001-Data.dta"

rename V6 statenum

cd "$project\Data"
save "1979 Prison Census Data.dta", replace

cd "$project\Data\Raw Data"
import excel "List of States.xlsx", firstrow clear
cd "$project\Data"
save "List of States.dta", replace

cd "$project\Data"
clear
use "List of States.dta", clear
tempfile Listofstates
save `Listofstates'
use "1979 Prison Census Data.dta"
merge m:1 statenum using `Listofstates'

order statename, after(statenum)

gen protectcust1=V102 if V100==2
gen protectcust2=V111 if V109==2
gen protectcust3=V120 if V118==2
gen protectcust4=V129 if V127==2
gen protectcust5=V138 if V136==2
gen protectcust6=V147 if V145==2
gen protectcust7=V156 if V154==2
gen protectcust8=V165 if V163==2
gen protectcust9=V174 if V172==2
gen protectcust10=V183 if V181==2
gen protectcust11=V192 if V190==2
gen protectcust12=V201 if V199==2
gen protectcust13=V210 if V208==2
gen protectcust14=V219 if V217==2
gen protectcust15=V228 if V226==2
gen protectcust16=V237 if V235==2
gen protectcust17=V246 if V244==2
gen protectcust18=V255 if V253==2
gen protectcust19=V264 if V262==2
gen protectcust20=V273 if V271==2

gen adminseg1=V102 if V100==3
gen adminseg2=V111 if V109==3
gen adminseg3=V120 if V118==3
gen adminseg4=V129 if V127==3
gen adminseg5=V138 if V136==3
gen adminseg6=V147 if V145==3
gen adminseg7=V156 if V154==3
gen adminseg8=V165 if V163==3
gen adminseg9=V174 if V172==3
gen adminseg10=V183 if V181==3
gen adminseg11=V192 if V190==3
gen adminseg12=V201 if V199==3
gen adminseg13=V210 if V208==3
gen adminseg14=V219 if V217==3
gen adminseg15=V228 if V226==3
gen adminseg16=V237 if V235==3
gen adminseg17=V246 if V244==3
gen adminseg18=V255 if V253==3
gen adminseg19=V264 if V262==3
gen adminseg20=V273 if V271==3

gen disciplinaction1=V102 if V100==4
gen disciplinaction2=V111 if V109==4
gen disciplinaction3=V120 if V118==4
gen disciplinaction4=V129 if V127==4
gen disciplinaction5=V138 if V136==4
gen disciplinaction6=V147 if V145==4
gen disciplinaction7=V156 if V154==4
gen disciplinaction8=V165 if V163==4
gen disciplinaction9=V174 if V172==4
gen disciplinaction10=V183 if V181==4
gen disciplinaction11=V192 if V190==4
gen disciplinaction12=V201 if V199==4
gen disciplinaction13=V210 if V208==4
gen disciplinaction14=V219 if V217==4
gen disciplinaction15=V228 if V226==4
gen disciplinaction16=V237 if V235==4
gen disciplinaction17=V246 if V244==4
gen disciplinaction18=V255 if V253==4
gen disciplinaction19=V264 if V262==4
gen disciplinaction20=V273 if V271==4

gen sickorinjured1=V102 if V100==5
gen sickorinjured2=V111 if V109==5
gen sickorinjured3=V120 if V118==5
gen sickorinjured4=V129 if V127==5
gen sickorinjured5=V138 if V136==5
gen sickorinjured6=V147 if V145==5
gen sickorinjured7=V156 if V154==5
gen sickorinjured8=V165 if V163==5
gen sickorinjured9=V174 if V172==5
gen sickorinjured10=V183 if V181==5
gen sickorinjured11=V192 if V190==5
gen sickorinjured12=V201 if V199==5
gen sickorinjured13=V210 if V208==5
gen sickorinjured14=V219 if V217==5
gen sickorinjured15=V228 if V226==5
gen sickorinjured16=V237 if V235==5
gen sickorinjured17=V246 if V244==5
gen sickorinjured18=V255 if V253==5
gen sickorinjured19=V264 if V262==5
gen sickorinjured20=V273 if V271==5

gen other1_1=V102 if V100==6
gen other1_2=V111 if V109==6
gen other1_3=V120 if V118==6
gen other1_4=V129 if V127==6
gen other1_5=V138 if V136==6
gen other1_6=V147 if V145==6
gen other1_7=V156 if V154==6
gen other1_8=V165 if V163==6
gen other1_9=V174 if V172==6
gen other1_10=V183 if V181==6
gen other1_11=V192 if V190==6
gen other1_12=V201 if V199==6
gen other1_13=V210 if V208==6
gen other1_14=V219 if V217==6
gen other1_15=V228 if V226==6
gen other1_16=V237 if V235==6
gen other1_17=V246 if V244==6
gen other1_18=V255 if V253==6
gen other1_19=V264 if V262==6
gen other1_20=V273 if V271==6

gen other2_1=V102 if V100==7
gen other2_2=V111 if V109==7
gen other2_3=V120 if V118==7
gen other2_4=V129 if V127==7
gen other2_5=V138 if V136==7
gen other2_6=V147 if V145==7
gen other2_7=V156 if V154==7
gen other2_8=V165 if V163==7
gen other2_9=V174 if V172==7
gen other2_10=V183 if V181==7
gen other2_11=V192 if V190==7
gen other2_12=V201 if V199==7
gen other2_13=V210 if V208==7
gen other2_14=V219 if V217==7
gen other2_15=V228 if V226==7
gen other2_16=V237 if V235==7
gen other2_17=V246 if V244==7
gen other2_18=V255 if V253==7
gen other2_19=V264 if V262==7
gen other2_20=V273 if V271==7

gen other3_1=V102 if V100==8
gen other3_2=V111 if V109==8
gen other3_3=V120 if V118==8
gen other3_4=V129 if V127==8
gen other3_5=V138 if V136==8
gen other3_6=V147 if V145==8
gen other3_7=V156 if V154==8
gen other3_8=V165 if V163==8
gen other3_9=V174 if V172==8
gen other3_10=V183 if V181==8
gen other3_11=V192 if V190==8
gen other3_12=V201 if V199==8
gen other3_13=V210 if V208==8
gen other3_14=V219 if V217==8
gen other3_15=V228 if V226==8
gen other3_16=V237 if V235==8
gen other3_17=V246 if V244==8
gen other3_18=V255 if V253==8
gen other3_19=V264 if V262==8
gen other3_20=V273 if V271==8

egen protectcust_tot=rowtotal(protectcust1 protectcust2 protectcust3 protectcust4 ///
	protectcust5 protectcust6 protectcust7 protectcust8 protectcust9 ///
	protectcust10 protectcust11 protectcust12 protectcust13 ///
	protectcust14 protectcust15 protectcust16 protectcust17 ///
	protectcust18 protectcust19 protectcust20)
	
egen adminseg_tot=rowtotal(adminseg1 adminseg2 adminseg3 adminseg4 ///
	adminseg5 adminseg6 adminseg7 adminseg8 adminseg9 ///
	adminseg10 adminseg11 adminseg12 adminseg13 ///
	adminseg14 adminseg15 adminseg16 adminseg17 ///
	adminseg18 adminseg19 adminseg20)
	
egen disciplinaction_tot=rowtotal(disciplinaction1 disciplinaction2 disciplinaction3 disciplinaction4 ///
	disciplinaction5 disciplinaction6 disciplinaction7 disciplinaction8 disciplinaction9 ///
	disciplinaction10 disciplinaction11 disciplinaction12 disciplinaction13 ///
	disciplinaction14 disciplinaction15 disciplinaction16 disciplinaction17 ///
	disciplinaction18 disciplinaction19 disciplinaction20)
	
egen solitarycust_tot=rowtotal(adminseg_tot disciplinaction_tot)
	
egen sickorinjured_tot=rowtotal(sickorinjured1 sickorinjured2 sickorinjured3 sickorinjured4 ///
	sickorinjured5 sickorinjured6 sickorinjured7 sickorinjured8 sickorinjured9 ///
	sickorinjured10 sickorinjured11 sickorinjured12 sickorinjured13 ///
	sickorinjured14 sickorinjured15 sickorinjured16 sickorinjured17 ///
	sickorinjured18 sickorinjured19 sickorinjured20)
	
egen other_tot=rowtotal(other1_1 other1_2 other1_3 other1_4 ///
	other1_5 other1_6 other1_7 other1_8 other1_9 ///
	other1_10 other1_11 other1_12 other1_13 ///
	other1_14 other1_15 other1_16 other1_17 ///
	other1_18 other1_19 other1_20 ///
	other2_1 other2_2 other2_3 other2_4 ///
	other2_5 other2_6 other2_7 other2_8 other2_9 ///
	other2_10 other2_11 other2_12 other2_13 ///
	other2_14 other2_15 other2_16 other2_17 ///
	other2_18 other2_19 other2_20 ///
	other3_1 other3_2 other3_3 other3_4 ///
	other3_5 other3_6 other3_7 other3_8 other3_9 ///
	other3_10 other3_11 other3_12 other3_13 ///
	other3_14 other3_15 other3_16 other3_17 ///
	other3_18 other3_19 other3_20)

gen protectcust1max=V102 if V100==2 & V101==1
gen protectcust2max=V111 if V109==2 & V110==1
gen protectcust3max=V120 if V118==2 & V119==1
gen protectcust4max=V129 if V127==2 & V128==1
gen protectcust5max=V138 if V136==2 & V137==1
gen protectcust6max=V147 if V145==2 & V146==1
gen protectcust7max=V156 if V154==2 & V155==1
gen protectcust8max=V165 if V163==2 & V164==1
gen protectcust9max=V174 if V172==2 & V173==1
gen protectcust10max=V183 if V181==2 & V182==1
gen protectcust11max=V192 if V190==2 & V191==1
gen protectcust12max=V201 if V199==2 & V200==1
gen protectcust13max=V210 if V208==2 & V209==1
gen protectcust14max=V219 if V217==2 & V218==1
gen protectcust15max=V228 if V226==2 & V227==1
gen protectcust16max=V237 if V235==2 & V236==1
gen protectcust17max=V246 if V244==2 & V245==1
gen protectcust18max=V255 if V253==2 & V254==1
gen protectcust19max=V264 if V262==2 & V263==1
gen protectcust20max=V273 if V271==2 & V272==1

gen adminseg1max=V102 if V100==3 & V101==1
gen adminseg2max=V111 if V109==3 & V110==1
gen adminseg3max=V120 if V118==3 & V119==1
gen adminseg4max=V129 if V127==3 & V128==1
gen adminseg5max=V138 if V136==3 & V137==1
gen adminseg6max=V147 if V145==3 & V146==1
gen adminseg7max=V156 if V154==3 & V155==1
gen adminseg8max=V165 if V163==3 & V164==1
gen adminseg9max=V174 if V172==3 & V173==1
gen adminseg10max=V183 if V181==3 & V182==1
gen adminseg11max=V192 if V190==3 & V191==1
gen adminseg12max=V201 if V199==3 & V200==1
gen adminseg13max=V210 if V208==3 & V209==1
gen adminseg14max=V219 if V217==3 & V218==1
gen adminseg15max=V228 if V226==3 & V227==1
gen adminseg16max=V237 if V235==3 & V236==1
gen adminseg17max=V246 if V244==3 & V245==1
gen adminseg18max=V255 if V253==3 & V254==1
gen adminseg19max=V264 if V262==3 & V263==1
gen adminseg20max=V273 if V271==3 & V272==1

gen disciplinaction1max=V102 if V100==4 & V101==1
gen disciplinaction2max=V111 if V109==4 & V110==1
gen disciplinaction3max=V120 if V118==4 & V119==1
gen disciplinaction4max=V129 if V127==4 & V128==1
gen disciplinaction5max=V138 if V136==4 & V137==1
gen disciplinaction6max=V147 if V145==4 & V146==1
gen disciplinaction7max=V156 if V154==4 & V155==1
gen disciplinaction8max=V165 if V163==4 & V164==1
gen disciplinaction9max=V174 if V172==4 & V173==1
gen disciplinaction10max=V183 if V181==4 & V182==1
gen disciplinaction11max=V192 if V190==4 & V191==1
gen disciplinaction12max=V201 if V199==4 & V200==1
gen disciplinaction13max=V210 if V208==4 & V209==1
gen disciplinaction14max=V219 if V217==4 & V218==1
gen disciplinaction15max=V228 if V226==4 & V227==1
gen disciplinaction16max=V237 if V235==4 & V236==1
gen disciplinaction17max=V246 if V244==4 & V245==1
gen disciplinaction18max=V255 if V253==4 & V254==1
gen disciplinaction19max=V264 if V262==4 & V263==1
gen disciplinaction20max=V273 if V271==4 & V272==1

gen sickorinjured1max=V102 if V100==5 & V101==1
gen sickorinjured2max=V111 if V109==5 & V110==1
gen sickorinjured3max=V120 if V118==5 & V119==1
gen sickorinjured4max=V129 if V127==5 & V128==1
gen sickorinjured5max=V138 if V136==5 & V137==1
gen sickorinjured6max=V147 if V145==5 & V146==1
gen sickorinjured7max=V156 if V154==5 & V155==1
gen sickorinjured8max=V165 if V163==5 & V164==1
gen sickorinjured9max=V174 if V172==5 & V173==1
gen sickorinjured10max=V183 if V181==5 & V182==1
gen sickorinjured11max=V192 if V190==5 & V191==1
gen sickorinjured12max=V201 if V199==5 & V200==1
gen sickorinjured13max=V210 if V208==5 & V209==1
gen sickorinjured14max=V219 if V217==5 & V218==1
gen sickorinjured15max=V228 if V226==5 & V227==1
gen sickorinjured16max=V237 if V235==5 & V236==1
gen sickorinjured17max=V246 if V244==5 & V245==1
gen sickorinjured18max=V255 if V253==5 & V254==1
gen sickorinjured19max=V264 if V262==5 & V263==1
gen sickorinjured20max=V273 if V271==5 & V272==1

gen other1_1max=V102 if V100==6 & V101==1
gen other1_2max=V111 if V109==6 & V110==1
gen other1_3max=V120 if V118==6 & V119==1
gen other1_4max=V129 if V127==6 & V128==1
gen other1_5max=V138 if V136==6 & V137==1
gen other1_6max=V147 if V145==6 & V146==1
gen other1_7max=V156 if V154==6 & V155==1
gen other1_8max=V165 if V163==6 & V164==1
gen other1_9max=V174 if V172==6 & V173==1
gen other1_10max=V183 if V181==6 & V182==1
gen other1_11max=V192 if V190==6 & V191==1
gen other1_12max=V201 if V199==6 & V200==1
gen other1_13max=V210 if V208==6 & V209==1
gen other1_14max=V219 if V217==6 & V218==1
gen other1_15max=V228 if V226==6 & V227==1
gen other1_16max=V237 if V235==6 & V236==1
gen other1_17max=V246 if V244==6 & V245==1
gen other1_18max=V255 if V253==6 & V254==1
gen other1_19max=V264 if V262==6 & V263==1
gen other1_20max=V273 if V271==6 & V272==1

gen other2_1max=V102 if V100==7 & V101==1
gen other2_2max=V111 if V109==7 & V110==1
gen other2_3max=V120 if V118==7 & V119==1
gen other2_4max=V129 if V127==7 & V128==1
gen other2_5max=V138 if V136==7 & V137==1
gen other2_6max=V147 if V145==7 & V146==1
gen other2_7max=V156 if V154==7 & V155==1
gen other2_8max=V165 if V163==7 & V164==1
gen other2_9max=V174 if V172==7 & V173==1
gen other2_10max=V183 if V181==7 & V182==1
gen other2_11max=V192 if V190==7 & V191==1
gen other2_12max=V201 if V199==7 & V200==1
gen other2_13max=V210 if V208==7 & V209==1
gen other2_14max=V219 if V217==7 & V218==1
gen other2_15max=V228 if V226==7 & V227==1
gen other2_16max=V237 if V235==7 & V236==1
gen other2_17max=V246 if V244==7 & V245==1
gen other2_18max=V255 if V253==7 & V254==1
gen other2_19max=V264 if V262==7 & V263==1
gen other2_20max=V273 if V271==7 & V272==1

gen other3_1max=V102 if V100==8 & V101==1
gen other3_2max=V111 if V109==8 & V110==1
gen other3_3max=V120 if V118==8 & V119==1
gen other3_4max=V129 if V127==8 & V128==1
gen other3_5max=V138 if V136==8 & V137==1
gen other3_6max=V147 if V145==8 & V146==1
gen other3_7max=V156 if V154==8 & V155==1
gen other3_8max=V165 if V163==8 & V164==1
gen other3_9max=V174 if V172==8 & V173==1
gen other3_10max=V183 if V181==8 & V182==1
gen other3_11max=V192 if V190==8 & V191==1
gen other3_12max=V201 if V199==8 & V200==1
gen other3_13max=V210 if V208==8 & V209==1
gen other3_14max=V219 if V217==8 & V218==1
gen other3_15max=V228 if V226==8 & V227==1
gen other3_16max=V237 if V235==8 & V236==1
gen other3_17max=V246 if V244==8 & V245==1
gen other3_18max=V255 if V253==8 & V254==1
gen other3_19max=V264 if V262==8 & V263==1
gen other3_20max=V273 if V271==8 & V272==1

egen protectcustmax_tot=rowtotal(protectcust1max protectcust2max protectcust3max protectcust4max ///
	protectcust5max protectcust6max protectcust7max protectcust8max protectcust9max ///
	protectcust10max protectcust11max protectcust12max protectcust13max ///
	protectcust14max protectcust15max protectcust16max protectcust17max ///
	protectcust18max protectcust19max protectcust20max)
	
egen adminsegmax_tot=rowtotal(adminseg1max adminseg2max adminseg3max adminseg4max ///
	adminseg5max adminseg6max adminseg7max adminseg8max adminseg9max ///
	adminseg10max adminseg11max adminseg12max adminseg13max ///
	adminseg14max adminseg15max adminseg16max adminseg17max ///
	adminseg18max adminseg19max adminseg20max)
	
egen disciplinactionmax_tot=rowtotal(disciplinaction1max disciplinaction2max disciplinaction3max disciplinaction4max ///
	disciplinaction5max disciplinaction6max disciplinaction7max disciplinaction8max disciplinaction9max ///
	disciplinaction10max disciplinaction11max disciplinaction12max disciplinaction13max ///
	disciplinaction14max disciplinaction15max disciplinaction16max disciplinaction17max ///
	disciplinaction18max disciplinaction19max disciplinaction20max)
	
egen solitarycustmax_tot=rowtotal(adminsegmax_tot disciplinactionmax_tot)
	
egen sickorinjuredmax_tot=rowtotal(sickorinjured1max sickorinjured2max sickorinjured3max sickorinjured4max ///
	sickorinjured5max sickorinjured6max sickorinjured7max sickorinjured8max sickorinjured9max ///
	sickorinjured10max sickorinjured11max sickorinjured12max sickorinjured13max ///
	sickorinjured14max sickorinjured15max sickorinjured16max sickorinjured17max ///
	sickorinjured18max sickorinjured19max sickorinjured20max)
	
egen othermax_tot=rowtotal(other1_1max other1_2max other1_3max other1_4max ///
	other1_5max other1_6max other1_7max other1_8max other1_9max ///
	other1_10max other1_11max other1_12max other1_13max ///
	other1_14max other1_15max other1_16max other1_17max ///
	other1_18max other1_19max other1_20max ///
	other2_1max other2_2max other2_3max other2_4max ///
	other2_5max other2_6max other2_7max other2_8max other2_9max ///
	other2_10max other2_11max other2_12max other2_13max ///
	other2_14max other2_15max other2_16max other2_17max ///
	other2_18max other2_19max other2_20max ///
	other3_1max other3_2max other3_3max other3_4max ///
	other3_5max other3_6max other3_7max other3_8max other3_9max ///
	other3_10max other3_11max other3_12max other3_13max ///
	other3_14max other3_15max other3_16max other3_17max ///
	other3_18max other3_19max other3_20max)

gen all1max=V102 if V101==1
gen all2max=V111 if V110==1
gen all3max=V120 if V119==1
gen all4max=V129 if V128==1
gen all5max=V138 if V137==1
gen all6max=V147 if V146==1
gen all7max=V156 if V155==1
gen all8max=V165 if V164==1
gen all9max=V174 if V173==1
gen all10max=V183 if V182==1
gen all11max=V192 if V191==1
gen all12max=V201 if V200==1
gen all13max=V210 if V209==1
gen all14max=V219 if V218==1
gen all15max=V228 if V227==1
gen all16max=V237 if V236==1
gen all17max=V246 if V245==1
gen all18max=V255 if V254==1
gen all19max=V264 if V263==1
gen all20max=V273 if V272==1

egen allmax_tot =rowtotal( all1max all2max all3max all4max all5max all6max ///
	all7max all8max all9max all10max all11max all12max all13max ///
	all14max all15max all16max all17max all18max all19max all20max)

egen restricted_tot=rowtotal(protectcust_tot adminseg_tot disciplinaction_tot ///
	sickorinjured_tot other_tot)
	
egen restrictedmax_tot=rowtotal(protectcustmax_tot adminsegmax_tot disciplinactionmax_tot ///
	sickorinjuredmax_tot othermax_tot)

gen malepop=V70
gen femalepop=V71
egen totalpop=rowtotal(malepop femalepop)

cd "$project\Data"
save "1979 Prison Census Data.dta", replace

clear
cd "$project\Data"
use "1979 Prison Census Data.dta", clear

cd "$project"
putexcel set "Restricted Housing in Prisons by Jurisdiction 1979-2017.xlsx", sheet("1979") modify

local row=2
forvalues i=1/51 {
	fre statename if statenum==`i'
	putexcel A`row'=`r(lab_valid)'
	sum totalpop if statenum==`i'
	putexcel B`row'=`r(sum)'
	sum malepop if statenum==`i'
	putexcel C`row'=`r(sum)'
	sum femalepop if statenum==`i'
	putexcel D`row'=`r(sum)'
	sum protectcust_tot if statenum==`i'
	putexcel E`row'=`r(sum)'
	sum adminseg_tot if statenum==`i'
	putexcel F`row'=`r(sum)'
	sum disciplinaction_tot if statenum==`i'
	putexcel G`row'=`r(sum)'
	sum solitarycust_tot if statenum==`i'
	putexcel H`row'=`r(sum)'
	sum sickorinjured_tot if statenum==`i'
	putexcel I`row'=`r(sum)'
	sum other_tot if statenum==`i'
	putexcel J`row'=`r(sum)'
	sum restricted_tot if statenum==`i'
	putexcel K`row'=`r(sum)'
	sum protectcustmax_tot if statenum==`i'
	putexcel L`row'=`r(sum)'
	sum adminsegmax_tot if statenum==`i'
	putexcel M`row'=`r(sum)'
	sum disciplinactionmax_tot if statenum==`i'
	putexcel N`row'=`r(sum)'
	sum solitarycustmax_tot if statenum==`i'
	putexcel O`row'=`r(sum)'
	sum sickorinjuredmax_tot if statenum==`i'
	putexcel P`row'=`r(sum)'
	sum othermax_tot if statenum==`i'
	putexcel Q`row'=`r(sum)'
	sum restrictedmax_tot if statenum==`i'
	putexcel R`row'=`r(sum)'
	sum allmax_tot if statenum==`i'
	putexcel S`row'=`r(sum)'
	local ++row
	}

****************************************************************************
clear
cd "$project\Data\Raw Data\Census of State and Federal Adult Correctional Facilities\1984"
use "08444-0001-Data.dta"

rename V6 statenum

cd "$project\Data"
save "1984 Prison Census Data.dta", replace

cd "$project\Data\Raw Data"
import excel "List of States.xlsx", firstrow clear
cd "$project\Data"
save "List of States.dta", replace

cd "$project\Data"
clear
use "List of States.dta", clear
tempfile Listofstates
save `Listofstates'
use "1984 Prison Census Data.dta"
merge m:1 statenum using `Listofstates'

order statename, after(statenum)

gen protectcust1=V100 if V98==2
gen protectcust2=V103 if V105==2
gen protectcust3=V110 if V112==2
gen protectcust4=V117 if V119==2
gen protectcust5=V124 if V126==2
gen protectcust6=V131 if V133==2
gen protectcust7=V138 if V140==2
gen protectcust8=V145 if V147==2
gen protectcust9=V152 if V154==2
gen protectcust10=V159 if V161==2
gen protectcust11=V166 if V168==2
gen protectcust12=V173 if V175==2
gen protectcust13=V180 if V182==2
gen protectcust14=V187 if V189==2
gen protectcust15=V194 if V196==2
gen protectcust16=V201 if V203==2
gen protectcust17=V208 if V210==2
gen protectcust18=V215 if V217==2
gen protectcust19=V222 if V224==2
gen protectcust20=V229 if V231==2

gen adminseg1=V100 if V98==3
gen adminseg2=V103 if V105==3
gen adminseg3=V110 if V112==3
gen adminseg4=V117 if V119==3
gen adminseg5=V124 if V126==3
gen adminseg6=V131 if V133==3
gen adminseg7=V138 if V140==3
gen adminseg8=V145 if V147==3
gen adminseg9=V152 if V154==3
gen adminseg10=V159 if V161==3
gen adminseg11=V166 if V168==3
gen adminseg12=V173 if V175==3
gen adminseg13=V180 if V182==3
gen adminseg14=V187 if V189==3
gen adminseg15=V194 if V196==3
gen adminseg16=V201 if V203==3
gen adminseg17=V208 if V210==3
gen adminseg18=V215 if V217==3
gen adminseg19=V222 if V224==3
gen adminseg20=V229 if V231==3

gen disciplinaction1=V100 if V98==4
gen disciplinaction2=V103 if V105==4
gen disciplinaction3=V110 if V112==4
gen disciplinaction4=V117 if V119==4
gen disciplinaction5=V124 if V126==4
gen disciplinaction6=V131 if V133==4
gen disciplinaction7=V138 if V140==4
gen disciplinaction8=V145 if V147==4
gen disciplinaction9=V152 if V154==4
gen disciplinaction10=V159 if V161==4
gen disciplinaction11=V166 if V168==4
gen disciplinaction12=V173 if V175==4
gen disciplinaction13=V180 if V182==4
gen disciplinaction14=V187 if V189==4
gen disciplinaction15=V194 if V196==4
gen disciplinaction16=V201 if V203==4
gen disciplinaction17=V208 if V210==4
gen disciplinaction18=V215 if V217==4
gen disciplinaction19=V222 if V224==4
gen disciplinaction20=V229 if V231==4

gen sickorinjured1=V100 if V98==5
gen sickorinjured2=V103 if V105==5
gen sickorinjured3=V110 if V112==5
gen sickorinjured4=V117 if V119==5
gen sickorinjured5=V124 if V126==5
gen sickorinjured6=V131 if V133==5
gen sickorinjured7=V138 if V140==5
gen sickorinjured8=V145 if V147==5
gen sickorinjured9=V152 if V154==5
gen sickorinjured10=V159 if V161==5
gen sickorinjured11=V166 if V168==5
gen sickorinjured12=V173 if V175==5
gen sickorinjured13=V180 if V182==5
gen sickorinjured14=V187 if V189==5
gen sickorinjured15=V194 if V196==5
gen sickorinjured16=V201 if V203==5
gen sickorinjured17=V208 if V210==5
gen sickorinjured18=V215 if V217==5
gen sickorinjured19=V222 if V224==5
gen sickorinjured20=V229 if V231==5

gen other1_1=V100 if V98==6
gen other1_2=V103 if V105==6
gen other1_3=V110 if V112==6
gen other1_4=V117 if V119==6
gen other1_5=V124 if V126==6
gen other1_6=V131 if V133==6
gen other1_7=V138 if V140==6
gen other1_8=V145 if V147==6
gen other1_9=V152 if V154==6
gen other1_10=V159 if V161==6
gen other1_11=V166 if V168==6
gen other1_12=V173 if V175==6
gen other1_13=V180 if V182==6
gen other1_14=V187 if V189==6
gen other1_15=V194 if V196==6
gen other1_16=V201 if V203==6
gen other1_17=V208 if V210==6
gen other1_18=V215 if V217==6
gen other1_19=V222 if V224==6
gen other1_20=V229 if V231==6

gen other2_1=V100 if V98==7
gen other2_2=V103 if V105==7
gen other2_3=V110 if V112==7
gen other2_4=V117 if V119==7
gen other2_5=V124 if V126==7
gen other2_6=V131 if V133==7
gen other2_7=V138 if V140==7
gen other2_8=V145 if V147==7
gen other2_9=V152 if V154==7
gen other2_10=V159 if V161==7
gen other2_11=V166 if V168==7
gen other2_12=V173 if V175==7
gen other2_13=V180 if V182==7
gen other2_14=V187 if V189==7
gen other2_15=V194 if V196==7
gen other2_16=V201 if V203==7
gen other2_17=V208 if V210==7
gen other2_18=V215 if V217==7
gen other2_19=V222 if V224==7
gen other2_20=V229 if V231==7

gen other3_1=V100 if V98==8
gen other3_2=V103 if V105==8
gen other3_3=V110 if V112==8
gen other3_4=V117 if V119==8
gen other3_5=V124 if V126==8
gen other3_6=V131 if V133==8
gen other3_7=V138 if V140==8
gen other3_8=V145 if V147==8
gen other3_9=V152 if V154==8
gen other3_10=V159 if V161==8
gen other3_11=V166 if V168==8
gen other3_12=V173 if V175==8
gen other3_13=V180 if V182==8
gen other3_14=V187 if V189==8
gen other3_15=V194 if V196==8
gen other3_16=V201 if V203==8
gen other3_17=V208 if V210==8
gen other3_18=V215 if V217==8
gen other3_19=V222 if V224==8
gen other3_20=V229 if V231==8

egen protectcust_tot=rowtotal(protectcust1 protectcust2 protectcust3 protectcust4 ///
	protectcust5 protectcust6 protectcust7 protectcust8 protectcust9 ///
	protectcust10 protectcust11 protectcust12 protectcust13 ///
	protectcust14 protectcust15 protectcust16 protectcust17 ///
	protectcust18 protectcust19 protectcust20)
	
egen adminseg_tot=rowtotal(adminseg1 adminseg2 adminseg3 adminseg4 ///
	adminseg5 adminseg6 adminseg7 adminseg8 adminseg9 ///
	adminseg10 adminseg11 adminseg12 adminseg13 ///
	adminseg14 adminseg15 adminseg16 adminseg17 ///
	adminseg18 adminseg19 adminseg20)
	
egen disciplinaction_tot=rowtotal(disciplinaction1 disciplinaction2 disciplinaction3 disciplinaction4 ///
	disciplinaction5 disciplinaction6 disciplinaction7 disciplinaction8 disciplinaction9 ///
	disciplinaction10 disciplinaction11 disciplinaction12 disciplinaction13 ///
	disciplinaction14 disciplinaction15 disciplinaction16 disciplinaction17 ///
	disciplinaction18 disciplinaction19 disciplinaction20)
	
egen solitarycust_tot=rowtotal(adminseg_tot disciplinaction_tot)
	
egen sickorinjured_tot=rowtotal(sickorinjured1 sickorinjured2 sickorinjured3 sickorinjured4 ///
	sickorinjured5 sickorinjured6 sickorinjured7 sickorinjured8 sickorinjured9 ///
	sickorinjured10 sickorinjured11 sickorinjured12 sickorinjured13 ///
	sickorinjured14 sickorinjured15 sickorinjured16 sickorinjured17 ///
	sickorinjured18 sickorinjured19 sickorinjured20)
	
egen other_tot=rowtotal(other1_1 other1_2 other1_3 other1_4 ///
	other1_5 other1_6 other1_7 other1_8 other1_9 ///
	other1_10 other1_11 other1_12 other1_13 ///
	other1_14 other1_15 other1_16 other1_17 ///
	other1_18 other1_19 other1_20 ///
	other2_1 other2_2 other2_3 other2_4 ///
	other2_5 other2_6 other2_7 other2_8 other2_9 ///
	other2_10 other2_11 other2_12 other2_13 ///
	other2_14 other2_15 other2_16 other2_17 ///
	other2_18 other2_19 other2_20 ///
	other3_1 other3_2 other3_3 other3_4 ///
	other3_5 other3_6 other3_7 other3_8 other3_9 ///
	other3_10 other3_11 other3_12 other3_13 ///
	other3_14 other3_15 other3_16 other3_17 ///
	other3_18 other3_19 other3_20)
	
gen protectcust1max=V100 if V98==2 & V99==1
gen protectcust2max=V103 if V105==2 & V106==1
gen protectcust3max=V110 if V112==2 & V113==1
gen protectcust4max=V117 if V119==2 & V120==1
gen protectcust5max=V124 if V126==2 & V127==1
gen protectcust6max=V131 if V133==2 & V134==1
gen protectcust7max=V138 if V140==2 & V141==1
gen protectcust8max=V145 if V147==2 & V148==1
gen protectcust9max=V152 if V154==2 & V155==1
gen protectcust10max=V159 if V161==2 & V162==1
gen protectcust11max=V166 if V168==2 & V169==1
gen protectcust12max=V173 if V175==2 & V176==1
gen protectcust13max=V180 if V182==2 & V183==1
gen protectcust14max=V187 if V189==2 & V190==1
gen protectcust15max=V194 if V196==2 & V197==1
gen protectcust16max=V201 if V203==2 & V204==1
gen protectcust17max=V208 if V210==2 & V211==1
gen protectcust18max=V215 if V217==2 & V218==1
gen protectcust19max=V222 if V224==2 & V225==1
gen protectcust20max=V229 if V231==2 & V232==1

gen adminseg1max=V100 if V98==3 & V99==1
gen adminseg2max=V103 if V105==3 & V106==1
gen adminseg3max=V110 if V112==3 & V113==1
gen adminseg4max=V117 if V119==3 & V120==1
gen adminseg5max=V124 if V126==3 & V127==1
gen adminseg6max=V131 if V133==3 & V134==1
gen adminseg7max=V138 if V140==3 & V141==1
gen adminseg8max=V145 if V147==3 & V148==1
gen adminseg9max=V152 if V154==3 & V155==1
gen adminseg10max=V159 if V161==3 & V162==1
gen adminseg11max=V166 if V168==3 & V169==1
gen adminseg12max=V173 if V175==3 & V176==1
gen adminseg13max=V180 if V182==3 & V183==1
gen adminseg14max=V187 if V189==3 & V190==1
gen adminseg15max=V194 if V196==3 & V197==1
gen adminseg16max=V201 if V203==3 & V204==1
gen adminseg17max=V208 if V210==3 & V211==1
gen adminseg18max=V215 if V217==3 & V218==1
gen adminseg19max=V222 if V224==3 & V225==1
gen adminseg20max=V229 if V231==3 & V232==1

gen disciplinaction1max=V100 if V98==4 & V99==1
gen disciplinaction2max=V103 if V105==4 & V106==1
gen disciplinaction3max=V110 if V112==4 & V113==1
gen disciplinaction4max=V117 if V119==4 & V120==1
gen disciplinaction5max=V124 if V126==4 & V127==1
gen disciplinaction6max=V131 if V133==4 & V134==1
gen disciplinaction7max=V138 if V140==4 & V141==1
gen disciplinaction8max=V145 if V147==4 & V148==1
gen disciplinaction9max=V152 if V154==4 & V155==1
gen disciplinaction10max=V159 if V161==4 & V162==1
gen disciplinaction11max=V166 if V168==4 & V169==1
gen disciplinaction12max=V173 if V175==4 & V176==1
gen disciplinaction13max=V180 if V182==4 & V183==1
gen disciplinaction14max=V187 if V189==4 & V190==1
gen disciplinaction15max=V194 if V196==4 & V197==1
gen disciplinaction16max=V201 if V203==4 & V204==1
gen disciplinaction17max=V208 if V210==4 & V211==1
gen disciplinaction18max=V215 if V217==4 & V218==1
gen disciplinaction19max=V222 if V224==4 & V225==1
gen disciplinaction20max=V229 if V231==4 & V232==1

gen sickorinjured1max=V100 if V98==5 & V99==1
gen sickorinjured2max=V103 if V105==5 & V106==1
gen sickorinjured3max=V110 if V112==5 & V113==1
gen sickorinjured4max=V117 if V119==5 & V120==1
gen sickorinjured5max=V124 if V126==5 & V127==1
gen sickorinjured6max=V131 if V133==5 & V134==1
gen sickorinjured7max=V138 if V140==5 & V141==1
gen sickorinjured8max=V145 if V147==5 & V148==1
gen sickorinjured9max=V152 if V154==5 & V155==1
gen sickorinjured10max=V159 if V161==5 & V162==1
gen sickorinjured11max=V166 if V168==5 & V169==1
gen sickorinjured12max=V173 if V175==5 & V176==1
gen sickorinjured13max=V180 if V182==5 & V183==1
gen sickorinjured14max=V187 if V189==5 & V190==1
gen sickorinjured15max=V194 if V196==5 & V197==1
gen sickorinjured16max=V201 if V203==5 & V204==1
gen sickorinjured17max=V208 if V210==5 & V211==1
gen sickorinjured18max=V215 if V217==5 & V218==1
gen sickorinjured19max=V222 if V224==5 & V225==1
gen sickorinjured20max=V229 if V231==5 & V232==1

gen other1_1max=V100 if V98==6 & V99==1
gen other1_2max=V103 if V105==6 & V106==1
gen other1_3max=V110 if V112==6 & V113==1
gen other1_4max=V117 if V119==6 & V120==1
gen other1_5max=V124 if V126==6 & V127==1
gen other1_6max=V131 if V133==6 & V134==1
gen other1_7max=V138 if V140==6 & V141==1
gen other1_8max=V145 if V147==6 & V148==1
gen other1_9max=V152 if V154==6 & V155==1
gen other1_10max=V159 if V161==6 & V162==1
gen other1_11max=V166 if V168==6 & V169==1
gen other1_12max=V173 if V175==6 & V176==1
gen other1_13max=V180 if V182==6 & V183==1
gen other1_14max=V187 if V189==6 & V190==1
gen other1_15max=V194 if V196==6 & V197==1
gen other1_16max=V201 if V203==6 & V204==1
gen other1_17max=V208 if V210==6 & V211==1
gen other1_18max=V215 if V217==6 & V218==1
gen other1_19max=V222 if V224==6 & V225==1
gen other1_20max=V229 if V231==6 & V232==1

gen other2_1max=V100 if V98==7 & V99==1
gen other2_2max=V103 if V105==7 & V106==1
gen other2_3max=V110 if V112==7 & V113==1
gen other2_4max=V117 if V119==7 & V120==1
gen other2_5max=V124 if V126==7 & V127==1
gen other2_6max=V131 if V133==7 & V134==1
gen other2_7max=V138 if V140==7 & V141==1
gen other2_8max=V145 if V147==7 & V148==1
gen other2_9max=V152 if V154==7 & V155==1
gen other2_10max=V159 if V161==7 & V162==1
gen other2_11max=V166 if V168==7 & V169==1
gen other2_12max=V173 if V175==7 & V176==1
gen other2_13max=V180 if V182==7 & V183==1
gen other2_14max=V187 if V189==7 & V190==1
gen other2_15max=V194 if V196==7 & V197==1
gen other2_16max=V201 if V203==7 & V204==1
gen other2_17max=V208 if V210==7 & V211==1
gen other2_18max=V215 if V217==7 & V218==1
gen other2_19max=V222 if V224==7 & V225==1
gen other2_20max=V229 if V231==7 & V232==1

gen other3_1max=V100 if V98==8 & V99==1
gen other3_2max=V103 if V105==8 & V106==1
gen other3_3max=V110 if V112==8 & V113==1
gen other3_4max=V117 if V119==8 & V120==1
gen other3_5max=V124 if V126==8 & V127==1
gen other3_6max=V131 if V133==8 & V134==1
gen other3_7max=V138 if V140==8 & V141==1
gen other3_8max=V145 if V147==8 & V148==1
gen other3_9max=V152 if V154==8 & V155==1
gen other3_10max=V159 if V161==8 & V162==1
gen other3_11max=V166 if V168==8 & V169==1
gen other3_12max=V173 if V175==8 & V176==1
gen other3_13max=V180 if V182==8 & V183==1
gen other3_14max=V187 if V189==8 & V190==1
gen other3_15max=V194 if V196==8 & V197==1
gen other3_16max=V201 if V203==8 & V204==1
gen other3_17max=V208 if V210==8 & V211==1
gen other3_18max=V215 if V217==8 & V218==1
gen other3_19max=V222 if V224==8 & V225==1
gen other3_20max=V229 if V231==8 & V232==1

egen protectcustmax_tot=rowtotal(protectcust1max protectcust2max protectcust3max protectcust4max ///
	protectcust5max protectcust6max protectcust7max protectcust8max protectcust9max ///
	protectcust10max protectcust11max protectcust12max protectcust13max ///
	protectcust14max protectcust15max protectcust16max protectcust17max ///
	protectcust18max protectcust19max protectcust20max)
	
egen adminsegmax_tot=rowtotal(adminseg1max adminseg2max adminseg3max adminseg4max ///
	adminseg5max adminseg6max adminseg7max adminseg8max adminseg9max ///
	adminseg10max adminseg11max adminseg12max adminseg13max ///
	adminseg14max adminseg15max adminseg16max adminseg17max ///
	adminseg18max adminseg19max adminseg20max)
	
egen disciplinactionmax_tot=rowtotal(disciplinaction1max disciplinaction2max disciplinaction3max disciplinaction4max ///
	disciplinaction5max disciplinaction6max disciplinaction7max disciplinaction8max disciplinaction9max ///
	disciplinaction10max disciplinaction11max disciplinaction12max disciplinaction13max ///
	disciplinaction14max disciplinaction15max disciplinaction16max disciplinaction17max ///
	disciplinaction18max disciplinaction19max disciplinaction20max)
	
egen solitarycustmax_tot=rowtotal(adminsegmax_tot disciplinactionmax_tot)	
	
egen sickorinjuredmax_tot=rowtotal(sickorinjured1max sickorinjured2max sickorinjured3max sickorinjured4max ///
	sickorinjured5max sickorinjured6max sickorinjured7max sickorinjured8max sickorinjured9max ///
	sickorinjured10max sickorinjured11max sickorinjured12max sickorinjured13max ///
	sickorinjured14max sickorinjured15max sickorinjured16max sickorinjured17max ///
	sickorinjured18max sickorinjured19max sickorinjured20max)
	
egen othermax_tot=rowtotal(other1_1max other1_2max other1_3max other1_4max ///
	other1_5max other1_6max other1_7max other1_8max other1_9max ///
	other1_10max other1_11max other1_12max other1_13max ///
	other1_14max other1_15max other1_16max other1_17max ///
	other1_18max other1_19max other1_20max ///
	other2_1max other2_2max other2_3max other2_4max ///
	other2_5max other2_6max other2_7max other2_8max other2_9max ///
	other2_10max other2_11max other2_12max other2_13max ///
	other2_14max other2_15max other2_16max other2_17max ///
	other2_18max other2_19max other2_20max ///
	other3_1max other3_2max other3_3max other3_4max ///
	other3_5max other3_6max other3_7max other3_8max other3_9max ///
	other3_10max other3_11max other3_12max other3_13max ///
	other3_14max other3_15max other3_16max other3_17max ///
	other3_18max other3_19max other3_20max)

gen all1max=V100 if V99==1
gen all2max=V103 if V106==1
gen all3max=V110 if V113==1
gen all4max=V117 if V120==1
gen all5max=V124 if V127==1
gen all6max=V131 if V134==1
gen all7max=V138 if V141==1
gen all8max=V145 if V148==1
gen all9max=V152 if V155==1
gen all10max=V159 if V162==1
gen all11max=V166 if V169==1
gen all12max=V173 if V176==1
gen all13max=V180 if V183==1
gen all14max=V187 if V190==1
gen all15max=V194 if V197==1
gen all16max=V201 if V204==1
gen all17max=V208 if V211==1
gen all18max=V215 if V218==1
gen all19max=V222 if V225==1
gen all20max=V229 if V232==1	
	
egen allmax_tot =rowtotal( all1max all2max all3max all4max all5max all6max ///
	all7max all8max all9max all10max all11max all12max all13max ///
	all14max all15max all16max all17max all18max all19max all20max)

egen restricted_tot=rowtotal(protectcust_tot adminseg_tot disciplinaction_tot ///
	sickorinjured_tot other_tot)
	
egen restrictedmax_tot=rowtotal(protectcustmax_tot adminsegmax_tot disciplinactionmax_tot ///
	sickorinjuredmax_tot othermax_tot)

gen malepop=V46
gen femalepop=V47
egen totalpop=rowtotal(malepop femalepop)

cd "$project\Data"
save "1984 Prison Census Data.dta", replace

clear
cd "$project\Data"
use "1984 Prison Census Data.dta", clear

cd "$project"
putexcel set "Restricted Housing in Prisons by Jurisdiction 1979-2017.xlsx", sheet("1984") modify

local row=2
forvalues i=1/51 {
	fre statename if statenum==`i'
	putexcel A`row'=`r(lab_valid)'
	sum totalpop if statenum==`i'
	putexcel B`row'=`r(sum)'
	sum malepop if statenum==`i'
	putexcel C`row'=`r(sum)'
	sum femalepop if statenum==`i'
	putexcel D`row'=`r(sum)'
	sum protectcust_tot if statenum==`i'
	putexcel E`row'=`r(sum)'
	sum adminseg_tot if statenum==`i'
	putexcel F`row'=`r(sum)'
	sum disciplinaction_tot if statenum==`i'
	putexcel G`row'=`r(sum)'
	sum solitarycust_tot if statenum==`i'
	putexcel H`row'=`r(sum)'
	sum sickorinjured_tot if statenum==`i'
	putexcel I`row'=`r(sum)'
	sum other_tot if statenum==`i'
	putexcel J`row'=`r(sum)'
	sum restricted_tot if statenum==`i'
	putexcel K`row'=`r(sum)'
	sum protectcustmax_tot if statenum==`i'
	putexcel L`row'=`r(sum)'
	sum adminsegmax_tot if statenum==`i'
	putexcel M`row'=`r(sum)'
	sum disciplinactionmax_tot if statenum==`i'
	putexcel N`row'=`r(sum)'
	sum solitarycustmax_tot if statenum==`i'
	putexcel O`row'=`r(sum)'
	sum sickorinjuredmax_tot if statenum==`i'
	putexcel P`row'=`r(sum)'
	sum othermax_tot if statenum==`i'
	putexcel Q`row'=`r(sum)'
	sum restrictedmax_tot if statenum==`i'
	putexcel R`row'=`r(sum)'
	sum allmax_tot if statenum==`i'
	putexcel S`row'=`r(sum)'
	local ++row
	}

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

/* 	Note: 1990 data must be reformatted from a fixed format text file before it 
	can be used in Stata. Please see do file named 
	"02 Reformatting 1990 Prison Data.do" if you wish to replicate this exercise 
	using the raw text file from the ICPSR website.
*/

clear
cd "$project\Data\Raw Data\Census of State and Federal Adult Correctional Facilities\1990"
use "1990 Prison Census Data.dta"

gen statenum=substr(V6, 1,2)
order statenum, after(V6)
destring statenum, replace

gen factype=substr(V6,3,1)
order factype, after(statenum)
destring factype, replace
gen federal=factype==6
order federal, after(factype)

cd "$project\Data"
save "1990 Prison Census Data.dta", replace

cd "$project\Data\Raw Data"
import excel "List of States.xlsx", firstrow clear
cd "$project\Data"
save "List of States.dta", replace

cd "$project\Data"
clear
use "List of States.dta", clear
tempfile Listofstates
save `Listofstates'
use "1990 Prison Census Data.dta"
merge m:1 statenum using `Listofstates'

order statename, after(statenum)
replace statenum=52 if federal==1
replace statename="Federal Prisons" if statenum==52

destring _all, replace

gen deathrow1=V118 if V115==2
gen deathrow2=V128 if V125==2
gen deathrow3=V138 if V135==2
gen deathrow4=V148 if V145==2
gen deathrow5=V158 if V155==2
gen deathrow6=V168 if V165==2
gen deathrow7=V178 if V175==2
gen deathrow8=V188 if V185==2
gen deathrow9=V198 if V195==2
gen deathrow10=V208 if V205==2
gen deathrow11=V218 if V215==2
gen deathrow12=V228 if V225==2
gen deathrow13=V238 if V235==2
gen deathrow14=V248 if V245==2
gen deathrow15=V258 if V255==2
gen deathrow16=V268 if V265==2
gen deathrow17=V278 if V275==2
gen deathrow18=V288 if V285==2
gen deathrow19=V298 if V295==2
gen deathrow20=V308 if V305==2

gen protectcust1=V118 if V115==3
gen protectcust2=V128 if V125==3
gen protectcust3=V138 if V135==3
gen protectcust4=V148 if V145==3
gen protectcust5=V158 if V155==3
gen protectcust6=V168 if V165==3
gen protectcust7=V178 if V175==3
gen protectcust8=V188 if V185==3
gen protectcust9=V198 if V195==3
gen protectcust10=V208 if V205==3
gen protectcust11=V218 if V215==3
gen protectcust12=V228 if V225==3
gen protectcust13=V238 if V235==3
gen protectcust14=V248 if V245==3
gen protectcust15=V258 if V255==3
gen protectcust16=V268 if V265==3
gen protectcust17=V278 if V275==3
gen protectcust18=V288 if V285==3
gen protectcust19=V298 if V295==3
gen protectcust20=V308 if V305==3

gen adminseg1=V118 if V115==4
gen adminseg2=V128 if V125==4
gen adminseg3=V138 if V135==4
gen adminseg4=V148 if V145==4
gen adminseg5=V158 if V155==4
gen adminseg6=V168 if V165==4
gen adminseg7=V178 if V175==4
gen adminseg8=V188 if V185==4
gen adminseg9=V198 if V195==4
gen adminseg10=V208 if V205==4
gen adminseg11=V218 if V215==4
gen adminseg12=V228 if V225==4
gen adminseg13=V238 if V235==4
gen adminseg14=V248 if V245==4
gen adminseg15=V258 if V255==4
gen adminseg16=V268 if V265==4
gen adminseg17=V278 if V275==4
gen adminseg18=V288 if V285==4
gen adminseg19=V298 if V295==4
gen adminseg20=V308 if V305==4

gen disciplinaction1=V118 if V115==5
gen disciplinaction2=V128 if V125==5
gen disciplinaction3=V138 if V135==5
gen disciplinaction4=V148 if V145==5
gen disciplinaction5=V158 if V155==5
gen disciplinaction6=V168 if V165==5
gen disciplinaction7=V178 if V175==5
gen disciplinaction8=V188 if V185==5
gen disciplinaction9=V198 if V195==5
gen disciplinaction10=V208 if V205==5
gen disciplinaction11=V218 if V215==5
gen disciplinaction12=V228 if V225==5
gen disciplinaction13=V238 if V235==5
gen disciplinaction14=V248 if V245==5
gen disciplinaction15=V258 if V255==5
gen disciplinaction16=V268 if V265==5
gen disciplinaction17=V278 if V275==5
gen disciplinaction18=V288 if V285==5
gen disciplinaction19=V298 if V295==5
gen disciplinaction20=V308 if V305==5

gen sickorinjured1=V118 if V115==6
gen sickorinjured2=V128 if V125==6
gen sickorinjured3=V138 if V135==6
gen sickorinjured4=V148 if V145==6
gen sickorinjured5=V158 if V155==6
gen sickorinjured6=V168 if V165==6
gen sickorinjured7=V178 if V175==6
gen sickorinjured8=V188 if V185==6
gen sickorinjured9=V198 if V195==6
gen sickorinjured10=V208 if V205==6
gen sickorinjured11=V218 if V215==6
gen sickorinjured12=V228 if V225==6
gen sickorinjured13=V238 if V235==6
gen sickorinjured14=V248 if V245==6
gen sickorinjured15=V258 if V255==6
gen sickorinjured16=V268 if V265==6
gen sickorinjured17=V278 if V275==6
gen sickorinjured18=V288 if V285==6
gen sickorinjured19=V298 if V295==6
gen sickorinjured20=V308 if V305==6

gen other1=V118 if V115==11
gen other2=V128 if V125==11
gen other3=V138 if V135==11
gen other4=V148 if V145==11
gen other5=V158 if V155==11
gen other6=V168 if V165==11
gen other7=V178 if V175==11
gen other8=V188 if V185==11
gen other9=V198 if V195==11
gen other10=V208 if V205==11
gen other11=V218 if V215==11
gen other12=V228 if V225==11
gen other13=V238 if V235==11
gen other14=V248 if V245==11
gen other15=V258 if V255==11
gen other16=V268 if V265==11
gen other17=V278 if V275==11
gen other18=V288 if V285==11
gen other19=V298 if V295==11
gen other20=V308 if V305==11

egen deathrow_tot=rowtotal(deathrow1 deathrow2 deathrow3 deathrow4 ///
	deathrow5 deathrow6 deathrow7 deathrow8 deathrow9 ///
	deathrow10 deathrow11 deathrow12 deathrow13 ///
	deathrow14 deathrow15 deathrow16 deathrow17 ///
	deathrow18 deathrow19 deathrow20)

egen protectcust_tot=rowtotal(protectcust1 protectcust2 protectcust3 protectcust4 ///
	protectcust5 protectcust6 protectcust7 protectcust8 protectcust9 ///
	protectcust10 protectcust11 protectcust12 protectcust13 ///
	protectcust14 protectcust15 protectcust16 protectcust17 ///
	protectcust18 protectcust19 protectcust20)
	
egen adminseg_tot=rowtotal(adminseg1 adminseg2 adminseg3 adminseg4 ///
	adminseg5 adminseg6 adminseg7 adminseg8 adminseg9 ///
	adminseg10 adminseg11 adminseg12 adminseg13 ///
	adminseg14 adminseg15 adminseg16 adminseg17 ///
	adminseg18 adminseg19 adminseg20)
	
egen disciplinaction_tot=rowtotal(disciplinaction1 disciplinaction2 disciplinaction3 disciplinaction4 ///
	disciplinaction5 disciplinaction6 disciplinaction7 disciplinaction8 disciplinaction9 ///
	disciplinaction10 disciplinaction11 disciplinaction12 disciplinaction13 ///
	disciplinaction14 disciplinaction15 disciplinaction16 disciplinaction17 ///
	disciplinaction18 disciplinaction19 disciplinaction20)
	
egen solitarycust_tot=rowtotal(adminseg_tot disciplinaction_tot)

egen sickorinjured_tot=rowtotal(sickorinjured1 sickorinjured2 sickorinjured3 sickorinjured4 ///
	sickorinjured5 sickorinjured6 sickorinjured7 sickorinjured8 sickorinjured9 ///
	sickorinjured10 sickorinjured11 sickorinjured12 sickorinjured13 ///
	sickorinjured14 sickorinjured15 sickorinjured16 sickorinjured17 ///
	sickorinjured18 sickorinjured19 sickorinjured20)
	
egen other_tot=rowtotal(other1 other2 other3 other4 ///
	other5 other6 other7 other8 other9 ///
	other10 other11 other12 other13 ///
	other14 other15 other16 other17 ///
	other18 other19 other20 )

gen deathrow1max=V118 if V115==2 & V116==1
gen deathrow2max=V128 if V125==2 & V126==1
gen deathrow3max=V138 if V135==2 & V136==1
gen deathrow4max=V148 if V145==2 & V146==1
gen deathrow5max=V158 if V155==2 & V156==1
gen deathrow6max=V168 if V165==2 & V166==1
gen deathrow7max=V178 if V175==2 & V176==1
gen deathrow8max=V188 if V185==2 & V186==1
gen deathrow9max=V198 if V195==2 & V196==1
gen deathrow10max=V208 if V205==2 & V206==1
gen deathrow11max=V218 if V215==2 & V216==1
gen deathrow12max=V228 if V225==2 & V226==1
gen deathrow13max=V238 if V235==2 & V236==1
gen deathrow14max=V248 if V245==2 & V246==1
gen deathrow15max=V258 if V255==2 & V256==1
gen deathrow16max=V268 if V265==2 & V266==1
gen deathrow17max=V278 if V275==2 & V276==1
gen deathrow18max=V288 if V285==2 & V286==1
gen deathrow19max=V298 if V295==2 & V296==1
gen deathrow20max=V308 if V305==2 & V306==1	

gen protectcust1max=V118 if V115==3 & V116==1
gen protectcust2max=V128 if V125==3 & V126==1
gen protectcust3max=V138 if V135==3 & V136==1
gen protectcust4max=V148 if V145==3 & V146==1
gen protectcust5max=V158 if V155==3 & V156==1
gen protectcust6max=V168 if V165==3 & V166==1
gen protectcust7max=V178 if V175==3 & V176==1
gen protectcust8max=V188 if V185==3 & V186==1
gen protectcust9max=V198 if V195==3 & V196==1
gen protectcust10max=V208 if V205==3 & V206==1
gen protectcust11max=V218 if V215==3 & V216==1
gen protectcust12max=V228 if V225==3 & V226==1
gen protectcust13max=V238 if V235==3 & V236==1
gen protectcust14max=V248 if V245==3 & V246==1
gen protectcust15max=V258 if V255==3 & V256==1
gen protectcust16max=V268 if V265==3 & V266==1
gen protectcust17max=V278 if V275==3 & V276==1
gen protectcust18max=V288 if V285==3 & V286==1
gen protectcust19max=V298 if V295==3 & V296==1
gen protectcust20max=V308 if V305==3 & V306==1	
	
gen adminseg1max=V118 if V115==4 & V116==1
gen adminseg2max=V128 if V125==4 & V126==1
gen adminseg3max=V138 if V135==4 & V136==1
gen adminseg4max=V148 if V145==4 & V146==1
gen adminseg5max=V158 if V155==4 & V156==1
gen adminseg6max=V168 if V165==4 & V166==1
gen adminseg7max=V178 if V175==4 & V176==1
gen adminseg8max=V188 if V185==4 & V186==1
gen adminseg9max=V198 if V195==4 & V196==1
gen adminseg10max=V208 if V205==4 & V206==1
gen adminseg11max=V218 if V215==4 & V216==1
gen adminseg12max=V228 if V225==4 & V226==1
gen adminseg13max=V238 if V235==4 & V236==1
gen adminseg14max=V248 if V245==4 & V246==1
gen adminseg15max=V258 if V255==4 & V256==1
gen adminseg16max=V268 if V265==4 & V266==1
gen adminseg17max=V278 if V275==4 & V276==1
gen adminseg18max=V288 if V285==4 & V286==1
gen adminseg19max=V298 if V295==4 & V296==1
gen adminseg20max=V308 if V305==4 & V306==1	

gen disciplinaction1max=V118 if V115==5 & V116==1
gen disciplinaction2max=V128 if V125==5 & V126==1
gen disciplinaction3max=V138 if V135==5 & V136==1
gen disciplinaction4max=V148 if V145==5 & V146==1
gen disciplinaction5max=V158 if V155==5 & V156==1
gen disciplinaction6max=V168 if V165==5 & V166==1
gen disciplinaction7max=V178 if V175==5 & V176==1
gen disciplinaction8max=V188 if V185==5 & V186==1
gen disciplinaction9max=V198 if V195==5 & V196==1
gen disciplinaction10max=V208 if V205==5 & V206==1
gen disciplinaction11max=V218 if V215==5 & V216==1
gen disciplinaction12max=V228 if V225==5 & V226==1
gen disciplinaction13max=V238 if V235==5 & V236==1
gen disciplinaction14max=V248 if V245==5 & V246==1
gen disciplinaction15max=V258 if V255==5 & V256==1
gen disciplinaction16max=V268 if V265==5 & V266==1
gen disciplinaction17max=V278 if V275==5 & V276==1
gen disciplinaction18max=V288 if V285==5 & V286==1
gen disciplinaction19max=V298 if V295==5 & V296==1
gen disciplinaction20max=V308 if V305==5 & V306==1	

gen sickorinjured1max=V118 if V115==6 & V116==1
gen sickorinjured2max=V128 if V125==6 & V126==1
gen sickorinjured3max=V138 if V135==6 & V136==1
gen sickorinjured4max=V148 if V145==6 & V146==1
gen sickorinjured5max=V158 if V155==6 & V156==1
gen sickorinjured6max=V168 if V165==6 & V166==1
gen sickorinjured7max=V178 if V175==6 & V176==1
gen sickorinjured8max=V188 if V185==6 & V186==1
gen sickorinjured9max=V198 if V195==6 & V196==1
gen sickorinjured10max=V208 if V205==6 & V206==1
gen sickorinjured11max=V218 if V215==6 & V216==1
gen sickorinjured12max=V228 if V225==6 & V226==1
gen sickorinjured13max=V238 if V235==6 & V236==1
gen sickorinjured14max=V248 if V245==6 & V246==1
gen sickorinjured15max=V258 if V255==6 & V256==1
gen sickorinjured16max=V268 if V265==6 & V266==1
gen sickorinjured17max=V278 if V275==6 & V276==1
gen sickorinjured18max=V288 if V285==6 & V286==1
gen sickorinjured19max=V298 if V295==6 & V296==1
gen sickorinjured20max=V308 if V305==6 & V306==1	

gen other1max=V118 if V115==11 & V116==1
gen other2max=V128 if V125==11 & V126==1
gen other3max=V138 if V135==11 & V136==1
gen other4max=V148 if V145==11 & V146==1
gen other5max=V158 if V155==11 & V156==1
gen other6max=V168 if V165==11 & V166==1
gen other7max=V178 if V175==11 & V176==1
gen other8max=V188 if V185==11 & V186==1
gen other9max=V198 if V195==11 & V196==1
gen other10max=V208 if V205==11 & V206==1
gen other11max=V218 if V215==11 & V216==1
gen other12max=V228 if V225==11 & V226==1
gen other13max=V238 if V235==11 & V236==1
gen other14max=V248 if V245==11 & V246==1
gen other15max=V258 if V255==11 & V256==1
gen other16max=V268 if V265==11 & V266==1
gen other17max=V278 if V275==11 & V276==1
gen other18max=V288 if V285==11 & V286==1
gen other19max=V298 if V295==11 & V296==1
gen other20max=V308 if V305==11 & V306==1	

egen deathrowmax_tot=rowtotal(deathrow1max deathrow2max deathrow3max deathrow4max ///
	deathrow5max deathrow6max deathrow7max deathrow8max deathrow9max ///
	deathrow10max deathrow11max deathrow12max deathrow13max ///
	deathrow14max deathrow15max deathrow16max deathrow17max ///
	deathrow18max deathrow19max deathrow20max)

egen protectcustmax_tot=rowtotal(protectcust1max protectcust2max protectcust3max protectcust4max ///
	protectcust5max protectcust6max protectcust7max protectcust8max protectcust9max ///
	protectcust10max protectcust11max protectcust12max protectcust13max ///
	protectcust14max protectcust15max protectcust16max protectcust17max ///
	protectcust18max protectcust19max protectcust20max)
	
egen adminsegmax_tot=rowtotal(adminseg1max adminseg2max adminseg3max adminseg4max ///
	adminseg5max adminseg6max adminseg7max adminseg8max adminseg9max ///
	adminseg10max adminseg11max adminseg12max adminseg13max ///
	adminseg14max adminseg15max adminseg16max adminseg17max ///
	adminseg18max adminseg19max adminseg20max)
	
egen disciplinactionmax_tot=rowtotal(disciplinaction1max disciplinaction2max disciplinaction3max disciplinaction4max ///
	disciplinaction5max disciplinaction6max disciplinaction7max disciplinaction8max disciplinaction9max ///
	disciplinaction10max disciplinaction11max disciplinaction12max disciplinaction13max ///
	disciplinaction14max disciplinaction15max disciplinaction16max disciplinaction17max ///
	disciplinaction18max disciplinaction19max disciplinaction20max)
	
egen solitarycustmax_tot=rowtotal(adminsegmax_tot disciplinactionmax_tot)	
	
egen sickorinjuredmax_tot=rowtotal(sickorinjured1max sickorinjured2max sickorinjured3max sickorinjured4max ///
	sickorinjured5max sickorinjured6max sickorinjured7max sickorinjured8max sickorinjured9max ///
	sickorinjured10max sickorinjured11max sickorinjured12max sickorinjured13max ///
	sickorinjured14max sickorinjured15max sickorinjured16max sickorinjured17max ///
	sickorinjured18max sickorinjured19max sickorinjured20max)
	
egen othermax_tot=rowtotal(other1max other2max other3max other4max ///
	other5max other6max other7max other8max other9max ///
	other10max other11max other12max other13max ///
	other14max other15max other16max other17max ///
	other18max other19max other20max)

gen all1max=V118 if V116==1
gen all2max=V128 if V126==1
gen all3max=V138 if V136==1
gen all4max=V148 if V146==1
gen all5max=V158 if V156==1
gen all6max=V168 if V166==1
gen all7max=V178 if V176==1
gen all8max=V188 if V186==1
gen all9max=V198 if V196==1
gen all10max=V208 if V206==1
gen all11max=V218 if V216==1
gen all12max=V228 if V226==1
gen all13max=V238 if V236==1
gen all14max=V248 if V246==1
gen all15max=V258 if V256==1
gen all16max=V268 if V266==1
gen all17max=V278 if V276==1
gen all18max=V288 if V286==1
gen all19max=V298 if V296==1
gen all20max=V308 if V306==1		
	
egen allmax_tot =rowtotal( all1max all2max all3max all4max all5max all6max ///
	all7max all8max all9max all10max all11max all12max all13max ///
	all14max all15max all16max all17max all18max all19max all20max)

egen restricted_tot=rowtotal(protectcust_tot adminseg_tot disciplinaction_tot ///
	sickorinjured_tot other_tot)
	
egen restrictedmax_tot=rowtotal(protectcustmax_tot adminsegmax_tot disciplinactionmax_tot ///
	sickorinjuredmax_tot othermax_tot)

gen maleadp=V63
gen femaleadp=V64
egen totaladp=rowtotal(maleadp femaleadp)

gen malepopcount=V65
gen femalepopcount=V71
egen totalpopcount=rowtotal(malepopcount femalepopcount)

cd "$project\Data"
save "1990 Prison Census Data.dta", replace

clear
cd "$project\Data"
use "1990 Prison Census Data.dta", clear

cd "$project"
putexcel set "Restricted Housing in Prisons by Jurisdiction 1979-2017.xlsx", sheet("1990") modify

local row=2
forvalues i=1/52 {
	fre statename if statenum==`i'
	putexcel A`row'=`r(lab_valid)'
	sum totaladp if statenum==`i'
	putexcel B`row'=`r(sum)'
	sum maleadp if statenum==`i'
	putexcel C`row'=`r(sum)'
	sum femaleadp if statenum==`i'
	putexcel D`row'=`r(sum)'
	sum totalpopcount if statenum==`i'
	putexcel E`row'=`r(sum)'
	sum malepopcount if statenum==`i'
	putexcel F`row'=`r(sum)'
	sum femalepopcount if statenum==`i'
	putexcel G`row'=`r(sum)'
	sum deathrow_tot if statenum==`i'
	putexcel H`row'=`r(sum)'
	sum protectcust_tot if statenum==`i'
	putexcel I`row'=`r(sum)'
	sum adminseg_tot if statenum==`i'
	putexcel J`row'=`r(sum)'
	sum disciplinaction_tot if statenum==`i'
	putexcel K`row'=`r(sum)'
	sum solitarycust_tot if statenum==`i'
	putexcel L`row'=`r(sum)'
	sum sickorinjured_tot if statenum==`i'
	putexcel M`row'=`r(sum)'
	sum other_tot if statenum==`i'
	putexcel N`row'=`r(sum)'
	sum restricted_tot if statenum==`i'
	putexcel O`row'=`r(sum)'
	sum deathrowmax_tot if statenum==`i'
	putexcel P`row'=`r(sum)'	
	sum protectcustmax_tot if statenum==`i'
	putexcel Q`row'=`r(sum)'
	sum adminsegmax_tot if statenum==`i'
	putexcel R`row'=`r(sum)'
	sum disciplinactionmax_tot if statenum==`i'
	putexcel S`row'=`r(sum)'
	sum solitarycustmax_tot if statenum==`i'
	putexcel T`row'=`r(sum)'
	sum sickorinjuredmax_tot if statenum==`i'
	putexcel U`row'=`r(sum)'
	sum othermax_tot if statenum==`i'
	putexcel V`row'=`r(sum)'
	sum restrictedmax_tot if statenum==`i'
	putexcel W`row'=`r(sum)'
	sum allmax_tot if statenum==`i'
	putexcel X`row'=`r(sum)'
	local ++row
	}

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

/*	Note: 1995 data must be reformatted from a text file before it can be used 
	in Stata if you wish to replicate this exercise using the raw file from the 
	ICPSR website. The excel file imported below is the reformatted dataset with
	no other alterations.
*/

clear
cd "$project\Data\Raw Data\Census of State and Federal Adult Correctional Facilities\1995"

import excel "1995 Prison Census Data.xlsx", firstrow clear
save "1995 Prison Census Data.dta", replace

use "1995 Prison Census Data.dta", clear

destring V1, replace
gen statenum=V1
order statenum, after(V1)

destring V2, replace
gen factype=V2
order factype, after(V2)

gen federal=factype==6
order federal, after(factype)

cd "$project\Data"
save "1995 Prison Census Data.dta", replace

cd "$project\Data"
clear
use "List of States.dta", clear
tempfile Listofstates
save `Listofstates'
use "1995 Prison Census Data.dta"
merge m:1 statenum using `Listofstates'

order statename, after(statenum)

replace statenum=52 if federal==1
replace statename="Federal Prisons" if statenum==52

destring _all, replace

gen totaladp=V71
gen totalpopcount=V73
gen malepopcount=V91
gen femalepopcount=V93
gen deathrow_tot=V254
gen protectcust_tot=V245
replace protectcust_tot=0 if protectcust==999999
gen adminseg_tot=V248
replace adminseg_tot=0 if adminseg_tot==999999
gen disciplinaction_tot=V242
replace disciplinaction_tot=0 if disciplinaction_tot==999999
gen solitarycust_tot=adminseg_tot + disciplinaction_tot
egen restricted_tot = rowtotal( deathrow_tot protectcust_tot adminseg_tot ///
	disciplinaction_tot)
gen maxmales=V75
gen maxfemales=V77
egen allmax_tot=rowtotal(maxmales maxfemales)

cd "$project\Data"
save "1995 Prison Census Data.dta", replace

clear
cd "$project\Data"
use "1995 Prison Census Data.dta", clear

cd "$project"
putexcel set "Restricted Housing in Prisons by Jurisdiction 1979-2017.xlsx", sheet("1995") modify

local row=2
forvalues i=1/52 {
	fre statename if statenum==`i'
	putexcel A`row'=`r(lab_valid)'
	sum totaladp if statenum==`i'
	putexcel B`row'=`r(sum)'
	sum totalpopcount if statenum==`i'
	putexcel C`row'=`r(sum)'
	sum malepopcount if statenum==`i'
	putexcel D`row'=`r(sum)'
	sum femalepopcount if statenum==`i'
	putexcel E`row'=`r(sum)'
	sum deathrow_tot if statenum==`i'
	putexcel F`row'=`r(sum)'
	sum protectcust_tot if statenum==`i'
	putexcel G`row'=`r(sum)'
	sum adminseg_tot if statenum==`i'
	putexcel H`row'=`r(sum)'
	sum disciplinaction_tot if statenum==`i'
	putexcel I`row'=`r(sum)'
	sum solitarycust_tot if statenum==`i'
	putexcel J`row'=`r(sum)'
	sum restricted_tot if statenum==`i'
	putexcel K`row'=`r(sum)'
	sum maxmales if statenum==`i'
	putexcel L`row'=`r(sum)'
	sum maxfemales if statenum==`i'
	putexcel M`row'=`r(sum)'
	sum allmax_tot if statenum==`i'
	putexcel N`row'=`r(sum)'
	local ++row
	}

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

clear
cd "$project\Data\Raw Data\Census of State and Federal Adult Correctional Facilities\2000"

use "04021-0001-Data.dta", clear

gen statenum=substr(VID,1,2)
order statenum, after(VID)
destring statenum, replace

gen factype=substr(VID,3,1)
order factype, after(VID)
destring factype, replace

gen federal=factype==6
order federal, after(factype)

cd "$project\Data"
save "2000 Prison Census Data.dta", replace

cd "$project\Data"
clear
use "List of States.dta", clear
tempfile Listofstates
save `Listofstates'
use "2000 Prison Census Data.dta"
merge m:1 statenum using `Listofstates'

order statename, after(statenum)

replace statenum=52 if federal==1
replace statename="Federal Prisons" if statenum==52

gen maleadp=V18M
gen femaleadp=V18F
gen totalpopcount=V13
gen malepopcount=V17EM
gen femalepopcount=V17EF
gen deathrow_tot=V20
gen protectcust_tot=V35C
gen adminseg_tot=V35A
gen disciplinaction_tot=V35B
gen solitarycust_tot=adminseg_tot + disciplinaction_tot
egen restricted_tot = rowtotal( deathrow_tot protectcust_tot adminseg_tot ///
	disciplinaction_tot)
gen maxmales=V17AM
gen maxfemales=V17AF
egen allmax_tot=rowtotal(maxmales maxfemales)

cd "$project\Data"
save "2000 Prison Census Data.dta", replace

clear
cd "$project\Data"
use "2000 Prison Census Data.dta", clear

cd "$project"
putexcel set "Restricted Housing in Prisons by Jurisdiction 1979-2017.xlsx", sheet("2000") modify

local row=2
forvalues i=1/52 {
	fre statename if statenum==`i'
	putexcel A`row'=`r(lab_valid)'
	sum maleadp if statenum==`i'
	putexcel B`row'=`r(sum)'
	sum femaleadp if statenum==`i'
	putexcel C`row'=`r(sum)'
	sum totalpopcount if statenum==`i'
	putexcel D`row'=`r(sum)'
	sum malepopcount if statenum==`i'
	putexcel E`row'=`r(sum)'
	sum femalepopcount if statenum==`i'
	putexcel F`row'=`r(sum)'
	sum deathrow_tot if statenum==`i'
	putexcel G`row'=`r(sum)'
	sum protectcust_tot if statenum==`i'
	putexcel H`row'=`r(sum)'
	sum adminseg_tot if statenum==`i'
	putexcel I`row'=`r(sum)'
	sum disciplinaction_tot if statenum==`i'
	putexcel J`row'=`r(sum)'
	sum solitarycust_tot if statenum==`i'
	putexcel K`row'=`r(sum)'
	sum restricted_tot if statenum==`i'
	putexcel L`row'=`r(sum)'
	sum maxmales if statenum==`i'
	putexcel M`row'=`r(sum)'
	sum maxfemales if statenum==`i'
	putexcel N`row'=`r(sum)'
	sum allmax_tot if statenum==`i'
	putexcel O`row'=`r(sum)'
	local ++row
	}

***************************************************************************
clear
cd "$project\Data\Raw Data\Census of State and Federal Adult Correctional Facilities\2005"

use "24642-0001-Data.dta", clear

gen statenum=substr(V1,1,2)
order statenum, after(V1)
destring statenum, replace

gen factype=substr(V1,3,1)
order factype, after(V1)
destring factype, replace

gen federal=factype==6
order federal, after(factype)

cd "$project\Data"
save "2005 Prison Census Data.dta", replace

cd "$project\Data"
clear
use "List of States.dta", clear
tempfile Listofstates
save `Listofstates'
use "2005 Prison Census Data.dta"
merge m:1 statenum using `Listofstates'

order statename, after(statenum)
replace statenum=52 if federal==1
replace statename="Federal Prisons" if statenum==52

gen maleadp=V76
gen femaleadp=V77
replace V78=0 if V78==9999
gen totalpopcount=V78
replace V79=0 if V79==9999
gen adultmalecount=V79
replace V81=0 if V81==999
gen minormalecount=V81
gen malepopcount=adultmalecount
replace V80=0 if V80==9999
gen adultfemalecount=V80
replace V82=0 if V82==999
gen minorfemalecount=V82
gen femalepopcount=adultfemalecount
replace V103=0 if V103==9999
gen deathrow_tot=V103
replace V110=0 if V110==999
gen protectcust_tot=V110
replace V112=0 if V112==9999
gen adminseg_tot=V112
replace V111=0 if V111==999
gen disciplinaction_tot=V111
gen solitarycust_tot=adminseg_tot + disciplinaction_tot
egen restricted_tot = rowtotal( deathrow_tot protectcust_tot adminseg_tot ///
	disciplinaction_tot)
replace V94=0 if V94==9999
gen allmax_tot=V94

cd "$project\Data"
save "2005 Prison Census Data.dta", replace

clear
cd "$project\Data"
use "2005 Prison Census Data.dta", clear

cd "$project"
putexcel set "Restricted Housing in Prisons by Jurisdiction 1979-2017.xlsx", sheet("2005") modify

local row=2
forvalues i=1/52 {
	fre statename if statenum==`i'
	putexcel A`row'=`r(lab_valid)'
	sum maleadp if statenum==`i'
	putexcel B`row'=`r(sum)'
	sum femaleadp if statenum==`i'
	putexcel C`row'=`r(sum)'
	sum totalpopcount if statenum==`i'
	putexcel D`row'=`r(sum)'
	sum malepopcount if statenum==`i'
	putexcel E`row'=`r(sum)'
	sum femalepopcount if statenum==`i'
	putexcel F`row'=`r(sum)'
	sum deathrow_tot if statenum==`i'
	putexcel G`row'=`r(sum)'
	sum protectcust_tot if statenum==`i'
	putexcel H`row'=`r(sum)'
	sum adminseg_tot if statenum==`i'
	putexcel I`row'=`r(sum)'
	sum disciplinaction_tot if statenum==`i'
	putexcel J`row'=`r(sum)'
	sum solitarycust_tot if statenum==`i'
	putexcel K`row'=`r(sum)'
	sum restricted_tot if statenum==`i'
	putexcel L`row'=`r(sum)'
	sum allmax_tot if statenum==`i'
	putexcel M`row'=`r(sum)'
	local ++row
	}

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