**** Living conditions Survey (2014/2015) Do File ******************************* /* James Reeler 2014/2015 Living Conditions Survey 23,380 survey households (UQNO) 88,906 survey people (personno) Population estimates: 16,618,692 households 57,913,060 individuals */ *********************************************************** set more off * set working directory global wf "C:\Users\Jay\Documents\Cloud Data\OneDrive\ACDI 2018\Thesis\Data\IES\LCS_2015\Analysis" cd "$wf" * set up logging cap log close log using IES2014_15,replace *=========================================================================== * GLOBALS FOR DATA FILES AND VERSION SUFFIXES * Type of data being used, Anon or Secure global TYPE "Anon" * Inflation value for calculating real expenditure/income. Derived from CPI data http://www.statssa.gov.za/?page_id=1854&PPN=P0141&SCH=7617 * LCS prices are in April 2015 prices, here inflated/deflated to Dec 2016 global inflation_correction 0.915 global year 2015 * Protect primary data by working with copies * * rename data files for simplicity use "..\Data\Original\lcs-2014-2015-households-v1.dta", clear save ..\Data\Houseinfo, replace use ..\Data\Original\lcs-2014-2015-persons-final-v1, clear save ..\Data\Personinfo, replace use ..\Data\Original\lcs-2014-2015-total-v1, clear save ..\Data\totalLCS, replace use ..\Data\Original\lcs-2014-2015-personincome-v1.dta, clear save ..\Data\PersonIncome, replace *creates a numbered id for each household (1,2,3,etc.) use ..\Data\Houseinfo, clear gen id = _n label var id "Household ID" keep UQNO hholds_wgt id sort UQNO hholds_wgt save 0id, replace * rest of code replaces the UQNO with the household id created above * the variable is still called UQNO use ..\Data\Personinfo, clear sort UQNO merge m:1 UQNO using 0id tab _merge drop _merge order id drop UQNO rename id UQNO rename hholds_wgt hh_wgt rename Persns_wgt p_weight save ..\Newdata\Personinfo, replace use ..\Data\Houseinfo, clear sort UQNO hholds_wgt merge 1:1 UQNO hholds_wgt using 0id tab _merge drop _merge order id drop UQNO rename id UQNO * Calculate real value for income (adjusted to Dec 2016 Rands) * replace income_inkind = income_inkind / $inflation_correction rename hholds_wgt hh_wgt save ..\Newdata\Houseinfo, replace **** Useful if we follow up with determinants of income (demographic factors) use ..\Data\PersonIncome, clear sort UQNO merge m:1 UQNO using 0id tab _merge drop if _merge ~= 3 drop _merge order id drop UQNO rename id UQNO * Calculate real value for annualized income value (adjusted to Dec 2016 Rands) * rename Valueannualized_adj valueannualized_adj replace valueannualized_adj = valueannualized_adj / $inflation_correction rename hholds_wgt hh_wgt rename Persns_wgt p_weight save ..\Newdata\PersonIncome, replace use ..\Data\totalLCS, clear sort UQNO hholds_wgt merge m:1 UQNO using 0id tab _merge drop _merge order id drop UQNO rename id UQNO * Calculate real value for annualized expediture value (adjusted to Dec 2016 Rands) * gen coicop_exp_adj = valueannualized_adj / $inflation_correction label var coicop_exp_adj "Expenditure on coicop, adjusted to Dec 2016 rands" rename Coicop coicop rename hholds_wgt hh_wgt save ..\Newdata\totalLCS, replace * Income and expenditure --------------------- * Import SUT definitions. This is a text file containing correlations between individual COICOPs and * relevant SU categories, prepared using the IES metadata and the SU classifications from StatsSA. * Non-expenditure SUs are not considered here. import delimited "..\..\Coicop_sut.csv", clear sort coicop save 0coicop, replace * Use COICOP/SUT equivalencies to allocate all COICOPs recorded in household expenditures to the relevant SU class. * Expenditure for each SUT is aggregated per household. use ..\Newdata\totalLCS, clear sort coicop merge m:1 coicop using 0coicop tab _m drop if _m ~= 3 drop _m drop if sut > 106 collapse (sum) coicop_exp_adj, by (sut UQNO) rename coicop_exp_adj sut_exp_adj label var sut_exp_adj "Expenditure per SUT" sort UQNO sut sort sut save 2workfile, replace *______________________________________________________________________ * new dataset to assign people to households and allocate expenditure/income to people. use ..\newdata\Personinfo, clear sort UQNO gen id_p = _n keep UQNO PERSONNO p_weight id_p hh_wgt sort UQNO p_weight PERSONNO id_p save 0id_p, replace * integrate individuals and individual weightings into the workfile use 0id_p, clear sort UQNO merge m:m UQNO using 2workfile tab _m drop if _m ~= 3 drop _m save 2workfile,replace *---------------------------Household classification BY INCOME------------------- income------------------- Classification of households by income allows determination of household CAPABILITY. However, emissions for the households are still calculated using housheold expenditure values, since this corresponds to emissions. Direct income and income in kind per household are provided in the IES/LCS datasets. Households are classified using in kind income, and taxes and debts are not considered. Household income is evaluated on a per capita basis, to compensate for household size and multiple income effects. The IES/LCS provides weightings for each household and individuals with income. Weightings consider the stratified survey approach, and compensate for over/under-represenation, non- response errors and exclusions for small evaluation areas, on the basis of the 2001 census. Individual weightings are applied here where they differ from the calculated household weighting. */ use ..\Newdata\Houseinfo, clear collapse (sum) income_inkind, by (UQNO) rename income_inkind hh_tot_income save 1temp, replace use ..\newdata\Houseinfo, clear sort UQNO merge 1:1 UQNO using 1temp tab _m drop if _m ~= 3 drop _m * per capita income for each household gen pcinc = hh_tot_income / hhsize label var pcinc "Per capita income for household" sort UQNO save 2workfile_1, replace * include individuals and personal weightings use 0id_p, clear sort UQNO merge m:1 UQNO using 2workfile_1 tab _m drop if _m ~= 3 drop _m save 2workfile_2,replace *Classify households according to weighted total income (including in kind income). use 2workfile_2, clear xtile decile=pcinc [pw=p_weight], nquantiles(10) xtile top=pcinc if decile == 10 [pw=p_weight], nquantiles(5) recode top (.=0) replace top = top -1 if top ~= 0 gen hhdclass = decile + top *define 14 household classes, corresponding to 9 deciles and a tenth decile split into five 2% groupings label define hhdclass 1 "0-10" 2 "11-20" 3 "21-30" 4 "31-40" 5 "41-50" 6 "51-60" 7 "61-70" 8 "71-80" 9 "81-90" 10 "91-92" 11 "93-94" 12 "95-96" 13 "97-98" 14 "99-100" label val hhdclass hhdclass label var hhdclass "Income decile for household" sort UQNO save 1hhdclass_p, replace erase 1temp.dta * Generate household class size variables. First nine classes are 10% of the population, whilst the last five are each 2% use 1hhdclass_p, clear gen hhdclass_size = hhdclass egen personcount = sum(p_weight) replace hhdclass_size = personcount/50 if hhdclass>9 replace hhdclass_size = personcount/10 if hhdclass<10 label var hhdclass_size "Number of households in class" keep hhdclass hhdclass_size hhsize collapse(first) hhdclass_size, by (hhdclass) merge 1:m hhdclass using 1hhdclass_p tab _m drop if _m ~= 3 drop _m keep id_p UQNO p_weight hhdclass hhdclass_size hhsize sort id_p save 1hhdclass_p, replace /*---------------------------Household classification BY EXPENDITURE------------------- Classification of households by expenditure allows evaluation of the deciles most and least responsible for overall emissions, driven by average percentile household emissions. As for income classification, weightings calculated in the IES are used to ensure suitable balance across the population. */ use 2workfile, clear collapse (sum) sut_exp_adj, by(UQNO) save 1temp, replace use ..\newdata\Houseinfo, clear sort UQNO merge 1:m UQNO using 1temp tab _m drop if _m ~= 3 drop _m *per capita expenditure for each household gen pcexp = sut_exp_adj / hhsize sort UQNO save 2workfile_3, replace use 0id_p, clear sort UQNO merge m:m UQNO using 2workfile_3 tab _m drop if _m ~= 3 drop _m save 2workfile_4,replace use 2workfile_4, clear xtile decile=pcexp [pw=p_weight], nquantiles(10) xtile top=pcexp if decile == 10 [pw=p_weight], nquantiles(5) recode top (.=0) replace top = top -1 if top ~= 0 gen hhdclass = decile + top label define hhdclass 1 "hhd0" 2 "hhd1" 3 "hhd2" 4 "hhd3" 5 "hhd4" 6 "hhd5" 7 "hhd6" 8 "hhd7" 9 "hhd8" 10 "hhd91" 11 "hhd92" 12 "hhd93" 13 "hhd94" 14 "hhd95" label val hhdclass hhdclass keep id_p UQNO hhdclass p_weight hhsize sort id_p save 1hhdclass_pexp, replace * generate household class size variables use 1hhdclass_pexp, clear gen hhdclass_size = hhdclass egen personcount = sum(p_weight) replace hhdclass_size = personcount/50 if hhdclass>9 replace hhdclass_size = personcount/10 if hhdclass<10 label var hhdclass_size "Number of households in class" keep hhdclass hhdclass_size hhsize collapse(first) hhdclass_size, by (hhdclass) merge 1:m hhdclass using 1hhdclass_pexp tab _m drop if _m ~= 3 drop _m keep id_p UQNO p_weight hhdclass hhdclass_size hhsize sort id_p save 1hhdclass_pexp, replace /* Generate output tables. These tables are used in the construction of full conumptions data in the Excel SUT multiplier analysis. Whislt direct analysis could be undertaken with the Stata code by importing CIMS from the SUT analysise, the total housheold expenditure within the IES and LCS datasets differed somewhat from that in the supply and use tables. In order to provide a clear estimae of the portion of total national emissions for which households are responsible, it was considered important to align household expenditures with those of n the SUT. To do this, the proportional shares of total emissions within each IES household class was assumed to be correct, whilst the total qauantum of expenditure in the SU was assumed to be correct. */ ****************************** * Table 1: Total household expenditure by income class (Rm) use 2workfile, clear keep sut id_p sut_exp_adj p_weight sort id_p merge m:1 id_p using 1hhdclass_p tab _m drop if _m ~= 3 drop _m ** Convert to total expenditure in Rm, to correlate to SUT units. replace sut_exp_adj = (sut_exp_adj)/1000000 * Annualised expenditure for each SUT is aggregate per household class, after applying relevant weighting collapse (sum) sut_exp_adj [iw=hh_wgt], by (sut hhdclass) label var sut_exp_adj "Total expenditure (Rm weighted & annualized) for hhdclass" save 2hhdsutinc, replace use 2hhdsutinc, clear reshape wide sut_exp_adj, i(sut) j(hhdclass) renpfix sut_exp_adj hhd * apply labels for the SUT classes label define sut 1 "P1" 2 "P2" 3 "P3" 4 "P4" 5 "P5" 6 "P6" 7 "P7" 8 "P7gas" 9 "P7oil" 10 "P8" 11 "P9" 12 "P10" 13 "P11" 14 "P12" 15 "P13" 16 "P14" 17 "P15" 18 "P16" 19 "P17" 20 "P18" 21 "P19" 22 "P20" 23 "P21" 24 "P22" 25 "P23" 26 "P24" 27 "P25" 28 "P26" 29 "P27" 30 "P28" 31 "P29" 32 "P30" 33 "P31" 34 "P32" 35 "P33" 36 "P34" 37 "P35" 38 "P36" 39 "P37" 40 "P38" 41 "P39" 42 "P40" 43 "P41" 44 "P42" 45 "P43" 46 "P44" 47 "P45" 48 "P46" 49 "P47" 50 "P48" 51 "P49" 52 "P50" 53 "P51" 54 "P52" 55 "P53" 56 "P54" 57 "P55" 58 "P56" 59 "P57" 60 "P58" 61 "P59" 62 "P60" 63 "P61" 64 "P62" 65 "P63" 66 "P64" 67 "P65" 68 "P66" 69 "P67" 70 "P68" 71 "P69" 72 "P70" 73 "P71" 74 "P72" 75 "P73" 76 "P74" 77 "P75" 78 "P76" 79 "P77" 80 "P78" 81 "P79" 82 "P80" 83 "P81" 84 "P82" 85 "P83" 86 "P84" 87 "P85" 88 "P86" 89 "P87" 90 "P88" 91 "P89" 92 "P90" 93 "P91" 94 "P92" 95 "P93" 96 "P94" 97 "P95" 98 "P96" 99 "P97" 100 "P98" 101 "P99" 102 "P100" 103 "P101" 104 "P102" 105 "P103" 106 "P104" label values sut sut sort sut save ..\Output\hhdincSUTuse, replace * export to excel file export excel using "..\Output\\$year.xlsx", firstrow(varlabels) sheetreplace sheet("1. SUT(inc)") * Table 2: Total household expenditure by expenditure class (Rm) use 2workfile, clear *keep sut id_p sut_exp_adj p_weight hh_wgt sort id_p merge m:1 id_p using 1hhdclass_pexp tab _m drop if _m ~= 3 drop _m * Convert to total expenditure in Rm, to correlate to SUT units. replace sut_exp_adj = (sut_exp_adj)/1000000 * Annualised expenditure for each SUT is aggregate per household class, after applying relevant weighting collapse (sum) sut_exp_adj [iw=hh_wgt], by (sut hhdclass) label var sut_exp_adj "Total expenditure (Rm weighted & annualized) for hhdclass" save 2hhdsutexp, replace use 2hhdsutexp, clear reshape wide sut_exp_adj, i(sut) j(hhdclass) renpfix sut_exp_adj hhd * apply labels for the SUT classes label define sut 1 "P1" 2 "P2" 3 "P3" 4 "P4" 5 "P5" 6 "P6" 7 "P7" 8 "P7gas" 9 "P7oil" 10 "P8" 11 "P9" 12 "P10" 13 "P11" 14 "P12" 15 "P13" 16 "P14" 17 "P15" 18 "P16" 19 "P17" 20 "P18" 21 "P19" 22 "P20" 23 "P21" 24 "P22" 25 "P23" 26 "P24" 27 "P25" 28 "P26" 29 "P27" 30 "P28" 31 "P29" 32 "P30" 33 "P31" 34 "P32" 35 "P33" 36 "P34" 37 "P35" 38 "P36" 39 "P37" 40 "P38" 41 "P39" 42 "P40" 43 "P41" 44 "P42" 45 "P43" 46 "P44" 47 "P45" 48 "P46" 49 "P47" 50 "P48" 51 "P49" 52 "P50" 53 "P51" 54 "P52" 55 "P53" 56 "P54" 57 "P55" 58 "P56" 59 "P57" 60 "P58" 61 "P59" 62 "P60" 63 "P61" 64 "P62" 65 "P63" 66 "P64" 67 "P65" 68 "P66" 69 "P67" 70 "P68" 71 "P69" 72 "P70" 73 "P71" 74 "P72" 75 "P73" 76 "P74" 77 "P75" 78 "P76" 79 "P77" 80 "P78" 81 "P79" 82 "P80" 83 "P81" 84 "P82" 85 "P83" 86 "P84" 87 "P85" 88 "P86" 89 "P87" 90 "P88" 91 "P89" 92 "P90" 93 "P91" 94 "P92" 95 "P93" 96 "P94" 97 "P95" 98 "P96" 99 "P97" 100 "P98" 101 "P99" 102 "P100" 103 "P101" 104 "P102" 105 "P103" 106 "P104" label values sut sut sort sut * save dataset save ..\Output\hhdexpSUTuse, replace * export to excel file export excel using "..\Output\\$year.xlsx", firstrow(varlabels) sheetreplace sheet("2. SUT(exp)")