/*****************************************************************************

This do file replicates Tables S2 and S3 in the Supplementary Material of the 
impact evaluation paper of SPOON Guatemala

Update on 6/17/2024

*****************************************************************************/

*---------------------------------------------------------------*
*	TABLE S2. Baseline demographics, by follow up status		*
*---------------------------------------------------------------*

/*CAREGIVER*/
{ 
	* Load data
	use "${bl_processed}/mom.dta", clear
	drop if comunidad_elegible==0
	rename idunico folio
	
	drop T1 T2 Control
	gen double id_persona = idpersona	

	merge m:1 folio using "${el_processed}/all_households.dta", keepusing(T1 T2 Control folio attrited)
	drop if _merge==2
	tab attrited

	* Re-label variables
	lab var age "Age, years"
	lab var indigenous "Indigenous"
	lab var married "Married"
	lab var work "Employed"
	lab var educ_prim_c "Completed primary"
	lab var decisiones "Decision-making power, 0-1"
	lab var rosenberg "Rosenberg score, 0-30"
	lab var grit "Grit score, 0-5"

	* Panel title and sample size
	gen mom_lab=.
	lab var mom_lab "Caregiver"
	gen sample_size=.
	lab var sample_size "n"

	* Output to Excel
	putexcel set "${tables_sup}/tablesS2-S3.xls", sheet("Table S2") replace
	
	putexcel C3=("Total")
	putexcel D3=("LTFU")
	putexcel E3=("At endline")
	putexcel F3 =("p-value")
	
	local row=4
	sum folio
	local n =string(r(N), "%9.0f")
	sum folio if attrited==1
	local n_ltf =string(r(N), "%9.0f")
	sum folio if attrited==0
	local n_el = string(r(N), "%9.0f")
		
	quietly putexcel C5 =("`n'")
	quietly putexcel D5 =("`n_ltf'")
	quietly putexcel E5 =("`n_el'")
	
	foreach depvar of varlist mom_lab sample_size age indigenous married work educ_prim_c decisiones rosenberg grit{
	
	local dep_varl : variable label `depvar'
	
	if (`depvar'==indigenous) | (`depvar'== married) | (`depvar'== work)| (`depvar'==educ_prim_c) {

	*TOTAL
	sum `depvar'  
	local mean = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"
	local n =string(r(N), "%9.0f")

	*LTFU 
	sum `depvar' if attrited==1
	local mean_ltf = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"

	*AT ENDLINE
	sum `depvar'  if attrited==0
	local mean_el = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"

	}
	else{
		
	*TOTAL
	sum `depvar'  
	local mean = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"
	local n =string(r(N), "%9.0f")

	*LTFU 
	sum `depvar'  if attrited==1
	local mean_ltf = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"

	*AT ENDLINE
	sum `depvar'  if attrited==0
	local mean_el = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"

	}
	
	if `n' >0 {
	quietly putexcel B`row'=("`dep_varl'") 
	quietly putexcel C`row' =("`mean'")
	quietly putexcel D`row' =("`mean_ltf'")
	quietly putexcel E`row' =("`mean_el'")
	
	/** BALANCE **
	reg `depvar' attrited, cl(communityid)
	local p1 = string(2*ttail(e(df_r),abs(_b[attrited]/_se[attrited])), "%9.3f")
	stars `p1'
	local b1= string(_b[attrited], "%9.3f")+"$s"
	
	quietly putexcel F`row' =("`p1'")*/
	local ++row
	} 
	
	else{
	quietly putexcel B`row'=("`dep_varl'") 
	local ++row
	}
}	
}	

