* CHANGE THE DIRECTORY TO THE ONE WHERE THIS CODE IS LOCATED
cd // XXXX

* Chetty data
use "http://www.equality-of-opportunity.org/images/onlinedata8.dta", clear // These are the covariates. Last accessed 5/30/2015
destring pop2000, replace ignore(",")
merge 1:1 cz czname using "http://www.equality-of-opportunity.org/images/preferred_measures.dta", nogenerate // These are the mobility measures. Last accessed 5/30/2015
keep cz czname pop2000 stateabbrv state_id cs_race_bla cs_race_theil_2000 cs00_seg_inc cs00_seg_inc_pov25 hhinc00 gini inc_share_1perc gini99 ccd_exp_tot ccd_pup_tch_ratio score_r dropout_r cs_labforce cs_born_foreign scap_ski90pcm rel_tot crime_violent cs_fam_wkidsinglemom cs_divorced s_rank e_rank_b prob_p1_k5
order cz czname pop2000 stateabbrv state_id
label var s_rank "absolute upward mobility (Chetty et al 2014)"
label var prob_p1_k5 "prob. (child --> top 5th | parents in bottom 5th) (Chetty et al. 2014)"
label var e_rank_b "relative mobility (Chetty et al. 2014)"

* collapse into state dataset while preserving variable labels
foreach v of var * { // from Nic Cox, http://www.stata.com/support/faqs/data-management/keeping-same-variable-with-collapse/
 	local l`v' : variable label `v'
	if `"`l`v''"' == "" {
		local l`v' "`v'"
	}
}
collapse cs_race_bla- prob_p1_k5 [fweight=pop2000], by(stateabbrv state_id) // this requires the "order" command above to exclude the by variables from the varlist
foreach v of var * {
	label var `v' "`l`v''"
}

decode state_id, gen(state)
order stateabbrv state_id state
tempfile Chetty
save `Chetty'

* adding the murder and prison rates
import excel using "http://www.census.gov/popest/data/intercensal/state/tables/ST-EST00INT-01.xls", cellra(A10:H60) clear 
drop C D E G
rename (A B F H) (state pop2000 pop2003 pop2005) // the population numbers are the official April 1, 2000 census count, and the July 1, 2003 and 2005 estimates, respectively
tempfile pop
save `pop', replace
collapse (sum) pop*
gen state="United States Total" // this is also in the original Excel file, but the cell format is difficult to load into Stata
append using `pop'
replace state=subinstr(state,".","",.)
gen mergestate=subinstr(state," ","",.)
save `pop', replace

import excel datasources/UCR_murder_by_states.xlsx, clear firstrow
destring Year, replace force // force concerns table information from Excel import
recode Year (1999/2001=2000) (2004/2006=2005) (*=.)
drop if mi(Year)
collapse Alabama-UnitedStates, by(Year)
xpose, clear varname
rename (v1 v2 _varname) (murder2000 murder2005 mergestate)
drop if _n==1
merge 1:1 mergestate using `pop', nogenerate keepusing(state pop2000 pop2005)
foreach year of numlist 2000 2005 {
	gen murderrate`year'=100000*murder`year'/pop`year'
	label var murderrate`year' "murder/nonnegligent manslaughter / 100k + yr, smoothed 3 yrs, UCR/Census"
}
keep state murderrat*
tempfile murder
save `murder', replace

import excel datasources/Sourcebook2003_t62.xlsx, clear sheet("cleancopy") firstrow case(lower)
keep state prisonjail
replace state = "District of Columbia" if state=="District of Columbiac"
tempfile prisoners
save `prisoners', replace
collapse (sum) prisonjail
gen state="United States Total"
append using `prisoners'
merge 1:1 state using `pop', nogenerate keepusing(pop2003 pop2005)
gen incarceration2003=100000*prisonjail/pop2003
keep state incarceration
label var incarceration "incarceration per 100k in 2003, Sourcebook t 6.2 / Census"
merge 1:1 state using `murder', nogenerate

merge 1:1 state using `Chetty', nogenerate

* clean-up

foreach var of varlist incarceration2003 murderrat* {
	gen ln_`var'=ln(`var')
}

replace stateabbrv="USA" if state=="United States Total"
order stateabbrv state state_id incarceration murderrat* ln_incarceration ln_murderrat*
compress
save state_dataset.dta, replace
