**** Egypt Analysis - David Files

cd "C:\Users\wb200090\Dropbox\Egypt -- Microinsurance with ABA\2 Cleaned Data"
use "Input\EPPclean_mg.dta", clear 


**** Variables needed:
* profits truncated at 99th percentile following pre-analysis plan
drop x10 x11
gen x10=b_605_1 
replace x10=7000 if x10>7000 & x10~=.
gen x11=b_605_2 
replace x11=8000 if x11>8000 & x11~=.
gen numworkers=b_210A_1
gen anypaidworker=numworkers>0 & numworkers~=.
gen manufacturing=b_202>=10 & b_202<=32
gen retail=b_202==47
gen assets=0
forval x=1/8 {
replace assets=assets+b_508_`x'
}

gen inventory=b_509A
gen cashonhand=b_511
gen valueofgoodsatstartofday=b_510
gen totalassets=assets+inventory+cashonhand
gen completedhighschool=1 if ((b_106>3 & b_106~=.) | (b_106==2 & b_107>2))
replace completedhighschool=0 if completedh~=1
gen ageofowner=2012-b_103YEAR if b_103YEAR<=1993
gen ageofbusiness=2012-b_202Y
replace ageofbusiness=. if ageofbusiness<0

*** Table 1: Summary Statistics by Treatment Status


mat y = J(21,3,.)	
local j=1
foreach var of varlist female  x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 anypaidworker ageofbusiness manufacturing retail totalassets completedhighschool ageofowner {
forval x=0/1 {
local k=`x'+1
sum `var' if treat==`x', d
mat y[`j',`k'] = r(mean)
}
ttest `var', by(treat)
mat y[`j',3] = r(p)
local j=`j'+1
}
mat rownames y = "Female" Shock_Risk Risk_Averse Ambiguity_Neutral HighBasisRisk MediumBasisRisk ConsiderDelayInvest ExpectNewLoan ExpectLoanLessthan3000 Expectloan3001to5000 FebProfits JanProfits MissFebProf MissJanProf  anypaidworker ageofbusiness manufacture retail totalassets completedhighschool ageofowner
mat colnames y = "Control" "Treatment" "TTest p-value"
mat2txt, matrix(y) saving("Output/Table1Paper.xls") replace


*** Table 2: Perceptions of Constraints to Business Growth
forval x= 1/21 {
gen xa_`x'=b_1102_`x'==3 |b_1102_`x'==4 if  b_1101_`x'~=.
 }
 
 mat y = J(21,1,.)
 local j=1
 foreach var of varlist xa_1-xa_21 {
  sum `var'
  mat y[`j',1]=r(mean)
local j=`j'+1
}
mat rownames y = "Telecommunications" "Electricity" "Transportation" "Water" "Access to land" "Price of land" "Regulatory policy uncertainty" "Tax rates" "Tax administration" "Customs and trade regulations" "Labor regulations" "Skill and education of workers" "Licensing and operating permits" "Political instability" "Access to financing" "Cost of finance" "Macroeconomic uncertainty" "Corruption" "Illegal competition" "Legal system" "Smuggling/dumping"
mat colnames y = "Proportion"
mat2txt, matrix(y) saving("Output/Table2Paper.xls") replace


**** Table 3: Macroeconomic Shock Expectations

local variables b_403_4A b_403_4B b_403_4C b_403_4D b_403_4E b_403_4F
local variables2 m_515_1 m_515_2 m_515_3 m_515_4 m_515_5 m_515_6
local j_rows=7
local k_columns=4

mat m = J(`j_rows',`k_columns',.)

local y=1