/*HOUSEHOLD*/
{
	* Load data and generate variables
	use "${bl_processed}/household.dta", clear
	drop if comunidad_elegible==0
	
	gen improved_sanitation=.
	replace improved_sanitation = 1 if bath_use_1==1 & (sanitation_1==1 | sanitation_2==1)
	replace improved_sanitation = 0 if improved_sanitation!=1 & bath_use!=.
		
	gen improved_water=.
	replace improved_water = 1 if water_1==1 | water_2==1 | water_3==1 | water_5==1
	replace improved_water = 0 if improved_water!=1 & water!=.

	drop T1 T2 Control
	
	rename idunico folio
	merge 1:1 folio using "${el_processed}/all_households.dta", keepusing(T1 T2 Control folio attrited)
	tab attrited
	drop if _merge==2
	
	gen hh_lab=.
	lab var hh_lab "Household"
	gen sample_size=.
	label var sample_size "n"

	* Re-label variables
	lab var size "Number of members"
	lab var improved_water "Improved water" 
	lab var improved_sanitation "Improved sanitation"
	lab var wealth "Wealth index"
	lab var inc_h "Monthly household income, GTQ"

	* Output to Excel
	putexcel set "${tables_sup}/tablesS2-S3.xls", sheet("Table S2") modify
	local row=15
	sum folio
	local n =string(r(N), "%9.0f")
	sum folio if attrited==1
	local n_ltf =string(r(N), "%9.0f")
	sum folio if attrited==0
	local n_el = string(r(N), "%9.0f")
		
	quietly putexcel C16 =("`n'")
	quietly putexcel D16 =("`n_ltf'")
	quietly putexcel E16 =("`n_el'")
	
	foreach depvar of varlist hh_lab sample_size size improved_water improved_sanitation wealth inc_h {
	
	local dep_varl : variable label `depvar'

	if (`depvar'==improved_water) | (`depvar'== improved_sanitation){

	*TOTAL
	sum `depvar'  
	local mean = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"
	local n =string(r(N), "%9.0f")

	*LTFU 
	sum `depvar' if attrited==1
	local mean_ltf = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"

	*AT ENDLINE
	sum `depvar'  if attrited==0
	local mean_el = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"

	}
	else{
		
	*TOTAL
	sum `depvar'  
	local mean = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"
	local n =string(r(N), "%9.0f")

	*LTFU 
	sum `depvar'  if attrited==1
	local mean_ltf = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"

	*AT ENDLINE
	sum `depvar'  if attrited==0
	local mean_el = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"

	}
	
	if `n' >0 {
	quietly putexcel B`row'=("`dep_varl'") 
	quietly putexcel C`row' =("`mean'")
	quietly putexcel D`row' =("`mean_ltf'")
	quietly putexcel E`row' =("`mean_el'")

	/** BALANCE **
	reg `depvar' attrited, cl(communityID)
	local p1 = string(2*ttail(e(df_r),abs(_b[attrited]/_se[attrited])), "%9.3f")
	stars `p1'
	local b1= string(_b[attrited], "%9.3f")+"$s"
	
	quietly putexcel F`row' =("`p1'")*/
	local ++row
	} 
	
	else{
	quietly putexcel B`row'=("`dep_varl'") 
	local ++row
	}
}	
}	


*-----------------------------------------------------------------------------*
*	TABLE S3. DESCRIPTIVE STATISTICS FOR LTFU HOUSEHOLDS, BY TREATMENT GROUP  *
*-----------------------------------------------------------------------------*

/*CAREGIVER*/
{	
	* Load data
	use "${bl_processed}/mom.dta", clear
	drop if comunidad_elegible==0
	rename idunico folio
	
	drop T1 T2 Control
	gen double id_persona = idpersona	

	merge m:1 folio using "${el_processed}/all_households.dta", keepusing(T1 T2 Control folio attrited)
	drop if _merge==2
	tab attrited

	* Re-label variables
	lab var age "Age, y"
	lab var indigenous "Indigenous"
	lab var married "Married"
	lab var work "Employed"
	lab var educ_prim_i "Less than primary"
	lab var educ_prim_c "Completed primary"
	lab var educ_sec_c "Completed secondary"
	lab var decisiones "Decision-making power, 0-1"
	lab var rosenberg "Rosenberg score, 0-30"
	lab var grit "Grit score, 0-5"

	* Panel title and sample size
	gen mom_lab=.
	lab var mom_lab "Caregiver"
	gen sample_size=.
	lab var sample_size "n"

	* Keep only LTFU
	keep if attrited==1
	
	* Output to Excel
	putexcel set "${tables_sup}/tablesS2-S3.xls", sheet("Table S3") modify
		
	putexcel C3=("Total")
	putexcel D3=("T1")
	putexcel E3=("T2")
	putexcel F3=("Control")

	local row=4
	sum folio
	local n =string(r(N), "%9.0f")
	sum folio if T1==1
	local n_t1 =string(r(N), "%9.0f")
	sum folio if T2==1
	local n_t2 =string(r(N), "%9.0f")
	sum folio if Control==1
	local nc =string(r(N), "%9.0f")

	quietly putexcel C5 =("`n'")
	quietly putexcel D5 =("`n_t1'")
	quietly putexcel E5 =("`n_t2'")
	quietly putexcel F5 =("`nc'")

	foreach depvar of varlist mom_lab sample_size age indigenous married work educ_prim_c decisiones rosenberg grit{
	
	local dep_varl : variable label `depvar'
	
	if (`depvar'==indigenous) | (`depvar'== married) | (`depvar'== work)| (`depvar'==educ_prim_c) {
		** DESCRIPTIVES **

		*TOTAL
		sum `depvar'  
		local mean = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"
		local n =string(r(N), "%9.0f")

		*T1
		sum `depvar'  if T1==1
		local mean_t1 = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"

		*T2
		sum `depvar'  if T2==1
		local mean_t2 = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%9.1f")+")"

		*CONTROL
		sum `depvar' if Control==1
		local meanc = string(r(sum), "%9.0f")+" ("+(string(r(mean)*100),"%9.1f")+")"
		
	}
	else{
		** DESCRIPTIVES **

		*TOTAL
		sum `depvar'  
		local mean = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"
		local n =string(r(N), "%9.0f")

		*T1
		sum `depvar'  if T1==1
		local mean_t1 = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"

		*T2
		sum `depvar'  if T2==1
		local mean_t2 = string(r(mean), "%12.2f")+" ("+string(r(sd),"%9.2f")+")"

		*CONTROL
		sum `depvar' if Control==1
		local meanc = string(r(mean), "%9.2f")+" ("+string(r(sd),"%9.2f")+")"
	}
	
	if `n' >0 {
	quietly putexcel B`row'=("`dep_varl'") 
	quietly putexcel C`row' =("`mean'")
	quietly putexcel D`row' =("`mean_t1'")
	quietly putexcel E`row' =("`mean_t2'")
	quietly putexcel F`row' =("`meanc'")
	local ++row
	} 
	
	else{
	quietly putexcel B`row'=("`dep_varl'") 
	local ++row
	}
}
}

