** ADO FILE FOR FISCAL INTERVENTIONS SHEET OF CEQ MASTER WORKBOOK SECTION E ** VERSION AND NOTES (changes between versions described under CHANGES) *! v2.4 26dec2020 For use with Jul 2017 version of CEQ Master Workbook 2017 ** v2.1 02jun2017 For use with Jul 2017 version of CEQ Master Workbook 2017 ** v2.0 23apr2017 For use with Oct 2016 version of CEQ Master Workbook 2017 ** v1.7 06apr2017 For use with Oct 2016 version of CEQ Master Workbook 2017 ** v1.6 03mar2017 For use with Sep 2016 version of CEQ Master Workbook 2016 ** v1.5 06feb2017 For use with Sep 2016 version of CEQ Master Workbook 2016 ** v1.4 12jan2017 For use with Oct 2016 version of CEQ Master Workbook 2016 ** v1.3 30oct2016 For use with Sep 2016 version of CEQ Master Workbook 2016 ** v1.2 01oct2016 For use with Sep 2016 version of CEQ Master Workbook 2016 ** v1.1 30sep2016 For use with Jun 2016 version of CEQ Master Workbook 2016 ** v1.0 23sep2016 For use with Jun 2016 version of CEQ Master Workbook 2016 ** (beta version; please report any bugs), written by Sean Higgins sean.higgins@ceqinstitute.org ** CHANGES ** 03-08-2020 Re-wrote calcultaions of total benefits in LCU and PPP ** 09-06-2020 Added warning on hh level markers ** 23-04-2020 Fixed issue with totals in targets ** 06-01-2017 Add additional options to print meta-information ** 04-23-2017 Change variable used to calculate beneficiary household and direct and indirect beneficiaries ** Change the if condition for target from == 1 to >0 to account for household-level data ** 04-06-2017 Add warning that users need to specify fiscal intervention option for target results ** Fix the too many options issues ** Update target and direct beneficiary matrices ** Update warning messages ** Add flexibility checks ** Add flexibility for hh-level data ** Update calculation matrices ** Remove the temporary variables from the negative tax warning list ** 03-08-2017 Remove the net in-kind transfers as a broad category in accordance with the instruction that users ** supply net in-kind transfer variables to health/education/otherpublic options ** 03-03-2017 Update row alignment ** Add new variable that identifies individuals who are both direct beneficiaries and belong to target population ** 02-06-2017 Add warning and error messages regarding specification of program options ** Add if condition to allow fiscal interventions not to be specified ** Fix issues with db and population estimates ** 01-12-2017 Set the data type of all newly generated variables to be double ** Add a check of the data type of income and fiscal variables and issue a warning if ** they are not double ** 10-30-2016 Fix bug with alltransfersp omitted from the broad categories ** 10-01-2016 Add direct beneficiaries matrix ** 9-30-2016 Change from d1 command to `command' command in warning ** Add warning for negative values of fiscal interventions ** NOTES ** TO DO ************************* ** PRELIMINARY PROGRAMS * ************************* // BEGIN returncol (Higgins 2015) // Returns Excel column corresponding to a number cap program drop returncol program define returncol, rclass confirm integer number `1' mata: st_strscalar("col",numtobase26(`1')) return local col = col end // END returncol ************************ ** ceqcoverage PROGRAM * ************************ ** For sheet E19. Coverage (Target) // BEGIN ceqcoverage (Higgins 2015) capture program drop ceqtarget program define ceqtarget, rclass version 13.0 #delimit ; syntax [using] [if] [in] [pweight/] [, /* INCOME CONCEPTS: */ Market(varname) Mpluspensions(varname) Netmarket(varname) Gross(varname) Taxable(varname) Disposable(varname) Consumable(varname) Final(varname) /* FISCAL INTERVENTIONS: */ Pensions (varlist) DTRansfers (varlist) DTAXes (varlist) CONTribs(varlist) SUbsidies (varlist) INDTAXes (varlist) HEALTH (varlist) EDUCation (varlist) OTHERpublic(varlist) USERFEESHealth(varlist) USERFEESEduc(varlist) USERFEESOther(varlist) /* DIRECT BENEFICIARIES */ RECPensions (varlist) RECDTRansfers (varlist) PAYDTAXes (varlist) PAYCOntribs(varlist) RECSUbsidies (varlist) PAYINDTAXes (varlist) RECHEALTH (varlist) RECEDUCation (varlist) RECOTHERpublic(varlist) PAYUSERFEESHealth(varlist) PAYUSERFEESEduc(varlist) PAYUSERFEESOther(varlist) /* TARGET BENEFICIARIES */ TRECPensions (varlist) TRECDTRansfers (varlist) TPAYDTAXes (varlist) TPAYCOntribs(varlist) TRECSUbsidies (varlist) TPAYINDTAXes (varlist) TRECHEALTH (varlist) TRECEDUCation (varlist) TRECOTHERpublic(varlist) TPAYUSERFEESHealth(varlist) TPAYUSERFEESEduc(varlist) TPAYUSERFEESOther(varlist) /* PPP CONVERSION */ PPP(real -1) CPISurvey(real -1) CPIBase(real -1) YEARly MOnthly DAily /* SURVEY INFORMATION */ HHid(varname) HSize(varname) PSU(varname) Strata(varname) /* GROUP CUTOFFS */ cut1(real 1.90) cut2(real 3.20) cut3(real 5.50) cut4(real 11.50) cut5(real 57.50) /* INFORMATION CELLS */ COUNtry(string) SURVeyyear(string) /* string because could be range of years */ AUTHors(string) BASEyear(real -1) SCENario(string) GROUp(string) PROJect(string) /* OTHER OPTIONS */ /* VARIABLE MODIFICATON */ IGNOREMissing /* ALLOWS FOR MORE OPTIONS */ * ] ; #delimit cr // The below code gets around the too many options issue if "`exp'"!="" local pw "[`weight'=`exp']" // tp get around Stata option limit if `"`using'"'!="" local using "`using'" local 0 `"`using' `if' `in' `pw', `options'"' //" #delimit ; syntax [if] [in] [using/] [pweight/] [, /* EXPORTING TO CEQ MASTER WORKBOOK: */ OPEN sheetm(string) sheetmp(string) sheetn(string) sheetg(string) sheett(string) sheetd(string) sheetc(string) sheetf(string) ] ; #delimit cr *********** ** LOCALS * *********** ** general programming locals local dit display as text in smcl local die display as error in smcl local command ceqtarget local version 2.4 `dit' "Running version `version' of `command' on `c(current_date)' at `c(current_time)'" _n " (please report this information if reporting a bug to sean.higgins@ceqinstitute.org)" ** income concepts local m `market' local mp `mpluspensions' local n `netmarket' local g `gross' local t `taxable' local d `disposable' local c `consumable' local f `final' local alllist m mp n g t d c f local origlist m mp n g d tokenize `alllist' // so `1' contains m; to get the variable you have to do ``1'' local varlist "" local counter = 1 foreach y of local alllist { local varlist `varlist' ``y'' // so varlist has the variable names // reverse tokenize: local _`y' = `counter' // so _m = 1, _mp = 2 (regardless of whether these options included) local ++counter } scalar _d_m = "Market Income" scalar _d_mp = "Market Income + Pensions" scalar _d_n = "Net Market Income" scalar _d_g = "Gross Income" scalar _d_t = "Taxable Income" scalar _d_d = "Disposable Income" scalar _d_c = "Consumable Income" scalar _d_f = "Final Income" local d_m "Market Income" local d_mp "Market Income + Pensions" local d_n "Net Market Income" local d_g "Gross Income" local d_t "Taxable Income" local d_d "Disposable Income" local d_c "Consumable Income" local d_f "Final Income" ************************ ** PRESERVE AND MODIFY * ************************ preserve if wordcount("`if' `in'")!=0 quietly keep `if' `in' ** print warning messages local warning "Warnings" ** make sure all newly generated variables are in double format set type double ** transfer and tax categories local taxlist dtaxes contribs indtaxes local transferlist pensions dtransfers subsidies health education otherpublic local programlist pensions dtransfers dtaxes contribs subsidies indtaxes health education otherpublic userfeeshealth userfeeseduc userfeesother /// /* nethealth neteducation netother */ local rec_options /// pensions dtransfers subsidies health education otherpublic local pay_options /// dtaxes contribs indtaxes userfeeshealth userfeeseduc userfeesother local program_options /// the ones given in the options pensions dtransfers dtaxes contribs subsidies indtaxes /// health education otherpublic userfeeshealth userfeeseduc userfeesother foreach x of local programlist { local allprogs `allprogs' ``x'' // so allprogs has the actual variable names } // move the broad category up so that we can create broad category variables for DB before collapsing ** columns including disaggregated components and broader categories // Removed `' because it was causing errors afterwards - Apr 22nd, 2020 local broadcats dtransfersp dtaxescontribs inkind userfees /*netinkind*/ alltaxes alltaxescontribs alltransfers alltransfersp local dtransfersp pensions dtransfers local dtaxescontribs dtaxes contribs local inkind health education otherpublic // these contain the variables, or blank if not specified local userfees userfeeshealth userfeeseduc userfeesother /*local netinkind `nethealth' `neteducation' `netother'*/ local alltransfers dtransfers subsidies inkind /*`userfees' */ local alltransfersp pensions dtransfers subsidies inkind /* `userfees' */ local alltaxes dtaxes indtaxes // user fees are not included as tax local alltaxescontribs dtaxes contribs indtaxes ** locals to produce broad categories for direct beneficiaries (because we need to add pay/rec prefix to the items in locals) local dtransfersp_n pensions dtransfers local dtaxescontribs_n dtaxes contribs local inkind_n health education otherpublic // these contain the variables, or blank if not specified local userfees_n userfeeshealth userfeeseduc userfeesother /*local netinkind_n nethealth neteducation netother */ local alltransfers_n dtransfers subsidies inkind /* userfees */ local alltransfersp_n pensions dtransfers subsidies inkind /* userfees */ local alltaxes_n dtaxes indtaxes // user fees are not included as tax local alltaxescontribs_n dtaxes contribs indtaxes foreach cat of local programlist { if "``cat''"!="" { tempvar v_`cat' // in the locals section despite creating vars qui gen double `v_`cat''=0 // because necessary for local programcolsc foreach x of local `cat' { qui replace `v_`cat'' = `v_`cat'' + `x' // so e.g. v_dtaxes will be sum of all vars given in dtaxes() option } local v_fiscal `v_fiscal' `v_`cat'' // put the variables in a local so we can keep these variables local v_fiscal_names `v_fiscal_names' v_`cat' // so suppose there are two direct taxes dtr1, dtr2 and two direct taxes dtax1, dtax2 // then `programcols' will be dtr1 dtr2 dtransfers dtax1 dtax2 dtaxes } } foreach bc of local broadcats { //Adjusting for no target markers - Apr 22nd, 2020 if wordcount("``bc''")>0 { // i.e. if any of the options were specified; for bc=inkind this says if any options health education or otherpublic were specified local empty="" // just keeping the categories with variables - August 3rd,2020 foreach var of local `bc'{ if wordcount("``var''")!=0 local empty=`empty'+1 } if "`empty'"!=""{ tempvar v_`bc' qui gen double `v_`bc'' = 0 foreach var of local `bc' { // each element will be blank if not specified foreach varorg of local `var' { if strpos("`pay_options'","`var'") != 0 & "`tpay`var''"!="" qui replace `v_`bc'' = `v_`bc'' + `varorg' if strpos("`pay_options'","`var'") != 0 & "`tpay`var''"=="" qui replace `v_`bc'' = `v_`bc'' if strpos("`rec_options'","`var'") != 0 & "`trec`var''"!="" qui replace `v_`bc'' = `v_`bc'' + `varorg' if strpos("`rec_options'","`var'") != 0 & "`trec`var''"=="" qui replace `v_`bc'' = `v_`bc'' } } local v_broadcat `v_broadcat' `v_`bc'' local v_broadcat_names `v_broadcat_names' v_`bc' } // end if `empty'>0 } } ** weight (if they specified hhsize*hhweight type of thing) if strpos("`exp'","*")> 0 { // TBD: what if they premultiplied w by hsize? `die' "Please use the household weight in {weight}; this will automatically be multiplied by the size of household given by {bf:hsize}" exit } ** hsize and hhid if wordcount("`hsize' `hhid'")!=1 { `die' "Must exclusively specify {bf:hsize} (number of household members for household-level data) or " `die' "{bf:hhid} (unique household identifier for individual-level data)" exit 198 } ** check direct beneficiary variables foreach x in "rec" "pay" { foreach `x'opt of local `x'_options { // get the corresponding option for direct beneficiary local _``x'opt' = "`x'" + "``x'opt'" // e.g. dtaxes would become paydtaxes // check if option used /*if "``_``x'opt'''"=="" { // they didn't specify the DB option if "`t`_``x'opt'''"!="" { // they did specify the corresponding target option // for benefit amounts `dit' "Warning: {bf:``x'opt'} not specified; direct beneficiary results not produced" local warning `warning' "Warning: {bf:``x'opt'} not specified; direct beneficiary results not produced" } } else { // they did specify the option */ // make sure length is the same if ("``_``x'opt'''"!="" | "`t`_``x'opt'''"!="") { foreach jj in ``_``x'opt'''{ if substr("`jj'",length("`jj'")-1,length("`jj'"))=="rh" { //Warning added for markers that only appear at the hh level - June 9, 2020 `dit' "Warning: Direct beneficiary marker {bf:`jj'} is constructed at the household level and assigend to the household head" local warning `warning' "Warning: Direct beneficiary marker `jj' is constructed at the household level and assigend to the household head" } } foreach hh in ``_``x'opt'''{ if substr("`hh'",length("`hh'")-1,length("`hh'"))=="th" { //Warning added for markers that only appear at the hh level - June 9, 2020 `dit' "Warning: Target marker {bf:`hh'} is constructed at the household level and assigend to the household head" local warning `warning' "Warning: Target marker `hh' is constructed at the household level and assigend to the household head" } } } if "``_``x'opt'''"!="" & "`t`_``x'opt'''"!="" { cap assert wordcount("``_``x'opt'''")==wordcount("`t`_``x'opt'''") // changed the latter to t_ if _rc { `die' "Number of variables in {bf:``x'opt'} must be the same as number in {bf:`_``x'opt''} and the variables must be in the same order. If the survey does not contain information on the direct beneficiaries, assign the household head as the direct beneficiaries." exit 198 } } if "```x'opt''"!="" & "`t`_``x'opt'''"!="" { cap assert wordcount("```x'opt''")==wordcount("`t`_``x'opt'''") if _rc { `die' "Number of variables in {bf:``x'opt'} must be the same as number in {bf:t`_``x'opt''} and the variables must be in the same order." exit 198 } } if "`t`_``x'opt'''"!="" & "``_``x'opt'''"=="" { `dit' "Warning: {bf:`_``x'opt''} not specified; target direct beneficiary results not produced" local warning `warning' "Warning: {bf:`_``x'opt''} not specified; target direct beneficiary results not produced" } if "`t`_``x'opt'''"=="" & "```x'opt''"!="" { `dit' "Warning: {bf:t`_``x'opt''} not specified; no results are produced" local warning `warning' "Warning: {bf:t`_``x'opt''} not specified; no results are produced" } if "`t`_``x'opt'''"!="" & "```x'opt''"=="" { `dit' "Warning: {bf:``x'opt'} need to be specified for target results to be produced" local warning `warning' "Warning: {bf:``x'opt'} need to be specified for target results to be produced" } // This loop is to make sure that if we have all target variables corresponding to the benefit variables if "```x'opt''"!="" & "`t`_``x'opt'''"!="" { local `x'list ``x'list' ``x'opt' // the option names local `x'vars1 ``x'vars1' ```x'opt'' // the variable names local tb_`x'list `tb_`x'list' t`_``x'opt'' // option names for target (trec..., tpay...) local tb_`x'vars `tb_`x'vars' `t`_``x'opt''' // variable names for target (trec..., tpay...) } // Separate loop for flexibility if "```x'opt''"!="" & "``_``x'opt'''"!="" & "`t`_``x'opt'''"!="" { local `x'list ``x'list' ``x'opt' // the option names local `x'vars2 ``x'vars2' ```x'opt'' // the variable names local _`x'list `_`x'list' `_``x'opt'' // option names (rec..., pay...) local _`x'vars `_`x'vars' ``_``x'opt''' // the variable names local t`x'list ``x'list' ``x'opt' // the option names local t`x'vars ``x'vars' ```x'opt'' // the variable names local t_`x'list `t_`x'list' t`_``x'opt'' // option names for target (trec..., tpay...) local t_`x'vars `t_`x'vars' `t`_``x'opt''' // variable names for target (trec..., tpay...) } } } ** Generate the variables for benefits tokenize `tb_recvars' local ben_count = 0 foreach var in `recvars1' { local ++ben_count tempvar tarbtemp_`var' qui gen `tarbtemp_`var'' = ``ben_count'' } tokenize `tb_payvars' local ben_count = 0 foreach var in `payvars1' { local ++ben_count tempvar tarbtemp_`var' qui gen `tarbtemp_`var'' = ``ben_count'' } ** Generate the variable for target direct beneficiaries // Direct beneficiaries tokenize `_recvars' local db_count = 0 foreach var in `recvars2' { local ++db_count tempvar dbc_`var' qui gen `dbc_`var'' = ``db_count'' tempvar bdt_temp_`var' qui gen `bdt_temp_`var'' = 0 qui replace `bdt_temp_`var'' = 1 if ``db_count''>0 & !missing(``db_count'') } // Note if there are missing values for the variable ``db_count'' we will consider it to be 0 tokenize `_payvars' local db_count = 0 foreach var in `payvars2' { local ++db_count tempvar dbc_`var' qui gen `dbc_`var'' = ``db_count'' tempvar bdt_temp_`var' qui gen `bdt_temp_`var'' = 0 qui replace `bdt_temp_`var'' = 1 if ``db_count''>0 & !missing(``db_count'') } // Target beneficiaries tokenize `t_recvars' local tar_count = 0 foreach var in `recvars2' { local ++tar_count tempvar tarc_`var' qui gen `tarc_`var'' = ``tar_count'' qui replace `bdt_temp_`var'' = 0 if ``tar_count''==0 | missing(``tar_count'') // make sure the missing values are converted to 0 local bdtc_`var' `bdtc_`var'' `bdt_temp_`var'' } tokenize `t_payvars' local tar_count = 0 foreach var in `payvars2' { local ++tar_count tempvar tarc_`var' qui gen `tarc_`var'' = ``tar_count'' qui replace `bdt_temp_`var'' = 0 if ``tar_count''==0 | missing(``tar_count'') local bdtc_`var' `bdtc_`var'' `bdt_temp_`var'' } /*tokenize `recvars' `payvars' local db_count = 0 foreach var in `_recvars' `_payvars' { local ++db_count local db_`var' ``db_count'' } */ ** generate db variables if "`hsize'"!= "" { local db_result = 0 // not produce db results for hh-level data `dit' "Warning: Household-level data used, but individual-level data strongly recommended for {cmd:ceqtarget}. Household-level data should only be used if each program's target population defined at household level. Individual-level results are not produced." local warning `warning' "Warning: Household-level data used, but individual-level data strongly recommended for {cmd:ceqtarget}. Household-level data should only be used if each program's target population defined at household level. Individual-level results are not produced." /*foreach var in `recvars' `payvars' { tempvar db_`var' qui gen `db_`var'' = `dbc_`var'' local db_tokeep `db_tokeep' `db_`var'' }*/ } ** create individual-level direct beneficiary vars // else { // if "`hsize'"=="" /*local uniq_recpayvars `_recvars' `_payvars' local uniq_recpayvars : list uniq uniq_recpayvars foreach var of local uniq_recpayvars { tempvar temp`var' qui bys `hhid' : egen `temp`var'' = total(`var') qui replace `var' = `temp`var'' }*/ foreach cat of local programlist { local bdttotal // empty the locals before each loop if strpos("`pay_options'","`cat'") != 0 { // Taxes paid by Target Population if "`tpay`cat''"!="" { if "``cat''"!="" { tempvar tarb_`v_`cat'' local tpaytotalb foreach x in `tpay`cat'' { if wordcount("`tpaytotalb'") > 0 local tpaytotalb `tpaytotalb' ,`x' else local tpaytotalb `x' } if strpos("`tpaytotalb'",",")!=0 { qui gen double `tarb_`v_`cat''' = max(`tpaytotalb') } else { qui gen double `tarb_`v_`cat''' = `tpaytotalb' } } if "`pay`cat''"!="" { tempvar tar_`v_`cat'' db_`v_`cat'' local tpaytotal local paytotal foreach x in `tpay`cat'' { if wordcount("`tpaytotal'") > 0 local tpaytotal `tpaytotal' ,`x' else local tpaytotal `x' } if strpos("`tpaytotal'",",")!=0 { qui gen double `tar_`v_`cat''' = max(`tpaytotal') } else { qui gen double `tar_`v_`cat''' = `tpaytotal' } foreach x in `pay`cat'' { if wordcount("`paytotal'") > 0 local paytotal `paytotal' ,`x' else local paytotal `x' } if strpos("`paytotal'",",")!=0 { qui gen double `db_`v_`cat''' = max(`paytotal') // e.g. var would be `db_v_pensions' so we can } // be sure each variable is distinct even if users specify multiple options with the same variable else { qui gen double `db_`v_`cat''' = `paytotal' } foreach var in ``cat'' { if wordcount("`bdttotal'")>0 local bdttotal `bdttotal', `bdt_temp_`var'' else local bdttotal `bdt_temp_`var'' } // Identify individuals who are both direct beneficiaries and target population if "`bdttotal'"!="" { tempvar bdt_`v_`cat'' if strpos("`bdttotal'",",")!=0 qui gen `bdt_`v_`cat''' = max(`bdttotal') if strpos("`bdttotal'",",")==0 qui gen `bdt_`v_`cat''' = `bdttotal' } // qui gen `bdt_`v_`cat''' = 0 // qui replace `bdt_`v_`cat''' = 1 if `db_`v_`cat'''==1 & `tar_`v_`cat''' == 1 // target beneficiary for broad category if target for } // at least one component of broad category } } if strpos("`rec_options'","`cat'") != 0 { if "`trec`cat''"!="" { if "``cat''"!="" { tempvar tarb_`v_`cat'' local trectotalb foreach x in `trec`cat'' { if wordcount("`trectotalb'") > 0 local trectotalb `trectotalb' ,`x' else local trectotalb `x' } if strpos("`trectotalb'",",")!=0 { qui gen double `tarb_`v_`cat''' = max(`trectotalb') } else { qui gen double `tarb_`v_`cat''' = `trectotalb' } } if "`rec`cat''"!="" { tempvar tar_`v_`cat'' db_`v_`cat'' local trectotal local rectotal foreach x in `trec`cat'' { if wordcount("`trectotal'") > 0 local trectotal `trectotal' ,`x' else local trectotal `x' } if strpos("`trectotal'",",")!=0 { qui gen double `tar_`v_`cat''' = max(`trectotal') } else { qui gen double `tar_`v_`cat''' = `trectotal' } foreach x in `rec`cat'' { if wordcount("`rectotal'") > 0 local rectotal `rectotal' ,`x' else local rectotal `x' } if strpos("`rectotal'",",")!=0 { qui gen double `db_`v_`cat''' = max(`rectotal') // e.g. var would be `db_v_pensions' so we can } // be sure each variable is distinct even if users specify multiple options with the same variable else { qui gen double `db_`v_`cat''' = `rectotal' } foreach var in ``cat'' { if wordcount("`bdttotal'")>0 local bdttotal `bdttotal', `bdt_temp_`var'' else local bdttotal `bdt_temp_`var'' } // Identify individuals who are both direct beneficiaries and target population if "`bdttotal'"!="" { tempvar bdt_`v_`cat'' if strpos("`bdttotal'",",")!=0 qui gen `bdt_`v_`cat''' = max(`bdttotal') if strpos("`bdttotal'",",")==0 qui gen `bdt_`v_`cat''' = `bdttotal' } // Identify individuals who are both direct beneficiaries and target population // qui gen `bdt_`v_`cat''' = 0 // qui replace `bdt_`v_`cat''' = 1 if `db_`v_`cat'''==1 & `tar_`v_`cat''' == 1 // target beneficiary for broad category if target for } // at least one component of broad category } } local db_vlist `db_vlist' `db_`v_`cat''' local tar_vlist `tar_vlist' `tar_`v_`cat''' local bdt_vlist `bdt_vlist' `bdt_`v_`cat''' local tarb_vlist `tarb_vlist' `tarb_`v_`cat''' //Reseting locals - Apr 22nd, 2020 local tpaytotalb= "" local bdttotal= "" local trectotalb= "" local trectotal= "" local paytotal= "" } foreach bc of local broadcats { if wordcount("``bc''")>0 { local payid = 0 local recid = 0 tempvar bdt_`v_`bc'' qui gen double `bdt_`v_`bc''' = 0 /* #delimit ; local tpaybroadb ; local tpaybroad ; local paybroad ; local trecbroadb ; local trecbroad ; local recbroad ; #delimit cr */ foreach cat of local `bc'_n { // so `cat' would be pensions, dtransfers, etc. if strpos("`pay_options'","`cat'") != 0 { if "`tpay`cat''"!="" { local payid = 1 if "``cat''"!="" { tempvar tarb_`v_`bc'' local tpaytotalb foreach x in `tpay`cat'' { if wordcount("`tpaybroadb'") > 0 local tpaybroadb `tpaybroadb' ,`x' else local tpaybroadb `x' } } if "`pay`cat''"!="" { tempvar db_`v_`bc'' tar_`v_`bc'' local tpaytotal local paytotal foreach x in `tpay`cat'' { if wordcount("`tpaybroad'") > 0 local tpaybroad `tpaybroad' ,`x' else local tpaybroad `x' } foreach x in `pay`cat'' { if wordcount("`paybroad'") > 0 local paybroad `paybroad' ,`x' else local paybroad `x' } if "`bdt_`v_`cat'''"!="" { if wordcount("`bdtbroad'")>0 local bdtbroad `bdtbroad', `bdt_`v_`cat''' else local bdtbroad `bdt_`v_`cat''' } } } } if strpos("`rec_options'","`cat'") != 0 { if "`trec`cat''"!="" { local recid = 1 if "``cat''"!="" { tempvar tarb_`v_`bc'' local trectotalb foreach x in `trec`cat'' { if wordcount("`trecbroadb'") > 0 local trecbroadb `trecbroadb' ,`x' else local trecbroadb `x' } } if "`rec`cat''"!="" { tempvar db_`v_`bc'' tar_`v_`bc'' local trectotal local rectotal foreach x in `trec`cat'' { if wordcount("`trecbroad'") > 0 local trecbroad `trecbroad' ,`x' else local trecbroad `x' } foreach x in `rec`cat'' { if wordcount("`recbroad'") > 0 local recbroad `recbroad' ,`x' else local recbroad `x' } if "`bdt_`v_`cat'''"!="" { if wordcount("`bdtbroad'")>0 local bdtbroad `bdtbroad', `bdt_`v_`cat''' else local bdtbroad `bdt_`v_`cat''' } } } } } // Direct beneficiaries if "`paybroad'"!="" & "`db_`v_`bc'''"!="" { if `payid' == 1 & strpos("`paybroad'",",")!=0 qui gen `db_`v_`bc''' = max(`paybroad') if `payid' == 1 & strpos("`paybroad'",",")==0 qui gen `db_`v_`bc''' = `paybroad' } if "`recbroad'"!="" & "`db_`v_`bc'''"!="" { if `recid' == 1 & strpos("`recbroad'",",")!=0 qui gen `db_`v_`bc''' = max(`recbroad') if `recid' == 1 & strpos("`recbroad'",",")==0 qui gen `db_`v_`bc''' = `recbroad' } // Target beneficiaries (for target and direct beneficiaries) if "`tpaybroad'"!="" & "`tar_`v_`bc'''"!="" { if `payid' == 1 & strpos("`tpaybroad'",",")!=0 qui gen `tar_`v_`bc''' = max(`tpaybroad') if `payid' == 1 & strpos("`tpaybroad'",",")==0 qui gen `tar_`v_`bc''' = `tpaybroad' } if "`trecbroad'"!="" & "`tar_`v_`bc'''"!="" { if `recid' == 1 & strpos("`trecbroad'",",")!=0 qui gen `tar_`v_`bc''' = max(`trecbroad') if `recid' == 1 & strpos("`trecbroad'",",")==0 qui gen `tar_`v_`bc''' = `trecbroad' } // Target beneficiaries (for benefits in LCU and PPP) if "`tpaybroadb'"!="" & "`tarb_`v_`bc'''"!="" { if `payid' == 1 & strpos("`tpaybroadb'",",")!=0 qui gen `tarb_`v_`bc''' = max(`tpaybroadb') if `payid' == 1 & strpos("`tpaybroadb'",",")==0 qui gen `tarb_`v_`bc''' = `tpaybroadb' } if "`trecbroadb'"!="" & "`tarb_`v_`bc'''"!="" { if `recid' == 1 & strpos("`trecbroadb'",",")!=0 qui gen `tarb_`v_`bc''' = max(`trecbroadb') if `recid' == 1 & strpos("`trecbroadb'",",")==0 qui gen `tarb_`v_`bc''' = `trecbroadb' } // capture confirm variable `db_`v_`bc''' `tar_`v_`bc''' // if _rc == 0 { if "`bdtbroad'"!="" { if strpos("`bdtbroad'",",")!=0 qui replace `bdt_`v_`bc''' = max(`bdtbroad') // 1 if `db_`v_`bc'''==1 & `tar_`v_`bc'''==1 if strpos("`bdtbroad'",",")==0 qui replace `bdt_`v_`bc''' = `bdtbroad' } // } local db_vlist `db_vlist' `db_`v_`bc''' local tar_vlist `tar_vlist' `tar_`v_`bc''' local bdt_vlist `bdt_vlist' `bdt_`v_`bc''' local tarb_vlist `tarb_vlist' `tarb_`v_`bc''' //Reseting locals - Apr 22nd, 2020 local trecbroadb= "" local tpaybroad= "" local bdtbroad= "" } } // } ** Keep the variables if "`hsize'"!="" { foreach var in `recvars1' `payvars1' { tempvar tarb_`var' qui gen `tarb_`var'' = `tarbtemp_`var'' // counts number of direct ben local tarb_tokeep `tarb_tokeep' `tarb_`var'' } foreach var in `recvars2' `payvars2' { tempvar db_`var' qui gen `db_`var'' = `dbc_`var'' // counts number of direct ben local db_tokeep `db_tokeep' `db_`var'' } foreach var in `recvars2' `payvars2' { tempvar tar_`var' qui gen `tar_`var'' = `tarc_`var'' // counts number of direct ben local tar_tokeep `tar_tokeep' `tar_`var'' } foreach var in `recvars2' `payvars2' { tempvar bdt_`var' qui gen `bdt_`var'' = `bdtc_`var'' // counts number of direct ben local bdt_tokeep `bdt_tokeep' `bdt_`var'' } // Broad Categories foreach var of local tarb_vlist { /*tempvar temp`var' qui gen `temp`var'' = `var' qui replace `var' = `temp`var'' */ // for hh-level we just need the variables generated in previous loops local tarb_tokeep `tarb_tokeep' `var' } foreach var of local db_vlist { /*tempvar temp`var' qui gen `temp`var'' = `var' qui replace `var' = `temp`var''*/ local db_tokeep `db_tokeep' `var' } foreach var of local tar_vlist { /*tempvar temp`var' qui by `hhid': egen `temp`var'' = `var' qui replace `var' = `temp`var''*/ local tar_tokeep `tar_tokeep' `var' } foreach var of local bdt_vlist { /*tempvar temp`var' qui by `hhid': egen `temp`var'' = `var' qui replace `var' = `temp`var'' */ local bdt_tokeep `bdt_tokeep' `var' } } ** collapse to hh-level data if "`hsize'"=="" { // i.e., it is individual-level data local db_result = 1 // not produce db results for hh-level data tempvar members sort `hhid', stable qui by `hhid': gen `members' = _N // # members in hh // Created individual-level direct beneficary and target population vars before collapsing ** create individual-level and direct beneficiary vars // Individual programs foreach var in `recvars1' `payvars1' { tempvar tarb_`var' qui by `hhid' : egen `tarb_`var'' = total(`tarbtemp_`var'') // counts number of targets per hh local tarb_tokeep `tarb_tokeep' `tarb_`var'' } foreach var in `recvars2' `payvars2' { tempvar db_`var' qui by `hhid' : egen `db_`var'' = total(`dbc_`var'') // counts number of direct ben per hh local db_tokeep `db_tokeep' `db_`var'' } foreach var in `recvars2' `payvars2' { tempvar tar_`var' qui by `hhid' : egen `tar_`var'' = total(`tarc_`var'') // counts number of targets per hh local tar_tokeep `tar_tokeep' `tar_`var'' } foreach var in `recvars2' `payvars2' { tempvar bdt_`var' qui by `hhid' : egen `bdt_`var'' = total(`bdtc_`var'') // counts number of direct ben per hh local bdt_tokeep `bdt_tokeep' `bdt_`var'' } // Broad Categories foreach var of local tarb_vlist { tempvar temp`var' qui by `hhid': egen `temp`var'' = total(`var') // counts number of members who are target for at least one program in bc per hh qui replace `var' = `temp`var'' local tarb_tokeep `tarb_tokeep' `var' } foreach var of local db_vlist { tempvar temp`var' qui by `hhid': egen `temp`var'' = total(`var') // counts number of members who are direct beneficiaries for at least one program in bc per hh qui replace `var' = `temp`var'' local db_tokeep `db_tokeep' `var' } foreach var of local tar_vlist { tempvar temp`var' qui by `hhid': egen `temp`var'' = total(`var') // counts number of members who are target for at least one program in bc per hh qui replace `var' = `temp`var'' local tar_tokeep `tar_tokeep' `var' } foreach var of local bdt_vlist { tempvar temp`var' qui by `hhid': egen `temp`var'' = total(`var') // counts number of members who are direct beneficiaries for at least one program in bc per hh qui replace `var' = `temp`var'' local bdt_tokeep `bdt_tokeep' `var' } qui bys `hhid': drop if _n>1 // faster than duplicates drop local hsize `members' ** Check if all income and fisc variables are in double format local inctypewarn foreach var of local varlist { if "`var'"!="" { local vartype: type `var' if "`vartype'"!="double" { if wordcount("`inctypewarn'")>0 local inctypewarn `inctypewarn', `var' else local inctypewarn `var' } } } if wordcount("`inctypewarn'")>0 `dit' "Warning: Income variable(s) `inctypewarn' not stored in double format. This may lead to substantial discrepancies in the MWB due to rounding error." if wordcount("`inctypewarn'")>0 local warning `warning' "Warning: Income variable(s) `inctypewarn' not stored in double format. This may lead to substantial discrepancies in the MWB due to rounding error." local fisctypewarn foreach var of local allprogs { if "`var'"!="" { local vartype: type `var' if "`vartype'"!="double" { if wordcount("`fisctypewarn'")>0 local fisctypewarn `fisctypewarn', `var' else local fisctypewarn `var' } } } if wordcount("`fisctypewarn'")>0 `dit' "Warning: Fiscal intervention variable(s) `fisctypewarn' not stored in double format. This may lead to substantial discrepancies in the MWB due to rounding error." if wordcount("`fisctypewarn'")>0 local warning `warning' "Warning: Fiscal intervention variable(s) `fisctypewarn' not stored in double format. This may lead to substantial discrepancies in the MWB due to rounding error." *********************** ** SVYSET AND WEIGHTS * *********************** cap svydes scalar no_svydes = _rc if !_rc qui svyset // gets the results saved in return list if "`r(wvar)'"=="" & "`exp'"=="" { `dit' "Warning: weights not specified in svydes or the command" `dit' "Hence, equal weights (simple random sample) assumed" local warning `warning' "Warning: weights not specified in svydes or the command. Hence, equal weights (simple random sample) assumed." } else { if "`exp'"=="" & "`r(wvar)'"!="" local w `r(wvar)' if "`exp'"!="" local w `exp' if "`w'"!="" { tempvar weightvar qui gen double `weightvar' = `w'*`hsize' local w `weightvar' } else local w "`hsize'" if "`w'"!="" { local pw "[pw = `w']" local aw "[aw = `w']" } if "`exp'"=="" & "`r(wvar)'"!="" { local weight "pw" local exp "`r(wvar)'" } } else if "`r(su1)'"=="" & "`psu'"=="" { di as text "Warning: primary sampling unit not specified in svydes or the `command' command's psu() option" di as text "P-values will be incorrect if sample was stratified" local warning `warning' "Warning: primary sampling unit not specified in svydes or the `command' command's psu() option. P-values will be incorrect if sample was stratified." } if "`psu'"=="" & "`r(su1)'"!="" { local psu `r(su1)' } if "`strata'"=="" & "`r(strata1)'"!="" { local strata `r(strata1)' } if "`strata'"!="" { local opt strata(`strata') } ** now set it: if "`exp'"!="" qui svyset `psu' `pw', `opt' else qui svyset `psu', `opt' ************************** ** VARIABLE MODIFICATION * ************************** #delimit ; local relevar `varlist' `allprogs' `v_fiscal' `v_broadcat' `recvars1' `payvars1' `recvars2' `payvars2' `_recvars' `_payvars' `db_tokeep' `trecvars' `tpayvars' `t_recvars' `t_payvars' `tb_recvars' `tb_payvars' `tar_tokeep' `bdt_tokeep' `tarb_tokeep' `w' `psu' `strata' `hsize' `exp' ; #delimit cr quietly keep `relevar' ** missing income concepts foreach var of local varlist { qui count if missing(`var') if "`ignoremissing'"=="" { if r(N) { `die' "Missing values not allowed; `r(N)' missing values of `var' found" exit 198 } } else { if r(N) { qui drop if missing(`var') `dit' "Warning: `r(N)' observations that are missing `var' were dropped because the user specified {bf:ignoremissing}" local warning `warning' "Warning: `r(N)' observations that are missing `var' were dropped because the user specified the ignoremissing option." } } } ** missing fiscal interventions foreach var of local allprogs { qui count if missing(`var') if "`ignoremissing'"=="" { if r(N) { `die' "Missing values not allowed; `r(N)' missing values of `var' found" `die' "For households that did not receive/pay the tax/transfer, assign 0" exit 198 } } else { if r(N) { qui drop if missing(`var') `dit' "Warning: `r(N)' observations that are missing `var' were dropped because the user specified {bf:ignoremissing}" local warning `warning' "Warning: `r(N)' observations that are missing `var' were dropped because the user specified the ignoremissing option." } } } // This section is moved up so that we can create db broad category estimates BEFORE collapsing ** columns including disaggregated components and broader categories /*local broadcats dtransfersp dtaxescontribs inkind userfees netinkind alltaxes alltaxescontribs alltransfers alltransfersp local paybroad dtaxescontribs userfees alltaxes alltaxescontribs local recbroad dtransfersp inkind netinkind alltransfers alltransfersp local dtransfersp `pensions' `dtransfers' local dtaxescontribs `dtaxes' `contribs' local inkind `health' `education' `otherpublic' // these contain the variables, or blank if not specified local userfees `userfeeshealth' `userfeeseduc' `userfeesother' local netinkind `nethealth' `neteducation' `netother' local alltransfers `dtransfers' `subsidies' `inkind' `userfees' local alltransfersp `pensions' `dtransfers' `subsidies' `inkind' `userfees' local alltaxes `dtaxes' `indtaxes' // user fees are not included as tax local alltaxescontribs `dtaxes' `contribs' `indtaxes' foreach cat of local programlist { if "``cat''"!="" { tempvar v_`cat' // in the locals section despite creating vars qui gen double `v_`cat''=0 // because necessary for local programcolsc foreach x of local `cat' { qui replace `v_`cat'' = `v_`cat'' + `x' // so e.g. v_dtaxes will be sum of all vars given in dtaxes() option } // so suppose there are two direct taxes dtr1, dtr2 and two direct taxes dtax1, dtax2 // then `programcols' will be dtr1 dtr2 dtransfers dtax1 dtax2 dtaxes } } foreach bc of local broadcats { if wordcount("``bc''")>0 { // i.e. if any of the options were specified; for bc=inkind this says if any options health education or otherpublic were specified tempvar v_`bc' qui gen double `v_`bc'' = 0 foreach var of local `bc' { // each element will be blank if not specified qui replace `v_`bc'' = `v_`bc'' + `var' } } } */ ** programs #delimit ; local programcols `pensions' `v_pensions' `dtransfers' `v_dtransfers' `v_dtransfersp' `dtaxes' `contribs' `v_dtaxes' `v_contribs' `v_dtaxescontribs' `subsidies' `v_subsidies' `indtaxes' `v_indtaxes' `v_alltaxes' `v_alltaxescontribs' `health' `v_health' `education' `v_education' `otherpublic' `v_otherpublic' `v_inkind' `userfeeshealth' `v_userfeeshealth' `userfeeseduc' `v_userfeeseduc' `userfeesother' `v_userfeesother' `v_userfees' /*`nethealth' `neteducation' `netother' `v_netinkind'*/ `v_alltransfers' `v_alltransfersp' ; local transfercols `pensions' `v_pensions' `dtransfers' `v_dtransfers' `v_dtransfersp' `subsidies' `v_subsidies' `health' `education' `otherpublic' `v_health' `v_education' `v_otherpublic' `v_inkind' /*`nethealth' `neteducation' `netother' `v_netinkind'*/ `v_alltransfers' `v_alltransfersp' ; local dtransferscols `pensions' `v_pensions' `dtransfers' `v_dtransfers' `v_dtransfersp' ; local dtaxescols `dtaxes' `contribs' `v_dtaxes' `v_contribs' `v_dtaxescontribs' ; local subsidiescols `subsidies' `v_subsidies' ; local indtaxescols `indtaxes' `v_indtaxes' ; local inkindcols `health' `v_health' `education' `v_education' `otherpublic' `v_otherpublic' `v_inkind' `userfeeshealth' `v_userfeeshealth' `userfeeseduc' `v_userfeeseduc' `userfeesother' `v_userfeesother' `v_userfees' /* `v_alltaxes' `v_alltaxescontribs' `v_alltransfers' `v_alltransfersp' `nethealth' `neteducation' `netother' `v_netinkind'*/ ; local taxcols: list programcols - transfercols; // set subtraction; #delimit cr local cols = wordcount("`programcols'") + 1 // + 1 for income column ** labels for fiscal intervention column titles foreach pr of local allprogs { // allprogs has variable names already local d_`pr' : var label `pr' if "`d_`pr''"=="" { // ie, if the var didnt have a label local d_`pr' `pr' `dit' "Warning: variable `pr' not labeled" local warning `warning' "Warning: variable `pr' not labeled." } if strpos("`d_`pr''","(")!=0 { if strpos("`d_`pr''",")")==0 { `die' "`d_`pr'' must have a closed parenthesis" exit 198 } } } local d_`v_pensions' = "All contributory pensions" local d_`v_dtransfers' = "All direct transfers excl contributory pensions" local d_`v_dtransfersp' = "All direct transfers incl contributory pensions" local d_`v_contribs' = "All contributions" local d_`v_dtaxes' = "All direct taxes" local d_`v_dtaxescontribs' = "All direct taxes and contributions" local d_`v_subsidies' = "All indirect subsidies" local d_`v_indtaxes' = "All indirect taxes" local d_`v_health' = "Net health transfers" // net health transfers labeled previously; similar for education and other public transfers local d_`v_education' = "Net education transfers" local d_`v_otherpublic' = "Net other public transfers" // LOH need to fix that this is showing up even when I don't specify the option /* scalar _d_`v_netinkind' = "All net inkind transfers" scalar of specfic net inkind transfers created before */ local d_`v_inkind' = "All net in-kind transfers" local d_`v_userfeeshealth' = "All health user fees" local d_`v_userfeeseduc' = "All education user fees" local d_`v_userfeesother' = "All other user fees" local d_`v_userfees' = "All user fees" local d_`v_alltransfers' = "All net transfers and subsidies excl contributory pensions" local d_`v_alltransfersp' = "All net transfers and subsidies incl contributory pensions" local d_`v_alltaxes' = "All taxes" local d_`v_alltaxescontribs' = "All taxes and contributions" ****************** ** PARSE OPTIONS * ****************** ** ** check if quantiles installed if "`nodecile'"=="" & "`nocentile'"=="" { cap which quantiles if _rc { `die' "{bf:quantiles} not installed; to install: {stata ssc install quantiles:ssc install quantiles}" exit } } ** ado file specific foreach vrank of local alllist { if "`sheet`vrank''"=="" { if "`vrank'"=="mp" local _vrank "m+p" else local _vrank "`vrank'" local sheet`vrank' "E19.`_vrank' Coverage (Target)" // default name of sheet in Excel files } } ** ppp conversion if (`ppp'==-1 & `cpisurvey'==-1 & `cpibase'==-1) { local _ppp = 0 `dit' "To convert to PPP, must provide {bf:ppp}, {bf:cpisurvey}, and {bf:cpibase} options." exit 198 } else local _ppp = 1 if (`_ppp' & min(`ppp',`cpisurvey',`cpibase')==-1) { `die' "To convert to PPP, must provide {bf:ppp}, {bf:cpisurvey}, and {bf:cpibase} options" exit 198 } if (`_ppp' & wordcount("`daily' `monthly' `yearly'")==0) { `dit' "Warning: {bf:daily}, {bf:monthly}, or {bf:yearly} options not specified; variables assumed to be in {bf:yearly} local currency units" local warning `warning' "Warning: daily, monthly, or yearly options not specified; variables assumed to be in yearly local currency units." local yearly yearly } if (wordcount("`daily' `monthly' `yearly'")>1) { `die' "{bf:daily}, {bf:monthly}, and {bf:yearly} options are exclusive" exit 198 } if ("`daily'"!="") local divideby = 1 else if ("`monthly'"!="") local divideby = 365/12 else if ("`yearly'"!="") local divideby = 365 ** group cut-offs local cut0 = 0 local cut6 = . // +infinity cap assert `cut0'<`cut1'<`cut2'<`cut3'<`cut4'<`cut5'<`cut6' if _rc { `die' "Group cut-off options must be specified such that 0<{bf:cut1}<{bf:cut2}<{bf:cut3}<{bf:cut4}<{bf:cut5}" exit 198 } ** titles of groups local g1 "y < `cut1'" local g2 "`cut1' < y < `cut2'" local g3 "y < `cut2'" local g4 "`cut2' < y < `cut3'" local g5 "y < `cut3'" local g6 "`cut3' < y < `cut4'" local g7 "`cut4' < y < `cut5'" local g8 "y > `cut5'" local g9 "y > `cut4'" local g10 "y > `cut3'" ** make sure using is xls or xlsx cap putexcel clear if `"`using'"'!="" { qui di " // for Notepad++ syntax highlighting if !strpos(`"`using'"' /* " */ , ".xls") { `die' "File extension must be .xls or .xlsx to write to an existing CEQ Master Workbook (requires Stata 13 or newer)" exit 198 } confirm file `"`using'"' qui di " } else { // if "`using'"=="" `dit' "Warning: No file specified with {bf:using}; results saved in {bf:return list} but not exported to CEQ Master Workbook Section E" } if strpos(`"`using'"'," ")>0 & "`open'"!="" { // has spaces in filename qui di " `dit' `"Warning: `"`using'"' contains spaces; {bf:open} option will not be executed. File can be opened manually after `command' runs."' local open "" // so that it won't try to open below } ** negative incomes foreach v of local alllist { if "``v''"!="" { qui count if ``v''<0 // note `v' is e.g. m, ``v'' is varname if r(N) { `dit' "Warning: `r(N)' negative values of ``v''." local warning `warning' "Warning: `r(N)' negative values of ``v''." } } } ** negative fiscal interventions foreach pr of local programcols { if "`pr'"!="" { qui summ `pr' if r(mean)>0 { qui count if `pr'<0 if r(N) `dit' "Warning: `r(N)' negative values of `d_`pr''." if r(N) local warning `warning' "Warning: `r(N)' negative values of `d_`pr''." } if r(mean)<0 { qui count if `pr'>0 if r(N) `dit' "Warning: `r(N)' positive values of `d_`pr'' (variable stored as negative values)." if r(N) local warning `warning' "Warning: `r(N)' positive values of `pr' (variable stored as negative values)." } } } *********************** ** OTHER MODIFICATION * *********************** ** separate warning so that the temporary variables do not show on the command screen if wordcount("`allprogs'")>0 /// foreach tax of local taxlist { foreach pr in ``tax'' { qui summ `pr', meanonly if r(mean)>0 { if wordcount("`taxwarning'")>0 local taxwarning `taxwarning', `pr' else local taxwarning `pr' } } } if wordcount("`taxwarning'")>0 { `dit' "Taxes appear to be positive values for variable(s) `taxwarning'; replaced with negative for calculations" } ** create new variables for program categories if wordcount("`allprogs'")>0 /// foreach pr of local taxcols { qui summ `pr', meanonly if r(mean)>0 { if wordcount("`postax'")>0 local postax `postax', `pr' else local postax `pr' qui replace `pr' = -`pr' // replace doesnt matter since we restore at the end } } ** PPP converted variables if (`_ppp') { local ppp_calculated = `ppp'*(`cpisurvey'/`cpibase') foreach v of local alllist { tempvar `v'_ppp if "``v''"!="" qui gen ``v'_ppp' = (``v''/`divideby')*(1/`ppp_calculated') } foreach pr of local programcols { tempvar `pr'_ppp qui gen ``pr'_ppp' = (`pr'/`divideby')*(1/`ppp_calculated') } } ** temporary variables tempvar one qui gen `one' = 1 ****************** ** INCOME GROUPS * ****************** foreach v of local alllist { if "``v''"!="" { ** groups tempvar `v'_group2 qui gen ``v'_group2' = . forval gp=1/6 { qui replace ``v'_group2' = `gp' if ``v'_ppp'>=`cut`=`gp'-1'' & ``v'_ppp'<`cut`gp'' // this works because I set `cut0' = 0 and `cut6' = infinity } qui replace ``v'_group2' = 1 if ``v'_ppp' < 0 } } local group2 = 6 ********************** ** CALCULATE RESULTS * ********************** local matrices dtransfers dtaxes subsidies indtaxes inkind local dtransfers_rows = 15 local dtaxes_rows = 16 local subsidies_rows = 15 local indtaxes_rows = 15 local inkind_rows = 30 foreach mat of local matrices { local `mat'_needrows = wordcount("``mat'cols'") } // Starting row for excel local dtransfers_start = 17 local dtaxes_start = `dtransfers_start' + `dtransfers_rows' + 1 local subsidies_start = `dtaxes_start' + `dtaxes_rows' + 1 local indtaxes_start = `subsidies_start' + `subsidies_rows' + 1 local inkind_start = `indtaxes_start' + `indtaxes_rows' + 1 local bottom_start = `inkind_start' + `inkind_rows' // no + 1 since no title row foreach v of local alllist { if "``v''"!="" { foreach suffix in "" "_ppp" { matrix bottom`v'`suffix' = J(3,7,.) } local counter_fiscal= 1 // New counters - August 3, 2020 local counter_bc= 1 // New counters - August 3, 2020 local counter_fiscal_ppp= 1 // New counters - August 3, 2020 local counter_bc_ppp= 1 // New counters - August 3, 2020 foreach mat of local matrices { if "``mat''"!="" { foreach suffix in "" "_ppp" /// "_target" "_target_hh" "_target_all" /// "_direct" "_hh" "_all" { matrix `mat'`v'`suffix' = J(``mat'_needrows',7,.) // 7 is 6 income groups + total } } local pr_row = 0 if "``mat''"!="" { foreach pr of local `mat'cols { local ++pr_row // if "`tarb_`pr''"=="" continue // skip program /*cap confirm variable `tar_`pr'' if _rc continue */ // end patch assert !missing(`tar_`pr'') // so `tar_`pr''>0 condition assert !missing(`tarb_`pr'') // not picking up missings // BENEFITS MATRICES - Re-wrote calculations - August 3, 2020 - Caterina Brest Lopez // Benefits received by target population in LCU if "`tarb_`pr''"!="" { cap confirm variable `tarb_`pr'' if _rc == 0 { if strpos("`v_fiscal'","`pr'")!=0 { // Fixing issue with totals - August 3, 2020 local ww: word `counter_fiscal' of `v_fiscal_names' local yy=substr("`ww'",3,length("`ww'")) g double `yy'_`v'_t=0 foreach var of local `yy' { if "`tarb_`var''"!="" { cap confirm variable `tarb_`var'' if _rc == 0 { replace `yy'_`v'_t=`yy'_`v'_t+`var'*(`tarb_`var''>0) } else{ replace `yy'_`v'_t=`yy'_`v'_t } } } local ++counter_fiscal } if strpos("`v_broadcat'","`pr'")!=0 { // Fixing issue with broad catergories' totals - August 3, 2020 local uu: word `counter_bc' of `v_broadcat_names' local ee=substr("`uu'",3,length("`uu'")) g double `ee'_`v'_t=0 foreach var of local `ee' { foreach var2 of local `var'{ if "`tarb_`var2''"!="" { cap confirm variable `tarb_`var2'' if _rc == 0 { replace `ee'_`v'_t=`ee'_`v'_t+`var2'*(`tarb_`var2''>0) } else{ replace `ee'_`v'_t=`ee'_`v'_t } } } } local ++counter_bc } forval gp=1/6 { //By income groups - August 3, 2020 if strpos("`v_fiscal'","`pr'")!=0 { qui summ `yy'_`v'_t if ``v'_group2'==`gp' & `tarb_`pr'' > 0 `aw' matrix `mat'`v'[`pr_row',`gp'] = r(sum) } else if strpos("`v_broadcat'","`pr'")!=0 { qui summ `ee'_`v'_t if ``v'_group2'==`gp' & `tarb_`pr'' > 0 `aw' matrix `mat'`v'[`pr_row',`gp'] = r(sum) } else{ qui summ `pr' if ``v'_group2'==`gp' & `tarb_`pr'' > 0 `aw' matrix `mat'`v'[`pr_row',`gp'] = r(sum) } } // Totals - August 3, 2020 if strpos("`v_fiscal'","`pr'")!=0 { qui summ `yy'_`v'_t if `tarb_`pr'' > 0 `aw' matrix `mat'`v'[`pr_row',7] = r(sum) // total } else if strpos("`v_broadcat'","`pr'")!=0 { qui summ `ee'_`v'_t if `tarb_`pr'' > 0 `aw' matrix `mat'`v'[`pr_row',7] = r(sum) // total } else { qui summ `pr' if `tarb_`pr'' > 0 `aw' matrix `mat'`v'[`pr_row',7] = r(sum) // total } } //if _rc == 0 } // if "`tarb_`pr''"!="" else { forval gp=1/6 { matrix `mat'`v'[`pr_row',`gp'] = . } matrix `mat'`v'[`pr_row',7] = . } // Benefits received by target population in PPP if "`tarb_`pr''"!="" { cap confirm variable `tarb_`pr'' if _rc == 0 { if strpos("`v_fiscal'","`pr'")!=0 { // Fixing issue with totals - August 3, 2020 local ww: word `counter_fiscal_ppp' of `v_fiscal_names' local yy=substr("`ww'",3,length("`ww'")) g double `yy'_`v'_ppp_t=0 foreach var of local `yy' { if "`tarb_`var''"!="" { cap confirm variable `tarb_`var'' if _rc == 0 { replace `yy'_`v'_ppp_t=`yy'_`v'_ppp_t+``var'_ppp'*(`tarb_`var''>0) } else{ replace `yy'_`v'_ppp_t=`yy'_`v'_ppp_t } } } local ++counter_fiscal_ppp } if strpos("`v_broadcat'","`pr'")!=0 { // Fixing issue with broad catergories' totals - August 3, 2020 local uu: word `counter_bc_ppp' of `v_broadcat_names' local ee=substr("`uu'",3,length("`uu'")) g double `ee'_`v'_ppp_t=0 foreach var of local `ee' { foreach var2 of local `var'{ if "`tarb_`var2''"!="" { cap confirm variable `tarb_`var2'' if _rc == 0 { replace `ee'_`v'_ppp_t=`ee'_`v'_ppp_t+``var2'_ppp'*(`tarb_`var2''>0) } else{ replace `ee'_`v'_ppp_t=`ee'_`v'_ppp_t } } } } local ++counter_bc_ppp } forval gp=1/6 { //By income groups - August 3, 2020 if strpos("`v_fiscal'","`pr'")!=0 { qui summ `yy'_`v'_ppp_t if ``v'_group2'==`gp' & `tarb_`pr'' > 0 `aw' matrix `mat'`v'_ppp[`pr_row',`gp'] = r(sum) } else if strpos("`v_broadcat'","`pr'")!=0 { qui summ `ee'_`v'_ppp_t if ``v'_group2'==`gp' & `tarb_`pr'' > 0 `aw' matrix `mat'`v'_ppp[`pr_row',`gp'] = r(sum) } else{ qui summ ``pr'_ppp' if ``v'_group2'==`gp' & `tarb_`pr'' > 0 `aw' matrix `mat'`v'_ppp[`pr_row',`gp'] = r(sum) } } // Totals - August 3, 2020 if strpos("`v_fiscal'","`pr'")!=0 { qui summ `yy'_`v'_ppp_t if `tarb_`pr'' > 0 `aw' matrix `mat'`v'_ppp[`pr_row',7] = r(sum) // total } else if strpos("`v_broadcat'","`pr'")!=0 { qui summ `ee'_`v'_ppp_t if `tarb_`pr'' > 0 `aw' matrix `mat'`v'_ppp[`pr_row',7] = r(sum) // total } else { qui summ ``pr'_ppp' if `tarb_`pr'' > 0 `aw' matrix `mat'`v'_ppp[`pr_row',7] = r(sum) // total } } //if _rc == 0 } // if "`tarb_`pr''"!="" else { forval gp=1/6 { matrix `mat'`v'_ppp[`pr_row',`gp'] = . } matrix `mat'`v'_ppp[`pr_row',7] = . } // TARGET POPULATION MATRICES // Use tarb rather than tar here because tarb_`var' is restricted by benefit and target specification; // tar_`pr' is restricted by benefit, target and db. This offers more flexibility if "`tarb_`pr''"!="" & `db_result' == 1 { // Target Individuals forval gp=1/6 { qui summ `tarb_`pr'' if ``v'_group2'==`gp' [aw=`exp'] // using the new variable matrix `mat'`v'_target[`pr_row',`gp'] = r(sum) } qui summ `tarb_`pr'' [aw=`exp'] matrix `mat'`v'_target[`pr_row',7] = r(sum) } if "`tarb_`pr''"=="" | `db_result' == 0 { forval noval = 1/7 { matrix `mat'`v'_target[`pr_row',`noval'] = . } } if "`tarb_`pr''"!="" { // Target households forval gp=1/6 { qui summ `one' if ``v'_group2'==`gp' & !missing(`pr') & `tarb_`pr'' > 0 /// [aw=`exp'] matrix `mat'`v'_target_hh[`pr_row',`gp'] = r(sum) } qui summ `one' if /*`pr'!=0 & */ !missing(`pr') & `tarb_`pr'' > 0 /// [aw=`exp'] matrix `mat'`v'_target_hh[`pr_row',7] = r(sum) // total // Direct and indirect target beneficiaries forval gp=1/6 { qui summ `one' if ``v'_group2'==`gp' & !missing(`pr') & `tarb_`pr'' > 0 /// `aw' matrix `mat'`v'_target_all[`pr_row',`gp'] = r(sum) } qui summ `one' if !missing(`pr') & `tarb_`pr''>0 /// `aw' matrix `mat'`v'_target_all[`pr_row',7] = r(sum) // total } else { forval noval = 1/7 { matrix `mat'`v'_target_hh[`pr_row',`noval'] = . matrix `mat'`v'_target_all[`pr_row',`noval'] = . } } // TARGET BENEFICIARY MATRICES if "`tar_`pr''"!="" & "`bdt_`pr''"!="" & `db_result' == 1 { // Target Direct beneficiaries forval gp=1/6 { qui summ `bdt_`pr'' if ``v'_group2'==`gp' [aw=`exp'] // using the new variable /* qui summ `db_`pr'' if ``v'_group'==`gp' & `tar_`pr'' > 0 /// [aw=`exp'] */ // `db_`pr'' has number of direct beneficiaries in hh // use hh weight rather than hhweight*members since // `db_`pr'' already has number of ben per hh matrix `mat'`v'_direct[`pr_row',`gp'] = r(sum) } qui summ `bdt_`pr'' [aw=`exp'] // qui summ /*`db_`pr'' if `tar_`pr'' > 0 */ [aw=`exp'] // use hh weight rather than hhweight*members since // `db_`pr'' already has number of ben per hh matrix `mat'`v'_direct[`pr_row',7] = r(sum) } if "``bdt_`pr''"=="" | `db_result' == 0 { forval noval = 1/7 { matrix `mat'`v'_direct[`pr_row',`noval'] = . } } if "`tar_`pr''"!="" { // Target beneficiary households forval gp=1/6 { qui summ `one' if ``v'_group2'==`gp' & `bdt_`pr''>0 /* & `pr'!=0 & !missing(`pr') & `tar_`pr'' > 0 */ /// [aw=`exp'] matrix `mat'`v'_hh[`pr_row',`gp'] = r(sum) } qui summ `one' if `bdt_`pr''>0 /* if `pr'!=0 & !missing(`pr') & `tar_`pr'' > 0 */ /// [aw=`exp'] matrix `mat'`v'_hh[`pr_row',7] = r(sum) // total // Direct and indirect target beneficiaries forval gp=1/6 { qui summ `one' if ``v'_group2'==`gp' & `bdt_`pr''>0 /* & `pr'!=0 & !missing(`pr') & `tar_`pr'' > 0 */ /// `aw' matrix `mat'`v'_all[`pr_row',`gp'] = r(sum) } qui summ `one' if `bdt_`pr''>0 /* `pr'!=0 & !missing(`pr') & `tar_`pr''>0 */ /// `aw' matrix `mat'`v'_all[`pr_row',7] = r(sum) // total } else { forval noval = 1/7 { matrix `mat'`v'_hh[`pr_row',`noval'] = . matrix `mat'`v'_all[`pr_row',`noval'] = . } } } // end foreach ...cols } // end if } // end foreach mat /* // Bottom matter foreach suffix in "" "_ppp" { forval gp=1/6 { qui summ `one' if ``v'_group'==`gp' & `tar_`pr'' > 0 `aw' matrix bottom`v'`suffix'[1,`gp'] = r(sum) qui summ `one' if ``v'_group'==`gp' & `tar_`pr'' > 0 [aw=`exp'] matrix bottom`v'`suffix'[2,`gp'] = r(sum) qui summ ``v'`suffix'' if ``v'_group'==`gp' & `tar_`pr'' > 0 `aw' matrix bottom`v'`suffix'[3,`gp'] = r(sum) } qui summ `one' `aw' if `tar_`pr'' > 0 matrix bottom`v'`suffix'[1,7] = r(sum) qui summ `one' [aw=`exp'] matrix bottom`v'`suffix'[2,7] = r(sum) qui summ ``v'`suffix'' `aw' matrix bottom`v'`suffix'[3,7] = r(sum) } */ } // if "``v''"!="" } // foreach v of local alllist } ***************** ** SAVE RESULTS * ***************** if `"`using'"'!="" /* " */ { `dit' `"Writing to "`using'"; may take several minutes"' // Locals for Excel columns local startcol_o = 4 // this one will stay fixed (column D) // Export to Excel (matrices) local submatrices extp poor rest tot local extp_add = 0 local poor_add = 3 local rest_add = 5 local tot_add = 10 local vertincrement = 101 local horzincrement = 14 foreach v of local alllist { if "``v''"=="" continue foreach mat in `matrices' { if "``mat''"!= "" { foreach suffix in "" "_ppp" /// "_target" "_target_hh" "_target_all" /// "_direct" "_hh" "_all" { foreach sub of local submatrices { tempname `mat'`v'`suffix'_`sub' } matrix ``mat'`v'`suffix'_extp' = `mat'`v'`suffix'[1...,1..2] matrix ``mat'`v'`suffix'_poor' = `mat'`v'`suffix'[1...,3] matrix ``mat'`v'`suffix'_rest' = `mat'`v'`suffix'[1...,4..6] matrix ``mat'`v'`suffix'_tot' = `mat'`v'`suffix'[1...,7] if ("`suffix'"=="" | "`suffix'"=="_ppp") /// local therow = ``mat'_start' if ("`suffix'"=="_target" | "`suffix'"=="_target_hh" | "`suffix'"=="_target_all") /// local therow = ``mat'_start' + `vertincrement' if ("`suffix'"=="_direct" | "`suffix'"=="_hh" | "`suffix'"=="_all") /// local therow = ``mat'_start' + `vertincrement' * 2 if ("`suffix'"=="" | "`suffix'"=="_target" | "`suffix'"=="_direct") /// local thecol = `startcol_o' else if ("`suffix'"=="_ppp" | "`suffix'"=="_target_hh" | "`suffix'"=="_hh") /// local thecol = `startcol_o' + `horzincrement' else local thecol = `startcol_o' + `horzincrement'*2 foreach sub of local submatrices { local thesubcol = `thecol' + ``sub'_add' returncol `thesubcol' local resultset`v' `resultset`v'' `r(col)'`therow'=matrix(``mat'`v'`suffix'_`sub'') } // this indent is due to 2-line foreach line } } } } // Print information local date `c(current_date)' local titlesprint local titlerow = 3 local titlecol = 1 local titlelist country surveyyear authors date ppp baseyear cpibase cpisurvey ppp_calculated /// scenario group project foreach title of local titlelist { returncol `titlecol' if "``title''"!="" & "``title''"!="-1" /// local titlesprint `titlesprint' `r(col)'`titlerow'=("``title''") local titlecol = `titlecol' + 1 } // Print version number on Excel sheet local versionprint A4=("Results produced by version `version' of `command' on `c(current_date)' at `c(current_time)'") // Export to Excel (row titles) local titlerow = 2 returncol `titlerow' foreach mat of local matrices { local rr = ``mat'_start' foreach pr of local `mat'cols { local titles `titles' `r(col)'`rr'=("`d_`pr''") local ++rr } } // Export to Excel (group cutoffs) local startcol = `startcol_o' local cutoffrow = 15 forval i=1/10 { returncol `startcol' local cutoffs `cutoffs' `r(col)'`cutoffrow'=("`g`i''") local ++startcol } // Print warning message on Excel sheet local warningrow = 721 local warningcount = -1 foreach x of local warning { local warningprint `warningprint' A`warningrow'=("`x'") local ++warningrow local ++warningcount } // overwrite the obsolete warning messages if there are any forval i=0/100 { local warningprint `warningprint' A`=`warningrow'+`i''=("") } // count warning messages and print at the top of MWB local warningprint `warningprint' A5=("`warningcount' important warning messages are printed starting on row 721.") // putexcel foreach vrank of local alllist { if "``vrank''"!="" { qui putexcel `titlesprint' `versionprint' /// `resultset`vrank'' `titles' `cutoffs' `warningprint' using `"`using'"', /// " modify keepcellformat sheet("`sheet`vrank''") } } } ********* ** OPEN * ********* if "`open'"!="" & "`c(os)'"=="Windows" { shell start `using' // doesn't work with "" or `""' so I already changed `open' to "" if using has spaces, } else if "`open'"!="" & "`c(os)'"=="MacOSX" { shell open `using' } else if "`open'"!="" & "`c(os)'"=="Unix" { shell xdg-open `using' } ************* ** CLEAN UP * ************* quietly putexcel clear restore // note this also restores svyset end // END ceqcoverage