/***************************************************************************** This do file extracts treatment assignments for all households at endline, and identifies attrited households and household added after baseline Updated on 10/12/2022 *****************************************************************************/ *** ENDLINE STATUS - ALL HOUSEHOLDS ** import excel "$admin_raw/Analisis_Base_evaluacion_SPOON-Mario_anon.xlsx", sheet("analisis") cellrange(A1:H1763) firstrow case(lower) clear rename f rand_num rename g hh_status rename idunico folio rename tratamiento treat_endline save "$el_processed/endline_status.dta", replace ** only new households ** keep if hh_status == "Casos nueva aleatorización" | hh_status =="Se crea por division de un hogar" save "${el_processed}/new_households.dta", replace * GET NEW PARTICIPANTS TREATMENT ASSIGNMENT import excel "$admin_raw/20190721-Second_Cohort_Random_Assignment.xlsx", sheet("Listado de participantes nuevas") cellrange(A7:R94) firstrow case(lower) clear rename tratamientovitalito1recibe new_treat rename numeroaleatorio rand_num keep new_treat rand_num save "${el_processed}/rand_num.dta", replace merge 1:1 rand_num using "${el_processed}/new_households.dta", nogen tab new_treat treat_endline // all in T1 and T2 (see treat_endline), 43 T1, 42 T2 drop if folio==. // 2 households in this data set that aren't in the other so we don't have a household ID for them... save "${el_processed}/new_household_assignments.dta", replace ** TREATMENT STATUS use "${el_raw}/sec_hogar.dta", clear rename folio idunico merge 1:1 idunico using "${bl_processed}/household.dta", gen(bl_merge) keepusing (T1 T2 Control idunico tratamiento_hogar interview_date population id_comunidad comunidad_elegible) rename idunico folio //treat_merge==1 are endline only (93) //treat_merge==2 are baseline only (59) //treat_merge==3 are matched (1,578) * 64 "comunidad" missing order folio comunidad id_comunidad * update comunidad with baseline if missing tostring id_comunidad, replace // add the extra digit in front so that it matches with the others replace comunidad="8102" if id_comunidad=="102" & comunidad=="" replace comunidad="8108" if id_comunidad=="108" & comunidad=="" replace comunidad="8128" if id_comunidad=="128" & comunidad=="" replace comunidad="5135" if id_comunidad=="135" & comunidad=="" replace comunidad="5178" if id_comunidad=="178" & comunidad=="" replace comunidad="2198" if id_comunidad=="198" & comunidad=="" replace comunidad="2241" if id_comunidad=="241" & comunidad=="" replace comunidad="2301" if id_comunidad=="301" & comunidad=="" replace comunidad="3355" if id_comunidad=="355" & comunidad=="" replace comunidad="6036" if id_comunidad=="36" & comunidad=="" replace comunidad="1393" if id_comunidad=="393" & comunidad=="" replace comunidad="7450" if id_comunidad=="450" & comunidad=="" replace comunidad="7466" if id_comunidad=="466" & comunidad=="" replace comunidad="4486" if id_comunidad=="486" & comunidad=="" replace comunidad="4499" if id_comunidad=="499" & comunidad=="" replace comunidad="4512" if id_comunidad=="512" & comunidad=="" replace comunidad="4523" if id_comunidad=="523" & comunidad=="" replace comunidad="6006" if id_comunidad=="6" & comunidad=="" replace comunidad="4626" if id_comunidad=="626" & comunidad=="" replace comunidad="4641" if id_comunidad=="641" & comunidad=="" merge 1:1 folio using "${el_processed}/endline_status.dta", keepusing(hh_status treat_endline) // 32 in using that are not in either dataset ?? drop if _merge==2 drop _merge * indicator for baseline ONLY households gen bl_only=(bl_merge==2) tab hh_status if bl_only==1 // these match up -- 58 not in endline data, one merged with another household *indicator for endline only households gen el_only=(bl_merge==1) tab hh_status if el_only==1, m // 85 new, 1 split from another household, 1 says "aclarar", 6 with no explanataion *endline total households: 1671 *survey result: 1289/1671 completed. 40 partial. 129 rejected. 114 moved. 29 died. tab resultado * kid not living there: 138 households -- 86 moved, 32 died, some others... tab q00_22 tab q00_23 *create variable for attrition gen attrited =(bl_only==1 | (resultado!=1 & resultado!=2)) *replace attrited=. if el_only==1 // don't put endline only households in this // 342 attrited * add treatment assignment for new households merge 1:1 folio using "${el_processed}/new_household_assignments.dta", nogen * update treatment status for new households (85) tab new_treat tab new_treat if el_only==1, m // add in status for 8 missing. Mario's email Oct 6, 2022: replace new_treat=1 if folio==1080022 | folio==1780112 | folio== 3550012 & new_treat==. replace new_treat=0 if folio==1351042 | folio==4650999 & new_treat==. * 1090252 and 3590072 are control... add later // 1==T1 (SQ-LNS) // 0==T2 (Chispitas) replace Control=1 if folio==1090252 | folio==3590072 & new_treat==. replace Control=0 if Control==. & el_only==1 replace T1=1 if new_treat==1 replace T1=0 if T1==. & el_only==1 replace T2=1 if new_treat==0 replace T2=0 if T2==. & el_only==1 replace tratamiento_hogar=1 if new_treat==1 replace tratamiento_hogar=2 if new_treat==0 replace tratamiento_hogar=3 if Control==1 & el_only==1 * save list with ALL households before dropping baseline only preserve keep folio bl_only el_only attrited T1 T2 Control tratamiento_hogar resultado comunidad comunidad_elegible interview_date fecha_ini save "${el_processed}/all_households.dta", replace restore drop if bl_merge==2 // (59 from baseline not in endline) keep folio bl_only el_only T1 T2 Control tratamiento_hogar new_treat interview_date population attrited resultado comunidad comunidad_elegible rand_num * save treatment status for endline households save "${el_processed}/endline_treatment.dta", replace