clear
set more off
#delimit ;
/*****************************************************************************************************************;
**This dofile starts from expandedDHSGPS_mergedrecoded and generates mergedallrecoded;
other pre-generated datasets this dofile also uses:
DHSGPS_matchedwithYYYY
hh_simulatedmindistance_YYYY_grid
some more raw files
*****************************************************************************************************************/;



*****************************************************************************************************************;
*Paths;
*****************************************************************************************************************;

*access files in subdirectory generated by data creating dofiles;
local indataset "dtafiles/expandedDHSGPS_mergedrecoded";

local outdataset "dtafiles/mergedallrecoded.dta";



local path=subinstr(c(pwd),"Documents","Dropbox\BF\data replication",.);
cap cd "`path'";

use "`indataset'", clear;


/**GKS: This data is at the husband level, so when we merge it with the woman/child data, we need to merge**
**it using the respondent's line number and the respondent's spouse's line number, so we need to keep**
**mv003 and mv034*;
use "`path'/DHS2007/BDMR51DT/BDMR51FL.DTA";
keep mv001 mv002 mv005 mv716 sm121 mv003 mv034*;

gen hhid=7*1000000+mv001*1000+mv002;
ren mv716 occupation;
ren sm121 sufficientearnings;
ren mv003 v034;

/*bys hhid: gen personnumber=_n;
reshape wide occupation sufficientearnings mv005, i(hhid) j(personnumber);*/;

tempfile hubs2007;
save `hubs2007', replace;

use "`path'/DHS2004/BDMR4JDT/BDMR4JFL.DTA", clear;
keep mv001 mv002 mv005 mv716 sm126 v003 mv034*; *smdistr;

gen hhid=4*1000000+mv001*1000+mv002;
ren mv716 occupation;
ren sm126 sufficientearnings;
ren v003 v034;

/*bys hhid: gen personnumber=_n;
reshape wide occupation sufficientearnings mv005, i(hhid) j(personnumber);*/;

append using `hubs2007';
sort hhid v034;
tempfile hubs;
save `hubs', replace;
**HW: the 1999 survey didn't ask men if they felt their earnings were sufficient to support thier families, and the occupation variable is in the children's recode already, so I don't think we need to merge in the men's file from 1999**;

*/;


************************************************************************************************************************************
use "dtafiles/expandedDHSGPS_mergedrecoded.dta", clear;
cap drop _merge;

/*sort hhid v034;
merge m:1 hhid v034 using `hubs';

gen agriculture=1 if occupation==12|occupation==13;
replace agriculture=0 if agriculture==. & occupation!=999;
replace agriculture=. if year==1999;
replace agriculture=1 if v705==4|v705==5;

gen business=1 if occupation==52|occupation==51;
replace business=0 if business==. & occupation!=999;
replace business=. if year==1999;
**GKS: this is not same as business from occupation data: replace business=1 if v705==1|v705==3;

** in case we wanted to get occupations of all men in hh, not just spouse, note has to be
** done before dropping _m==2 obs because other men (non fathers) are in that pool;
egen hhagriculture=max(agriculture), by(hhid);
egen hhbusiness=max(business), by(hhid);
drop if _m==2;
drop _m mv034*;
*/;

preserve;
**GKS: Added 1999 survey to get district, which we get for 2004 and 2007 from the community leader surveys below;
use "rawdta/DHS1999/BDHR41DT/BDHR41FL.DTA", clear;
keep hv001 hv002 shdist;

gen hhid=9*1000000+hv001*1000+hv002;

tempfile district1999;
save `district1999', replace;

restore;
merge m:1 hhid using `district1999';
drop if _merge==2;
drop _merge;

preserve;

**HW: merging in community leader survey**;
use "rawdta/DHS2007/BDSQ51DT/bdCQ51FL.DTA", clear;
gen dhsidyear=2007*1000+coclust;

tempfile community2007;
save `community2007', replace;

use "rawdta/DHS2004/bdsq4idt/bdSQ4IFL.dta", clear;
gen dhsidyear=2004*1000+cclust;
tempfile community2004;
save `community2004', replace;

restore;

merge m:1 dhsidyear using `community2007';
drop _merge;
merge m:1 dhsidyear using `community2004';
drop _merge;

gen villagepipedwater=1 if co113==1|c113==11|c113==12;
replace villagepipedwater=0 if villagepipedwater==. & ((co113!=. & year==2007) | (c113!=. & year==2004));
replace villagepipedwater=. if co113==99|c113==99;


