**** 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)")