foreach var of varlist `variables' {
	sum `var', d
	mat m[`y',1] = round(r(mean),.1)
	mat m[`y',2] = round(r(sd),.1)
	local y=`y'+1
	}
	
	sum x1, d
	mat m[`y',1] = round(r(mean),.1)
    mat m[`y',2] = round(r(sd),.1)
local y=1
	
foreach var of varlist `variables2' {
	sum `var', d
	mat m[`y',3] = round(r(mean),.1)
	mat m[`y',4] = round(r(sd),.1)
	local y=`y'+1
	}
	
	sum mx1, d
	mat m[`y',3] = round(r(mean),.1)
	mat m[`y',4] = round(r(sd),.1)
	

mat colnames m = "Baseline Mean" "Baseline SD" "Endline Mean" "Endline SD"
mat rownames m = "Stock" "Inflation" "Benzene 80" "Benzene 90" "LPG" "Curfew" "Any of the above 6"

mat2txt, matrix(m) saving("Output/Table3.xls")  replace


**** Table 4: Insurance Take-up
* take-up among treated
tab epp if treat==1
* take-up among treated who renewed loan
tab epp if treat==1 & admin_loanrenewal==1

replace x1=x1/100

* discounts future by 30% or more
gen discounter=b_406VALU<=350
gen numeracy=( b_1001_1==50)& b_1001_2==83& b_1001_3==64& b_1002_1==93& b_1002_2==86& b_1002_3==79
pca b_1205_8 b_1205_11 b_1205_12 b_1205_15  b_1205_18 b_1205_20
predict wealth

gen noaccesstootherfinance=b_701==2
gen creditconstrained=b_901==2
gen insurance_understand=b_1006==1 & b_1007==2 & b_1008==1
gen riskaverse_ambiguityneutral=x2*x3
rename riskaverse ra_an

est drop _all
* Column 1
dprobit epp   x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained if treat==1, robust
eststo table4a 

* Column 2: add branch effects
xi: dprobit  epp   x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained i.branchid if treat==1, robust
eststo table4b

* Column 3: know what insurance is
dprobit  epp   x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained insurance_understand if treat==1, robust
eststo table4c

* Column 4: ambiguity aversion
dprobit  epp   x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained x3 ra_an if treat==1, robust
eststo table4d

* Column 5: Column 1 conditional on taking a loan
dprobit epp   x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained if treat==1 & admin_loanrenewal==1, robust
eststo table4e 

* Column 5: Column 1 conditional on taking a loan
dprobit epp   x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained if treat==1 & admin_loanrenewal==1, robust
eststo table4e 

* Column 6: Column 2 conditional on taking a loan
dprobit epp   x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained insurance_understand if treat==1 & admin_loanrenewal==1, robust
eststo table4f

#delimit ;
esttab table4a table4b table4c table4d table4e table4f using "Output/Table4.csv", replace depvar legend label nonumbers nogaps
	drop(_Ibranchid* _cons) 
	b(%9.3f) se star(* 0.10 ** 0.05 *** 0.01)
	stats(N, fmt(%9.0g) labels("Sample Size")) 
	title("Table 4: Determinants of Insurance Take-up among Treatment Group") addnotes("""") ;
#delimit cr

	foreach var of varlist x1 x4 x5 discounter x2 x8 x9 admin_numberloans female completedhighschool numeracy wealth noaccesstootherfinance creditconstrained insurance_understand x3 {
	dprobit epp `var' if treat==1, robust
	}

********** Table 5: ITT of treatment effect on loan take-up and loan amount


est drop _all
* Column 1: Whether or not they take a loan
areg admin_loanrenewal treat, robust a(pair)
sum admin_loanrenewal if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table5a

* Column 2: Unconditional size of the loan (LE)
areg m_loanvalue_uncond treat b_base_loanamount, robust a(pair)
sum m_loanvalue_uncond if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table5b

* Column 3: Loan amount conditional on renewal (LE)
areg m_loanvalue_uncond treat b_base_loanamount if admin_loanrenewal==1, robust a(pair)
sum m_loanvalue_uncond if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table5c

** Column 4: Heterogeneity of impact
*** Examining whether effect interacts with likelihood of taking up loan anyway based on predictables
xi: dprobit admin_loanrenewal x1 x2 x4 x5 x6 x7 x8 x9 female i.branchid manufacturing retail admin_numberloans ageofbusiness if treat==0, r
predict probtakeloan, p
gen highchancerenew=probtakeloan>0.8 & probtake~=.
gen treat_highchance=treat*highchancerenew
areg admin_loanrenewal treat treat_highchance highchancerenew, robust a(pair)
sum admin_loanrenewal if treat==0 & e(sample) & highchancerenew==0
estadd scalar mean = r(mean)
eststo table5d

** Column 5: Took a loan from another microfinance organization or bank
gen tookotherloan=m_401_2==1|m_401_4==1|m_401_3==1
tab tookotherloan treat, col
areg tookotherloan treat, robust a(pair)
sum tookotherloan if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table5e

#delimit ;
esttab table5a table5b table5c table5d table5e using "Output/Table5.csv", replace depvar legend label nonumbers nogaps
	drop(_cons) 
	b(%9.3f) se star(* 0.10 ** 0.05 *** 0.01)
	stats(mean N, fmt(%9.3f %9.0g) labels("Control Group Mean" "Sample Size")) 
	title("Table 5: ITT Impacts on Loan Take-up and Loan Size") addnotes("""") ;
#delimit cr

** Version with confidence intervals
#delimit ;
esttab table5a table5b table5c table5d table5e using "Output/Table5ci.csv", replace depvar legend label nonumbers nogaps
	drop(_cons) 
	b(%9.3f) ci 
	stats(mean N, fmt(%9.3f %9.0g) labels("Control Group Mean" "Sample Size")) 
	title("Table 5: ITT Impacts on Loan Take-up and Loan Size") addnotes("""") ;
#delimit cr

*** Appendix 2 Table: probit coefficients on loan renewal among control group
xi: dprobit admin_loanrenewal x1 x2 x4 x5 x6 x7 x8 x9 female i.branchid manufacturing retail admin_numberloans ageofbusiness if treat==0, r
eststo tableapp2
#delimit ;
esttab tableapp2 using "Output/TableApp2.csv", replace depvar legend label nonumbers nogaps
	drop(_cons _Ibranch*) 
	b(%9.3f) se star(* 0.10 ** 0.05 *** 0.01)
	stats(N, fmt(%9.0g) labels("Sample Size")) 
	title("Appendix 2: Predicting Loan Take-up in Control Group") addnotes("""") ;
#delimit cr

*** Figure 2:  CDFs of loan amounts by treatment status
cumul m_loanvalue_uncond if treat==0, gen(cdf1) equal
cumul m_loanvalue_uncond if treat==1, gen(cdf2) equal
label var cdf1 "Control"
label var cdf2 "Treatment"
sort m_loanvalue_uncond
label var m_loanvalue_uncond "Loan Value (LE)"
twoway line cdf1 cdf2 m_loanvalue_uncond if m_loanvalue_uncond<=50000

*** Table 6: Impacts on New Investments

est drop _all
* Column 1: Whether or not they have made a new investment in machinery or equipment
gen makeinvest=m_601_2==1
areg makeinvest treat, robust a(pair)
sum makeinvest if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table6a

* Column 2: Amount Invested in Machinery or Equipment
gen amountinvest=m_601_2VA
areg amountinvest treat, robust a(pair)
sum amountinvest if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table6b

* Column 3: Introduced a new product or service
gen newproduct=m_216==1
areg newproduct treat, robust a(pair)
sum newproduct if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table6c

* Column 4: Started a Second Business
gen secondbus=m_202A>=2 & m_202A<=4 & m_208==1
areg secondbus treat, robust a(pair)
sum secondbus if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table6d

* Column 5- Business inventories and raw materials
gen m_inventories=m_702 if m_702<999998
gen b_inventories=b_509A
areg m_inventories treat b_inventories, robust a(pair)
sum m_inventories if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table6e

* Column 6- Hired a new worker
gen hireworker=m_222_1==1|m_222_2==1
areg hireworker treat, robust a(pair)
sum hireworker if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table6f


#delimit ;
esttab table6a table6b table6c table6d table6e table6f using "Output/Table6.csv", replace depvar legend label nonumbers nogaps
	drop(_cons b_inventories) 
	b(%9.3f) se star(* 0.10 ** 0.05 *** 0.01)
	stats(mean N, fmt(%9.3f %9.0g) labels("Control Mean" "Sample Size")) 
	title("Table 6: Impacts of Insurance on Investment Activities") addnotes("""") ;
#delimit cr


*** Panel B: LATE estimates
** note: iv can't handle all the pair strata dummies, so just control linearly for these variables
est drop _all
* Column 1: Whether or not they have made a new investment in machinery or equipment
xi: ivreg2 makeinvest (epp=treat) x1-x13 female i.branchid, robust 
eststo table6Ba

* Column 2: Amount Invested in Machinery or Equipment
xi: ivreg2 amountinvest (epp=treat) x1-x13 female i.branchid, robust 
eststo table6Bb

* Column 3: Introduced a new product or service
xi: ivreg2 newproduct (epp=treat) x1-x13 female i.branchid, robust 
eststo table6Bc

* Column 4: Started a Second Business
xi: ivreg2 secondbus (epp=treat) x1-x13 female i.branchid, robust 
eststo table6Bd

* Column 5- Business inventories and raw materials
xi: ivreg2 m_inventories (epp=treat)b_inventories x1-x13 female i.branchid, robust 
eststo table6Be

* Column 6- Hired a new worker
xi: ivreg2 hireworker (epp=treat) x1-x13 female i.branchid, robust 
eststo table6Bf


#delimit ;
esttab table6Ba table6Bb table6Bc table6Bd table6Be table6Bf using "Output/Table6B.csv", replace depvar legend label nonumbers nogaps
	drop(_cons) 
	b(%9.3f) se star(* 0.10 ** 0.05 *** 0.01)
	stats(N, fmt( %9.0g) labels("Sample Size")) 
	title("Table 6B: Impacts of Insurance on Investment Activities") addnotes("""") ;
#delimit cr


******************* Table 7: Impacts on Profits and Revenues

est drop _all
* Column 1: Monthly profits (top-coded at 99th percentile) - take average of last three months to reduce noise
for num 1/3: replace m_903_X=. if m_903_X==99997|m_903_X==99998
gen m_profits=(m_903_1+m_903_2+m_903_3)/3
* closed businesses have zero profit
replace m_profits=0 if m_102==2|(m_207==1|m_207==2|m_207==4)

gen b_profits=(b_605_1+b_605_2+b_605_3)/3
gen miss_baseprofits=b_profits==.
replace b_profits=0 if b_profits==.

sum m_profits, d
gen m_prof_cap=m_profits
replace m_prof_cap=r(p99) if m_profits>r(p99) & m_profits~=.

areg m_prof_cap treat b_profits miss_baseprofits, robust a(pair)
sum m_prof_cap if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table7a

* Column 2: Profits above the 95th percentile of control group
sum m_profits, d
gen highprofits=(m_profits>r(p95)) & m_profits~=.
replace highprofits=. if m_profits==.
sum b_profits, d
gen b_highprofits=(b_profits>r(p95)) & b_profits~=.

areg highprofits treat b_highprofits miss_baseprofits, robust a(pair)
sum highprofits if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table7b

* Column 3: Revenue
for num 1/3: replace m_901_X=. if m_901_X==99997|m_901_X==99998
gen m_revenue=(m_901_1+m_901_2+m_901_3)/3
* closed businesses have zero revenue
replace m_revenue=0 if m_102==2|(m_207==1|m_207==2|m_207==4)

gen b_revenue=(b_604_1+b_604_2+b_604_3)/3
gen miss_baserevenue=b_revenue==.
replace b_revenue=0 if b_revenue==.

sum m_revenue, d
gen m_rev_cap=m_revenue
replace m_rev_cap=r(p99) if m_revenue>r(p99) & m_revenue~=.

areg m_rev_cap treat b_revenue miss_baserevenue, robust a(pair)
sum m_rev_cap if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table7c

* Column 4: Revenue above 95th percentile of control group distribution
sum m_revenue, d
gen highrevenue=(m_revenue>r(p95)) & m_revenue~=.
replace highrevenue=. if m_revenue==.
sum b_revenue, d
gen b_highrevenue=(b_revenue>r(p95)) & b_revenue~=.

areg highrevenue treat b_highrevenue miss_baserevenue, robust a(pair)
sum highrevenue if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table7d

* Column 5: Number of employees
gen m_paidemployees=m_223_1+m_223_2
sum m_paidemployees, d
replace m_paidemployees=r(p99) if m_paidemployees>r(p99) & m_paidemployees~=.
replace m_paidemployees=0 if m_102==2|(m_207==1|m_207==2|m_207==4)

areg m_paidemp treat, robust a(pair)
sum m_paidemp if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table7e

** Column 6: Any worker
gen anyworker=m_paidemp>0 
replace anyworker=. if m_paidemp==.
areg anyworker treat, robust a(pair)
sum anyworker if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table7f

* Column 7: Consumption
gen consumption_food=b_1201_1+b_1201_2+ b_1201_3+ b_1201_4+b_1201_5+b_1201_6+b_1201_7+b_1201_8+b_1201_9 
gen consumption_monthlyexpenses=b_1202_1+b_1202_2+b_1202_3 +b_1202_4 +b_1202_5 +b_1202_6 +b_1202_7 +b_1202_8 +b_1202_9 +b_1202_10 +b_1202_11 +b_1202_12
gen consumption_yearlyexpenses= b_1203_1 +b_1203_2 +b_1203_3 +b_1203_4 +b_1203_5 +b_1203_6 +b_1203_7 +b_1203_8 +b_1203_9 +b_1203_10

foreach var of varlist consumption_food consumption_monthlyexpenses consumption_yearlyexpenses {
sum `var', d
replace `var'=r(p99) if `var'>r(p99) & `var'~=.
}
gen totalconsumption_monthly=consumption_food*4 + consumption_m + consumption_y/12

gen m_consumption_avg=52*m_1301_10+12*m_1302_12+2*m_1303_11 if m_1301_10<3000 & m_1302_12<9500 & m_1303_11<250000
egen m_consumption_avg95= pctile(m_consumption_avg), p(95)
cap drop  m_consumption_avg_cap
gen m_consumption_avg_cap= m_consumption_avg if m_consumption_avg<=m_consumption_avg95
replace m_consumption_avg_cap= m_consumption_avg95 if m_consumption_avg95 < m_consumption_avg
replace m_consumption_avg_cap=m_consumption_avg_cap/12

areg m_consumption_avg_cap treat totalconsumption_monthly, robust a(pair)
sum m_consumption_avg_cap if treat==0 & e(sample)
estadd scalar mean = r(mean)
eststo table7g


#delimit ;
esttab table7a table7b table7c table7d table7e table7f table7g using "Output/Table7.csv", replace depvar legend label nonumbers nogaps
	drop(_cons b_*) 
	b(%9.3f) se star(* 0.10 ** 0.05 *** 0.01)
	stats(mean N, fmt(%9.3f %9.0g) labels("Control Mean" "Sample Size")) 
	title("Table 7: Impacts of Insurance on Profits and Revenues") addnotes("""") ;
#delimit cr

**** Panel B: LATE estimates
est drop _all
xi: ivreg2 m_prof_cap (epp=treat) x1-x13 female i.branchid b_profits miss_baseprofits, robust 
eststo table7Ba
xi: ivreg2 highprofits (epp=treat) x1-x13 female i.branchid b_highprofits miss_baseprofits, robust 
eststo table7Bb
xi: ivreg2 m_rev_cap (epp=treat) x1-x13 female i.branchid b_revenue miss_baserevenue, robust
eststo table7Bc
xi: ivreg2 highrevenue (epp=treat) x1-x13 female i.branchid b_highrevenue miss_baserevenue, robust 
eststo table7Bd
xi: ivreg2 m_paidemp (epp=treat) x1-x13 female i.branchid, robust 
eststo table7Be
xi: ivreg2 m_paidemp (epp=treat) x1-x13 female i.branchid, robust 
eststo table7Bf
xi: ivreg2 m_consumption_avg_cap (epp=treat) x1-x13 female i.branchid totalconsumption_monthly, robust 
eststo table7Bg

#delimit ;
esttab table7Ba table7Bb table7Bc table7Bd table7Be table7Bf table7Bg using "Output/Table7B.csv", replace depvar legend label nonumbers nogaps
	drop(_cons b_*) 
	b(%9.3f) se star(* 0.10 ** 0.05 *** 0.01)
	stats(N, fmt( %9.0g) labels("Sample Size")) 
	title("Table 7B: LATE Impacts of Insurance on Profits and Revenues") addnotes("""") ;
#delimit cr

*** Appendix 3: What is going on with revenue? 
cumul m_rev_cap if treat==1, gen(revtreat)
cumul m_rev_cap if treat==0, gen(revcontrol)
label var revtreat "Treatment"
label var revcontrol "Control"
label var m_rev_cap "Monthly Revenue (LE)"
sort m_rev_cap
twoway line revtreat revcontrol m_rev_cap

cumul m_prof_cap if treat==1, gen(proftreat)
cumul m_prof_cap if treat==0, gen(profcontrol)
label var proftreat "Treatment"
label var profcontrol "Control"
label var m_prof_cap "Monthly Profits (LE)"
sort m_prof_cap
twoway line proftreat profcontrol m_prof_cap


*** Testing whether expectations change
areg m_516 treat, r a(pair)

*** Heterogeneity of effects with respect to understanding of insurance
gen treat_insuranceunderstand=treat*insurance_understand

* Loan take-up
areg admin_loanrenewal treat treat_insurance insurance_understand, robust a(pair)
areg m_loanvalue_uncond treat treat_insurance insurance_understand, robust a(pair)
areg makeinvest treat treat_insurance insurance_understand, robust a(pair)
areg m_prof_cap treat  treat_insurance insurance_understand, robust a(pair)

**********Table 8:  How loan is spent
for num 1/6: gen perspentX=m_404_X*100/m_404_7

mat m=J(6,3,.)
local y=1
foreach var of varlist perspent1-perspent6 {
sum `var' if treat==0 & m_401_1==1
mat m[`y',1]=round(r(mean),.1)
sum `var' if treat==1 & m_401_1==1
mat m[`y',2]=round(r(mean),.1)
areg `var' treat if  m_401_1==1, a(pair) r
test treat==0
mat m[`y',3]=round(r(p),.001)
	local y=`y'+1
	}
	
mat colnames m = "Control Mean" "Treatment Mean" "p-value"
mat rownames m = "Equipment" "Inventories" "Education" "Household Needs" "Weddings" "Other"

mat2txt, matrix(m) saving("Output/Table8.xls")  replace