/*gen villagetubewell=1 if co113==3|co113==4|c113==21|c113==22;
replace villagetubewell=0 if villagetubewell==. & ((co113!=. & year==2007) | (c113!=. & year==2004));
replace villagetubewell=. if co113==99|c113==99;

**GKS: co113 is never 23?;
*gen villagesurfacewater=1 if co113==23|c113==31|c113==32;
gen villagesurfacewater=1 if co113==5|c113==31|c113==32;
replace villagesurfacewater=0 if villagesurfacewater==. & ((co113!=. & year==2007) | (c113!=. & year==2004));
replace villagesurfacewater=. if co113==99|c113==99;
*/;

label variable villagepipedwater "village leader lists piped water as primary water source in cluster";

/*
preserve;

*HW: merging in year of earliest birth that was a son**;
use "rawdta/DHS2007/BDBR51DT/BDBR51FL.DTA", clear;
gen double motherid=7*1000000000+v001*1000000+v002*1000+v003;
egen temp=min(b2) if b4==1, by(motherid);
egen firstsonbirthyr=mean(temp),by(motherid);
keep motherid firstsonbirthyr;
duplicates drop;
save "rawdta/dtafiles/firstsonbirthyr2007.dta", replace;

use "rawdta/DHS2004/BDBR4Jdt/BDBR4JFL.dta", clear;
gen double motherid=4*1000000000+v001*1000000+v002*1000+v003;
egen temp=min(b2) if b4==1, by(motherid);
egen firstsonbirthyr=mean(temp),by(motherid);
keep motherid firstsonbirthyr;
duplicates drop;
save "`path'/dtafiles/firstsonbirthyr2004.dta", replace;

use "`path'/DHS1999/BDBR41dt/BDBR41FL.dta", clear;
gen double motherid=9*1000000000+v001*1000000+v002*1000+v003;
egen temp=min(b2) if b4==1, by(motherid);
egen firstsonbirthyr=mean(temp),by(motherid);
keep motherid firstsonbirthyr;
duplicates drop;
save "`path'/dtafiles/firstsonbirthyr1999.dta", replace;

append using "`path'/dtafiles/firstsonbirthyr2004.dta";
append using "`path'/dtafiles/firstsonbirthyr2007.dta";

save "`path'/dtafiles/firstsonbirthyr.dta", replace;

restore;
*/;

gen double motherid=7*1000000000+dhsid07*1000000+v002*1000+v003;
replace motherid=4*1000000000+dhsid04*1000000+v002*1000+v003 if year==2004;
replace motherid=9*1000000000+dhsid99*1000000+v002*1000+v003 if year==1999;

/*
merge m:1 motherid using "`path'/dtafiles/firstsonbirthyr.dta";
drop if _merge==2;
drop _merge;
replace firstsonbirthyr=2000 if firstsonbirthyr==0;
replace firstsonbirthyr=firstsonbirthyr+1900 if firstsonbirthyr<100;

*/;
preserve;


use "dtafiles/hh_simulatedmindistance_2007_grid.dta", clear;
for var  mean_mindistanceU-wmean_mindistanceU_inC2radius: rename X X_G;
sort dhsid; 
/*merge dhsid using "dtafiles/hh_simulatedmindistance_2007_random.dta";
for var  mean_mindistanceU-wmean_mindistanceU_inC2radius: rename X X_R;*/;
gen year=2007;
tempfile sim2007;
save `sim2007', replace;

use "dtafiles/hh_simulatedmindistance_2004_grid.dta";
for var  mean_mindistanceU-wmean_mindistanceU_inC2radius: rename X X_G;
sort dhsid; 
/*merge dhsid using "dtafiles/hh_simulatedmindistance_2004_random.dta";
for var  mean_mindistanceU-wmean_mindistanceU_inC2radius: rename X X_R;*/;
gen year=2004;
tempfile sim2004;
save `sim2004', replace;

use "`path'/dtafiles/hh_simulatedmindistance_1999_grid.dta";
for var  mean_mindistanceU-wmean_mindistanceU_inC2radius: rename X X_G;
sort dhsid; 
/*merge dhsid using "`path'/dtafiles/hh_simulatedmindistance_1999_random.dta";
for var  mean_mindistanceU-wmean_mindistanceU_inC2radius: rename X X_R;*/;
gen year=1999;
append using `sim2007';
append using `sim2004';

*drop _m;
gen dhsidnum= real(substr(dhsid,-3,3));
gen dhsidyear=year*1000+dhsidnum;
sort dhsidyear;

