* to run this code, first name path of "datasources" folder
local path C:\Users\hspamann\Documents_sugarsynced\Documents\Projects\In_progress\crimlaw_legorig\drafts\CURRENT\paper\ALERsubmission\code\data_generation\datasources
cd `path'/UNCTS

set more off
clear all
tempfile UNCTS UNCTS1 UNCTS2 bin countrynames

**** preliminaries: demographics file

tempfile UNCTSms

forvalues N=2/3 {
insheet using 2_and_3\Demo`N'.csv, comma clear
keep country totpop* m1519*
reshape long totpop m1519, i(country) j(year)
replace year=year+1900
capture: destring t m, replace ignore("#N/A")
replace totpop=. if totpop<0
replace m1519=. if m1519<0
recode totpop m1519 (99999 999999 = .)
drop if mi(m,t)
drop if t<m
gen UNCTSms1519=m/t
drop m t
drop if U>0.11
* the latter would be implausibly large values; I have verified that this concerns only countries with obviously misstated populations (compared to other years)
* there are no outliers below 0.03
rename country countrycode
merge m:m countrycode using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop countrycode 
if `N'==2 save `UNCTSms', replace
else merge 1:1 ISO year using `UNCTSms', nogenerate update
}

label var U "males 15-19, share of population, from UNCTS 2-3"
save UNCTS_ms, replace


***** 1 & 2

forvalues sweep=1/2 {
infix varnum 1-3 str country 4-15 hom70 16-25 hom71 26-35 hom72 36-45 hom73 46-55 hom74 56-65 hom75 66-75 using 1_and_2\wcsone.txt, clear
drop if _n<31
keep if varnum==`sweep'
drop varnum
reshape long hom, i(country) j(year)
drop if mi(hom)
replace year=1900+year
if `sweep'==2 replace year=year+5
rename hom homicides`sweep'
label var homicides`sweep' "homicides, UNCTS `sweep' revised by UNCIJN"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
save `UNCTS`sweep'', replace
}


infix varnum 1-3 str country 4-15 using 1_and_2\wcstwo.txt, clear
keep if varnum==30
drop varnum
save `countrynames', replace
infix varnum 1-3 str pris70 4-12 pris72 13-22 pris74 23-32 pris76 33-42 pris77 43-52 pris78 53-62 pris79 63-72 pris80 73-80 using 1_and_2\wcstwo.txt, clear
keep if varnum==40
drop varnum
destring pris70, replace
merge 1:1 _n using `countrynames', nogenerate
reshape long pris, i(country) j(year)
replace year=1900+year
drop if mi(pris)
gen prisoners2=pris if year>1975
replace pris=. if year>1975
rename pris prisoners1
label var prisoners1 "prisoners, UNCTS 1 revised by UNCIJN"
label var prisoners2 "prisoners, UNCTS 2 revised by UNCIJN"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country

* merge of homicides to prisoners, separately for 1 and 2 to avoid unnecessary duplicates when adding values 2b from package 2/3 below 
preserve
keep ISO year prisoners1
drop if mi(prisoners)
merge 1:1 ISO year using `UNCTS1', nogenerate
save `UNCTS', replace
restore
keep ISO year prisoners2
drop if mi(prisoners)
merge 1:1 ISO year using `UNCTS2', nogenerate
rename *2 *2a
save `UNCTS2', replace


******* 2b

insheet using 2_and_3\unwcs2l_CRIMES.csv, comma clear // I previously extracted this as a wk1 file from unwcs2l.zip and converted to csv
keep country tihom*
reshape long tihom, i(country) j(year)
recode tihom (-1=.)
drop if mi(tihom)
replace year=1900+year
rename tihom homicides2b
label var homicides2b "homicides, UNCTS 2 from 2-3 package"
rename country countrycode
merge m:m countrycode using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop countrycode
merge 1:1 ISO year using `UNCTS2', nogenerate
save `UNCTS2', replace