/*HOUSEHOLD*/
{
	* Load data and generate variables
	use "${bl_processed}/household.dta", clear
	drop if comunidad_elegible==0
	
	gen improved_sanitation=.
	replace improved_sanitation = 1 if bath_use_1==1 & (sanitation_1==1 | sanitation_2==1)
	replace improved_sanitation = 0 if improved_sanitation!=1 & bath_use!=.
		
	gen improved_water=.
	replace improved_water = 1 if water_1==1 | water_2==1 | water_3==1 | water_5==1
	replace improved_water = 0 if improved_water!=1 & water!=.

	drop T1 T2 Control
	
	rename idunico folio
	merge 1:1 folio using "${el_processed}/all_households.dta", keepusing(T1 T2 Control folio attrited)
	tab attrited
	drop if _merge==2
	
	gen hh_lab=.
	lab var hh_lab "Household"
	gen sample_size=.
	label var sample_size "n"

	* Re-label variables
	lab var size "Number of members"
	lab var improved_water "Improved water" 
	lab var improved_sanitation "Improved sanitation"
	lab var wealth "Wealth index"
	lab var inc_h "Monthly household income, GTQ"

	* Keep only LTFU
	keep if attrited==1
	
	* Output to Excel
	putexcel set "${tables_sup}/tablesS2-S3.xls", sheet("Table 3") modify
	local row=15
	sum folio
	local n =string(r(N), "%9.0f")
	sum folio if T1==1
	local n_t1 =string(r(N), "%9.0f")
	sum folio if T2==1
	local n_t2 =string(r(N), "%9.0f")
	sum folio if Control==1
	local nc =string(r(N), "%9.0f")
		
	quietly putexcel C16 =("`n'")
	quietly putexcel D16 =("`n_t1'")
	quietly putexcel E16 =("`n_t2'")
	quietly putexcel F16 =("`nc'")

foreach depvar of varlist hh_lab sample_size size improved_water improved_sanitation wealth inc_h {
	
	local dep_varl : variable label `depvar'
	
	if (`depvar'==improved_water) | (`depvar'== improved_sanitation){
		** DESCRIPTIVES **

		*TOTAL
		sum `depvar'  
		local mean = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"
		local n =string(r(N), "%9.0f")

		*T1
		sum `depvar'  if T1==1
		local mean_t1 = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%12.1f")+")"

		*T2
		sum `depvar'  if T2==1
		local mean_t2 = string(r(sum), "%12.0f")+" ("+(string(r(mean)*100),"%9.1f")+")"

		*CONTROL
		sum `depvar' if Control==1
		local meanc = string(r(sum), "%9.0f")+" ("+(string(r(mean)*100),"%9.1f")+")"
	}
	else{
		** DESCRIPTIVES **

		*TOTAL
		sum `depvar'  
		local mean = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"
		local n =string(r(N), "%9.0f")

		*T1
		sum `depvar'  if T1==1
		local mean_t1 = string(r(mean), "%12.2f")+" ("+string(r(sd),"%12.2f")+")"

		*T2
		sum `depvar'  if T2==1
		local mean_t2 = string(r(mean), "%12.2f")+" ("+string(r(sd),"%9.2f")+")"

		*CONTROL
		sum `depvar' if Control==1
		local meanc = string(r(mean), "%9.2f")+" ("+string(r(sd),"%9.2f")+")"		
	}
	
	if `n' >0 {
	quietly putexcel B`row'=("`dep_varl'") 
	quietly putexcel C`row' =("`mean'")
	quietly putexcel D`row' =("`mean_t1'")
	quietly putexcel E`row' =("`mean_t2'")
	quietly putexcel F`row' =("`meanc'")
	local ++row
	} 
	
	else{
	quietly putexcel B`row'=("`dep_varl'") 
	local ++row
	}
}
}