tempfile sims;
save `sims', replace;
#delimit ;
/**HW: merge in simulated distance to nearest well**;
use "`path'/dtafiles/hh_simulatedmindistance_2007_100_grid.dta", clear;
for var  fraction_mindistanceC_1m- wfraction_mindistanceC_2m: rename X X_G100;
sort dhsid; 
merge dhsid using "`path'/dtafiles/hh_simulatedmindistance_2007_100_random.dta";
for var  fraction_mindistanceC_1m- wfraction_mindistanceC_2m: rename X X_R100;
gen year=2007;
tempfile sim2007100;
save `sim2007100', replace;

use "`path'/dtafiles/hh_simulatedmindistance_2004_100_grid.dta";
for var  fraction_mindistanceC_1m- wfraction_mindistanceC_2m: rename X X_G100;
sort dhsid; 
merge dhsid using "`path'/dtafiles/hh_simulatedmindistance_2004_100_random.dta";
for var  fraction_mindistanceC_1m- wfraction_mindistanceC_2m: rename X X_R100;
gen year=2004;
tempfile sim2004100;
save `sim2004100', replace;

use "`path'/dtafiles/hh_simulatedmindistance_1999_100_grid.dta";
for var  fraction_mindistanceC_1m- wfraction_mindistanceC_2m: rename X X_G100;
sort dhsid; 
merge dhsid using "`path'/dtafiles/hh_simulatedmindistance_1999_100_random.dta";
for var  fraction_mindistanceC_1m- wfraction_mindistanceC_2m: rename X X_R100;
gen year=1999;
append using `sim2007100';
append using `sim2004100';

drop _m;
gen dhsidnum= real(substr(dhsid,-3,3));
gen dhsidyear=year*1000+dhsidnum;
sort dhsidyear;

tempfile sims100;
save `sims100', replace;
*/;
restore;

merge m:1 dhsidyear using `sims';
rename _m mergesims;
drop dhsidnum;

/*sort dhsidyear;
merge m:1 dhsidyear using `sims100';
rename _m mergesims100;
drop dhsidnum;
*/;

/*
preserve;
**HW: Merging in grid data weighted by distance to well;
use "`path'/dtafiles/1999_grid.dta", clear;
gen year=1999;
sort dhsid;
merge dhsid using "`path'/dtafiles/1999_random.dta";
tempfile sim1999well;
save `sim1999well', replace;

use "`path'/dtafiles/2004_grid.dta";
gen year=2004;
sort dhsid;
merge dhsid using "`path'/dtafiles/2004_random.dta";
tempfile sim2004well;
save `sim2004well', replace;

use "`path'/dtafiles/2007_grid.dta";
gen year=2007;
sort dhsid;
merge dhsid using "`path'/dtafiles/2007_random.dta";
tempfile sim2007well;
save `sim2007well', replace;
append using `sim2004well';
append using `sim1999well';

drop _m;
gen dhsidnum= real(substr(dhsid,-3,3));
gen dhsidyear=year*1000+dhsidnum;
sort dhsidyear;

tempfile simswell;
save `simswell', replace;
restore;

sort dhsidyear;
merge m:1 dhsidyear using `simswell';
rename _m mergesimswell;
drop dhsidnum;
*/;

**GKS: new stuff;
forvalues i=1996(1)2007 {;
	local j=substr(string(`i'),-2,2);
	
	*gen fraction_mindistanceC_1m_G_b`j'=fraction_mindistanceC_1m_G*byear`i';
	gen wfraction_mindistanceC_1m_G_b`j'=wfraction_mindistanceC_1m_G*byear`i';
	
};
foreach measure in "fraction_mindistanceC_1m_G" 
	"wfraction_mindistanceC_1m_G" {; //"fraction_mindistanceC_2m_G" "wfraction_mindistanceC_2m_G" {;

gen `measure'_2002=`measure'*post2002;
*gen `measure'_2001=`measure'*post2001;
*gen `measure'_2003=`measure'*post2003;
};

merge m:1 dhsidyear using "`path'/dtafiles/DHSGPS_matchedwith2004.dta";
drop _merge;

/*merge m:1 dhsidyear using "`path'/dtafiles/DHSGPS_matchedwith2007.dta";
drop _merge;

merge m:1 dhsidyear using "`path'/dtafiles/DHSGPS_matchedwith1999.dta";
drop _merge;
*/;

gen district=cdist;
replace district=codist if district==. & codist!=.;
replace district=shdist if district==. & shdist!=.;



**HW: creates mean contamination within 5 miles for each district so we can drop ones with all missing 
**this means that some clusters with no wells in 5 mi will still be included, but no districts that weren't surveyed;
bys district: egen distcont=mean(pctcontaminated5);
label variable distcont "mean of pctcontaminated5 by district";
**HW: note that districts 3, 46 and 84 appear to be the districts where BGS did no testing
**Dropping these 3 districts only results in dropping 314 obs;

/**HW: Merging in birth composition;

merge m:1 motherid using "`path'/dtafiles/birthcomp.dta";

**HW: this drops the mothers who haven't had any children in the past 5 years and therefore aren't in our dataset;
drop if _m==2;
drop _merge;
*/;

label variable dhsidyear "unique year-cluster identifier";

save "`outdataset'", replace;