insheet using 2_and_3\unwcs2l_DETAIN.csv, comma clear // I previously extracted this as a wk1 file from unwcs2l.zip and converted to csv
keep country tadet* // consider using also tjdet* = juvenile detained. missing for more countries; on average, 10% of adult pop.
reshape long tadet, i(country) j(year)
recode tadet (-1=.)
drop if mi(tadet)
replace year=1900+year
rename tadet prisoners2b
label var prisoners2b "total adults detained, UNCTS 2 from 2-3 package"
rename country countrycode
merge m:m countrycode using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop countrycode
merge 1:1 ISO year using `UNCTS2', nogenerate

append using `UNCTS'
save `UNCTS', replace

*********** 3 


insheet using 2_and_3\unwcs3la_CCRIMES.csv, comma clear // I previously extracted this as a wk1 file from unwcs3la.zip and converted to csv
keep country inthom*
reshape long inthom, i(country) j(year)
recode inthom (99999=.)
drop if mi(inthom)
replace year=1900+year
rename inthom homicides3
label var homicides3 "homicides, UNCTS 3 from 2-3 package"
rename country countrycode
merge m:m countrycode using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop countrycode
save `bin'

insheet using 2_and_3\unwcs3lb_VPRISON.csv, comma clear // I previously extracted this as a wk1 file from unwcs3lb.zip and converted to csv
keep country totpri*
rename totpris4 totpri84
reshape long totpri, i(country) j(year)
recode totpri (99999=.) (999999=.)
drop if mi(totpri)
drop if totpri>2500000 // CVI has some absurd e149 number
replace year=1900+year
rename totpri prisoners3
label var prisoners3 "total incarcerated, UNCTS 3 from 2-3 package"
rename country countrycode
merge m:m countrycode using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop countrycode
merge 1:1 ISO year using `bin', nogenerate
append using `UNCTS'
save `UNCTS', replace


********** 4

infix 5 first str country 1-20 pris1986 21-32 pris1988 33-44 pris1990 45-55 using 4\totpris.txt, clear // careful: Croatia numbers >.5m obviously wrong
reshape long pris, i(country) j(year)
drop if mi(pris)
rename pris prisoners4
label var prisoners4 "total prisoners, UNCTS 4"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
save `bin', replace

infix 8 first str country 1-20 hom1986 21-27 hom1987 28-39 hom1988 40-51 hom1989 52-63 hom1990 64-75 using 4\cominhom.txt, clear
drop if mi(country)
reshape long hom, i(country) j(year)
drop if mi(hom)
rename hom homicides4
label var homicides4 "total committed intentional homicides, UNCTS 4"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
merge 1:1 ISO year using `bin', nogenerate
append using `UNCTS'
save `UNCTS', replace


********** 5

import excel using 5\5KPRISON.xlsx, clear firstrow cellrange(A1:G200)
drop IDN DAY
recode TOTPRI90 TOTPRI92 TOTPRI94 (99999999=.)
drop if mi(TOTPRI90) & mi(TOTPRI92) & mi(TOTPRI94)
drop COUNTRY
rename CON country
reshape long TOTPRI, i(country) j(year)
drop if mi(TOTPRI)
replace year=year+1900
rename TOTPRI prisoners5a
label var prisoners5 "total persons incarcerated, UNCTS 5"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
save `bin', replace


import excel using 5\5CCCRIME.xlsx, clear firstrow cellrange(B1:R200)
keep CON_ COUNTRY CIH*
recode CIH* (9999999=.)
egen empty=rowmiss(CIH*)
drop if empty==5
drop empty
drop COUNTRY
rename CON country
reshape long CIHPOL, i(country) j(year)
drop if mi(CIHPOL)
replace year=year+1900
rename CIH homicides5a
label var homicides5 "total recorded committed intentional homicides, UNCTS 5"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
merge 1:1 ISO year using `bin', nogenerate
save `bin', replace


******** 5-7

import excel using 5_6_and_7\pris_hom_UNCTS567.xlsx, clear firstrow sheet("totpri")
drop if mi(country) | country=="TOTPRI"
reshape long totpri, i(country) j(year)
drop if mi(totpri)
rename totpri prisoners567
label var prisoners567 "TOTPRI from UNCTS 5-7"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
merge 1:1 ISO year using `bin', nogenerate
save `bin', replace

import excel using 5_6_and_7\pris_hom_UNCTS567.xlsx, clear firstrow sheet("cihpol")
drop if mi(country) | inlist(country,"CIHPOL","Country")
destring cihpol*, replace
reshape long cihpol, i(country) j(year)
drop if mi(cihpol)
rename cihpol homicides567
label var homicides567 "CIHPOL from UNCTS 5-7"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
merge 1:1 ISO year using `bin', nogenerate
append using `UNCTS'
save `UNCTS', replace

************* 8

local hom "Total recorded intentional homicide, completed"
local pris "Total persons incarcerated"
import excel 8\8th_all_050331.xls, sheet("raw") firstrow clear
keep ISO year VLabel NumValue
keep if inlist(VLabel,"`hom'","`pris'")
rename ISO ISO3code
replace VLabel="homicides8" if VL=="`hom'"
replace VLabel="prisoners8" if VL=="`pris'"
reshape wide NumValue, i(ISO year) j(VLabel) string
rename NumValue* *
label var hom "`hom', CTS 8"
label var pris "`pris', CTS 8"
append using `UNCTS'
save `UNCTS', replace

