// Merge all files for analysis of historical relationships 

use "cleaned_ExposureCounty"
merge 1:1 county state year month using "cleaned_ReportCounty"
drop _merge
merge 1:1 county state year month using "cleaned_ClimateVars"
tab state if _merge==1 
drop _merge
keep if sst!=. // keep only coastal counties per the SST data 

sort state county year month 
drop if state=="-"
drop if county=="NAME"

* fill missing vars with zeros
foreach x of varlist case_e_all case_e_food case_e_non case_e_vul case_e_par case_e_alg ///
   case_e_other case_r_all case_r_food case_r_non case_r_vul case_r_par case_r_alg case_r_other { 
   replace `x'=0 if `x'==.
      }

* create binary vars
foreach x in e r {
 foreach y in all food non vul par alg other {
   gen binary_`x'_`y'=1 if case_`x'_`y'>0
     replace binary_`x'_`y'=0 if binary_`x'_`y'==.
   }
   }


* partition data into regions (AK and HI separate regions)
gen region = ""
local gulf_states FL LA AL TX MS
foreach state of local gulf_states{
	replace region = "gulf" if state == "`state'"
}

local west_states CA WA OR
foreach state of local west_states{
	replace region = "west" if state == "`state'"
}

local east_states CT DE GA ME MD MA NH NJ NC NY PA RI SC VA DC 
foreach state of local east_states{
	replace region = "east" if state == "`state'"
}

replace region="AK" if state=="AK"
replace region="HI" if state=="HI"

* generate a numeric state and region var
encode region, gen(region_num)
gen state_num=substr(FIPS,-5,2)
	replace state_num=substr(FIPS,-4,1) if state_num==""
	destring state_num, replace

order region region_num state state_num county FIPS year month binary_e* binary_r* case_e* case_r* sst sss tmean 

label var region "Region"
label var region_num "Region code"
label var state "State"
label var state_num "State code"
label var county "County"
label var FIPS "FIPS code"
label var year "Reporting year"
label var month "Reporing month"
label var binary_e_all "Any vibrio cases in exposure location (1=yes)"
label var binary_e_food "Any foodborne vibrio cases in exposure location (1=yes)"
label var binary_e_non "Any nonfoodborne vibrio cases in exposure location (1=yes)"
label var binary_e_vul "Any vibrio vulnificus cases in exposure location (1=yes)"
label var binary_e_par "Any vibrio parahaemolyticus cases in exposure location (1=yes)"
label var binary_e_alg "Any vibrio alginolyticus cases in exposure location (1=yes)"
label var binary_e_other "Any other vibrio species cases in exposure location (1=yes)"
label var binary_r_all "Any vibrio cases in reporting location (1=yes)"
label var binary_r_food "Any foodborne vibrio cases in reporting location (1=yes)"
label var binary_r_non "Any nonfoodborne vibrio cases in reporting location (1=yes)"
label var binary_r_vul "Any vibrio vulnificus cases in reporting location (1=yes)"
label var binary_r_par "Any vibrio parahaemolyticus cases in reporting location (1=yes)"
label var binary_r_alg "Any vibrio alginolyticus cases in reporting location (1=yes)"
label var binary_r_other "Any other vibrio species cases in reporting location (1=yes)"
label var case_e_all "Number of total vibrio cases by exposure location"
label var case_e_food "Number of foodborne vibrio cases by exposure location"
label var case_e_non "Number of nonfoodborne vibrio cases by exposure location"
label var case_e_vul "Number of vibrio vulnificus cases by exposure location"
label var case_e_par "Number of vibrio parahaemolyticus cases by exposure location"
label var case_e_alg "Number of vibrio alginolyticus cases by exposure location"
label var case_e_other "Number of other vibrio species cases by exposure location"
label var case_r_all "Number of total vibrio cases by reporting location"
label var case_r_food "Number of foodborne vibrio cases by reporting location"
label var case_r_non "Number of nonfoodborne vibrio cases by reporting location"
label var case_r_vul "Number of vibrio vulnificus cases by reporting location"
label var case_r_par "Number of vibrio parahaemolyticus cases by reporting location"
label var case_r_alg "Number of vibrio alginolyticus cases by reporting location"
label var case_r_other "Number of other vibrio species cases by reporting location"
label var sst "Sea surface temperature"
label var sss "Sea surface salinity"
label var tmean "Air temperature"

count // 47,088 obs
codebook FIPS // 327 counties
codebook state // 25 states, including DC 
codebook year // 12 years

save "cleaned_ALL", replace 
clear