*********************************************
*WAVoterToStata.do
**
*Turn tab-delimited voter files obtained from Washington
*Secretary of State from 2008 and 2010 into Stata files.
**
*Alan Gerber, Gregory Huber, Seth Hill, "Identifying the Effects of Elections Held All-Mail on
*Turnout: Staggered Reform in the Evergreen State."
*********************************************

clear
set more off
capture log close
log using WAVoterToStata.log, text replace
di c(current_date)+ " " + c(current_time)

**************************
*2006 file.
**************************
*Active registrants.
/* StatTransfered from Access file in zip archive. */
use "200701FromSOS/voterextract.dta", clear
count

*Create unique household identifying variable.
gen address_string = regstnum	+ " " + regstfrac	+ " " + regstpredirection	+ " " + regstname	+ " " + regsttype	+ " " + regstpostdirection
replace address_string = subinstr(address_string,"  "," ",.)
replace address_string = subinstr(address_string,"  "," ",.)
replace address_string = subinstr(address_string,"  "," ",.)
replace address_string = trim(address_string)
gen household_string = address_string + " " + regcity	+ ", " + regzipcode
replace household_string = upper(household_string)

*Create unique identifier.
sort household_string
egen hh_group = group(household_string)
summ hh_group
bysort hh_group: gen num_in_hh = _N
label variable num_in_hh "Number at this address among all active registrants"
tab num_in_hh

drop address_string household_string

isid statevoterid

*No history file. 2006 turnout included as variable lastvoted.
gen vote2006g = (lastvoted == "11/07/2006")
tab countycode vote2006g, row miss

*All registrants.
compress
count
save WA_Voter_All_Registrants_200701.dta, replace

/*
*King and Pierce Counties only.
preserve
keep if countycode == "KI" | countycode == "PI"
compress
count
save WA_Voter_KingAndPierce_Registrants_200701.dta, replace
restore

*All other counties. "Treated" counties 2006-2008 are Island, Kittitas, and Klickitat.
drop if countycode == "KI" | countycode == "PI"
compress
count
save WA_Voter_NotKingAndPierce_Registrants_200701.dta, replace
*/

**************************
*2008 file.
**************************
*Active registrants.
/* Stata didn't insheet all of the cases, so StatTransfer was used instead.
clear
insheet using "200812FromSOS/DelimitedVoterExtract_Active.txt", tab names
compress
save WA_Voter_All_Registrants_200812.dta, replace
*/
use "200812FromSOS/delimitedvoterextract_active.dta", clear
count

*Create unique household identifying variable.
gen address_string = regstnum	+ " " + regstfrac	+ " " + regstpredirection	+ " " + regstname	+ " " + regsttype	+ " " + regstpostdirection
replace address_string = subinstr(address_string,"  "," ",.)
replace address_string = subinstr(address_string,"  "," ",.)
replace address_string = subinstr(address_string,"  "," ",.)
gen state = "WA"
gen household_string = address_string + " " + regcity	+ ", " + state + " " + regzipcode
replace household_string = upper(household_string)

*Create unique identifier.
sort household_string
egen hh_group = group(household_string)
summ hh_group
bysort hh_group: gen num_in_hh = _N
label variable num_in_hh "Number at this address among all active registrants"
tab num_in_hh

*Outsheet unique addresses to csv.
preserve
local keepvarlist hh_group address_string regcity state regzipcode countycode 
contract `keepvarlist'
sort countycode regzipcode hh_group
outsheet `keepvarlist' using "WA_Voter_UniqueAddresses_200812.csv", comma replace
restore

drop address_string household_string state

*One person missing a state voter id.
drop if statevoterid == ""

*All registrants.
compress
count
save WA_Voter_All_Registrants_200812.dta, replace

/*
*King and Pierce Counties only.
preserve
keep if countycode == "KI" | countycode == "PI"
compress
count
save WA_Voter_KingAndPierce_Registrants_200812.dta, replace
restore

*All other counties. "Treated" counties 2006-2008 are Island, Kittitas, and Klickitat.
drop if countycode == "KI" | countycode == "PI"
compress
count
save WA_Voter_NotKingAndPierce_Registrants_200812.dta, replace
*/

**************************
*2008 Voter histories.
**************************
if (1) {
  *Memory intenstive insheet.
  clear
  insheet using "200812FromSOS/VoterHistory-delimited.txt", tab names
  compress
  count
  save WA_Voter_All_Histories_200812.dta, replace
}
else {
  use WA_Voter_All_Histories_200812.dta, clear
}

/*
*King and Pierce Counties only.
keep if countycode == "KI" | countycode == "PI"

*2000 histories and later.
gen yearvoted = substr(voted,7,4)
destring yearvoted, force replace
keep if yearvoted >= 2000
compress
count
save WA_Voter_KingAndPierce_Histories_2000AndLater_200812.dta, replace
*/

**************************
*2010 file.
**************************
*Active registrants.
/* Stata didn't insheet all of the cases, so StatTransfer was used instead.
clear
insheet using "201012FromSOS/DelimitedVoterExtract_Active.txt", tab names
compress
save WA_Voter_All_Registrants_201012.dta, replace
*/
use "201012FromSOS/delimitedvoterextract_active.dta", clear
count
drop lastvoted

*All registrants.
compress
count
save WA_Voter_All_Registrants_201012.dta, replace

/*
*King and Pierce Counties only.
keep if countycode == "KI" | countycode == "PI"
compress
count
save WA_Voter_KingAndPierce_Registrants_201012.dta, replace
*/

**************************
*Voter histories.
**************************
if (1) {
  *Memory intenstive insheet.
  clear
  insheet using "201012FromSOS/VotingHistory-delimited.txt", tab names
  compress
  count
  save WA_Voter_All_Histories_201012.dta, replace
}
else {
  use WA_Voter_All_Histories_201012.dta, clear
}

/*
*King and Pierce Counties only.
keep if countycode == "KI" | countycode == "PI"

*2000 histories and later.
gen yearvoted = substr(voted,7,4)
destring yearvoted, force replace
keep if yearvoted >= 2000
compress
count
save WA_Voter_KingAndPierce_Histories_2000AndLater_201012.dta, replace
*/
log close