********* 9 and 10

import excel using 9_and_10\CTS9_10_hom_pris.xlsx, clear firstrow sheet("cts9hom")
drop *rate*
reshape long hom, i(country) j(year)
drop if mi(hom)
rename hom homicides9
label var homicides9 "Total recorded intentional homicide, completed, CTS 9"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
append using `UNCTS'
save `UNCTS', replace

import excel using 9_and_10\CTS9_10_hom_pris.xlsx, clear firstrow sheet("cts9pris")
reshape long pris, i(country) j(year)
drop if mi(pris)
rename pris prisoners9
label var prisoners9 "Total persons incarcerated, CTS 9"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
append using `UNCTS'
save `UNCTS', replace

import excel using 9_and_10\CTS9_10_hom_pris.xlsx, clear firstrow sheet("cts10hom")
reshape long hom, i(country) j(year)
drop if mi(hom)
rename hom homicides10
label var homicides10 "Intentional homicide, completed. Total recorded offences at nat'l level, CTS10"
replace country=proper(country)
merge m:m country using ISO3codes.dta, nogenerate keep(1 3) keepusing(ISO)
drop country
append using `UNCTS'


*******************************************
******* general cleanup

drop if mi(ISO) // these are 5 observations; should be Catalonia and country code N.WK1 from 3rd and 4th surveys
replace ISO="ROU" if ISO=="ROM"
save `UNCTS', replace

keep if inlist(ISO,"UKI","ENG","SCO")
preserve
collapse (count) pris* hom*, by(year)
recode pris* hom* (0/2=-99) (3=.)
tempfile knockout
save `knockout', replace
restore
collapse (sum) pris* hom*, by(year)
merge 1:1 year using `knockout', nogenerate update replace
recode pris* hom* (-99=.)
egen something=rownonmiss(pris* hom*)
drop if something==0
drop something
gen ISO3code="GBR"
merge 1:m ISO year using `UNCTS', nogenerate update replace

merge m:1 ISO year using `path'/Population_USCensusBureau/Census_IDB_for_export.dta, keep(1 3) keepusing(population) nogenerate

compress
order ISO year hom* pris*
* save UNCTS_complete_absolute.dta, replace

drop if mi(population)

foreach number of varlist pris* {
replace `number'=10^5*`number'/population
replace `number'=. if !inrange(`number',10,1000) // not even the US is 1000 today, while only San Marino has less than 15 in ICPS data; hand checked all dropped numbers and compared to other years, looked fishy
}

foreach number of varlist hom* {
replace `number'=10^5*`number'/population
replace `number'=. if !inrange(`number',0.5,150) // max violent deaths in IHME data is 101 for Lesotho in 2010; minimum is 0.53 for Bosnia in 1990; in UNCTS data, Japan always above 0.5, Singapore drops below only in 2006
}

drop population

* the following are countries identified by the code below (deviations by factor two from one sweep to next, and then (back or implausibly high)).
* I also checked for implausibly large rates, i.e., rates above 500 that are inconsistent with other information (in particular ICPS rates for same or different years that are less than half)
replace homicides1=. if ISO=="IRL" // Ireland in round one is two orders of magnitudes higher than 2. The latter is more in line with later numbers, but ...
replace homicides2a=. if ISO=="IRL"
replace homicides3=. if ISO=="NLD" // Netherlands has a rate >10 in the 3rd sweep, an order of magnitude higher than other sweeps ...
replace prisoners3=. if ISO=="NOR" // Norway is up by a factor of 10 in the 3rd sweep relative to 2 and 4
replace prisoners4=. if ISO=="AUT" // Austria is up by a factor 2-3 vs sweeps 3 and 5, even in overlapping years (1986 and 1990). The number would put AUstria far ahead of all other EUropean countries
replace prisoners8=. if ISO=="CYP" // Cyprus is up by a factor of 4 in sweep 8 vs sweeps 7 and 9
replace prisoners567=. if ISO=="LVA" & inrange(year,1998,2000) // according to UNCTS 6 and 7, jumps from 10,137 to 3,561 between 1997 and 1998 ... Former number corresponds to ICPS data and sweep 8
replace prisoners567=. if ISO=="MUS" & inrange(year,1995,1997) // according to UNCTS 5-7, jumps from 1,020 to 3,094 in 1994-95 and then back from 3,448 to 1,510 in 1997-1998; other years are like 5 and 7
replace prisoners4=. if ISO=="MUS" // 2.5 times higher than same year in UNCTS 5, five times higher than six years prior in UNCTS 2 ...
replace prisoners9=. if ISO=="MUS" // three times as high as UNCTS 8 three years prior, twice as high as ICPS for same years
foreach var of varlist prisoners* { // seems utterly unreliable -- the two versions of survey 2 differ by factor of 2; survey 3 jumps by more than factor 2 between 1984 and 1986; jumps by factor 5 from 1997 and 1998 and stays there: level 5 times higher than ICPS for same years
replace `var'=. if ISO=="LKA"
} 
foreach sweep in 1 2a 2b {
replace prisoners`sweep'=. if ISO=="QAT" // Qatar has implausibly large values here: 800+, the world's max, as opposed to ~100 in later years
}
replace prisoners567=. if ISO=="SWZ" // values at least four times higher than ICPS values for same years and UNCTS values before and after
replace prisoners567=. if ISO=="JOR" // values about 10 times higher than ICPS values for same years and UNCTS values after
replace prisoners4=. if ISO=="GHA" & year==1986 // clear typo -- ten times higher than preceding and subsequent value

egen something=rownonmiss(pris* hom*)
drop if something==0
drop something

foreach var in homicides prisoners { // this takes care of double observations for 2nd and 5th sweeps
egen UNCTS2`var'rate=rowmean(`var'2a `var'2b)
egen UNCTS5`var'rate=rowmean(`var'5*) if year<1995
gen UNCTS7`var'rate=`var'567 if year>1997
gen UNCTS6`var'rate=`var'567 if inrange(year,1995,1997)
drop `var'2a `var'2b `var'5*
}
rename UNCTS?homicides* UNCTS?hom*
rename UNCTS?prisoners* UNCTS?pris*
rename homicides* UNCTS*homrate
rename prisoners* UNCTS*prisrate

forvalues sweep=1/10 {
	capture label var UNCTS`sweep'pris "Incarceration rate per 100k, UNCTS `sweep'"
	label var UNCTS`sweep'hom "Intentional completed hom. per 100k, UNCTS `sweep'"
}

egen UNCTShomrate=rowmean(UNCTS*homrate)
egen UNCTSprisrate=rowmean(UNCTS?prisrate)
label var UNCTShom "Intentional completed hom. per 100k, UNCTS 1-10"
label var UNCTSpris "Incarceration rate per 100k, UNCTS 1-9"


*************
** SAVING ***
egen homdata=rownonmiss(UNCTS?homrate UNCTS10)
egen prisdata=rownonmiss(UNCTS?prisrate)
drop if !homdata & !prisdata
drop homdata prisdata

order UNCTS*homrate, sequential
order UNCTS*prisrate, sequential
order ISO year UNCTShomrate UNCTSprisrate UNCTS?homrate UNCTS10homrate UNCTS?prisrate
compress

* separate observations per sweep
gen int UNCTShomrate_source=.
gen int UNCTSprisrate_source=.
forvalues sweep=1/10 {
	replace UNCTShomrate_source=`sweep' if !mi(UNCTS`sweep'homrate)
}
forvalues sweep=1/9 {
	replace UNCTSprisrate_source=`sweep' if !mi(UNCTS`sweep'prisrate)
}
label var UNCTShomrate_source "source of UNCTShomrate: which sweep"
label var UNCTSprisrate_source "source of UNCTSprisrate: which sweep"
* save UNCTS_complete_rates.dta, replace

* joint observations: thanks to Nic Cox for the label code (http://www.stata.com/support/faqs/data-management/keeping-same-variable-with-collapse/)
keep ISO year UNCTShomrate UNCTSprisrate *source
foreach v of var * {
	local l`v' : variable label `v'
	if `"`l`v''"' == "" local l`v' "`v'"
}
sort year
collapse (lastnm) UNCTS*, by(ISO year)
foreach v of var * {
	label var `v' "`l`v''"
}

