#delimit ;
set more off;
clear all;
capture log close;
pause on;

global feshealthvarlist "srh_1 srh_2 srh_3 srh_na ghs res_srh1 res_srh2 res_srh3";
global prodgroups "foodin foodout othnd medical hrelated recrea transport";


/**************/
/*    FES     */
/**************/

use fesdata.dta, clear;

drop if ndx_mh==0;
drop if empstat==0;
keep if year>=1978 & year<=2010;

drop empstat;

foreach var in relderly rhealthin rhlthser rmedic rdrugs rhospital rmedical {; 
	gen ann`var' = `var'*52;
};

gen annrmedical_noins = annrmedical - annrhealthin;

gen annrmedical_noelderly = annrmedical - annrelderly;
gen annrmedical_noinsnoelderly = annrmedical_noins - annrelderly;

/*Data for UK portion of Table 3*/
log using "textfiles\medicalspendingover60s", text replace;
/*Spending on different medical categories in the LCFS*/
di "Made in prepfesdata_olderages";
su annrmedical_noins if inrange(year,2000,2006) & agehd1>=60, det; 
su annrmedical_noinsnoelderly if inrange(year,2000,2006) & agehd1>=60, det; 
su annrmedical_noelderly if inrange(year,2000,2006) & agehd1>=60, det; 
su annrmedical if inrange(year,2000,2006) & agehd1>=60, det; 
su annrelderly if inrange(year,2000,2006) & agehd1>=60, det; 
su annrhealthin if inrange(year,2000,2006) & agehd1>=60, det; 
su annrhlthser if inrange(year,2000,2006) & agehd1>=60, det;
su annrmedic  if inrange(year,2000,2006) & agehd1>=60, det; 
su annrdrugs if inrange(year,2000,2006) & agehd1>=60, det;
su annrhospital if inrange(year,2000,2006) & agehd1>=60, det;
log close;

drop annrmedical_noins annrmedical_noinsnoelderly annrmedical_noelderly annrmedical annrelderly annrhealthin annrhlthser annrmedic annrdrugs annrhospital;

gen dob = year-agehd1;
gen     cohort = 1  if            dob<=1902;
replace cohort = 2  if dob>1902 & dob<=1907;
replace cohort = 3  if dob>1907 & dob<=1912;
replace cohort = 4  if dob>1912 & dob<=1917;
replace cohort = 5  if dob>1917 & dob<=1922;
replace cohort = 6  if dob>1922 & dob<=1927;
replace cohort = 7  if dob>1927 & dob<=1932;
replace cohort = 8  if dob>1932 & dob<=1937;
replace cohort = 9  if dob>1937 & dob<=1942;
replace cohort = 10 if dob>1942 & dob<=1947;
replace cohort = 11 if dob>1947 & dob<=1952;
replace cohort = 12 if dob>1952 & dob<=1957;
replace cohort = 13 if dob>1957 & dob<=1962;
replace cohort = 14 if dob>1962 & dob<=1967;
replace cohort = 15 if dob>1967 & dob<=1972;
replace cohort = 16 if dob>1972 & dob<=1977;
replace cohort = 17 if dob>1977 & dob<=1982;
replace cohort = 18 if dob>1982 & dob<=1987;
replace cohort = 19 if dob>1987 & dob<=1992;
replace cohort = 20 if dob>1992 & dob<=1997;

label define cohort
1  "<=1902"
2  "1903-1907"
3  "1908-1912"
4  "1913-1917"
5  "1918-1922"
6  "1923-1927"
7  "1928-1932"
8  "1933-1937"
9  "1938-1942"
10 "1943-1947"
11 "1948-1952"
12 "1953-1957"
13 "1958-1962"
14 "1963-1967"
15 "1968-1972"
16 "1973-1977"
17 "1978-1982"
18 "1983-1987"
19 "1988-1992"
20 "1993-1997";
label values cohort cohort;

gen age1978 = 1978-dob;

gen  age1978b = -1 if age1978 >-20 & age1978<=-15;
replace  age1978b = 0 if age1978 >-15 & age1978<=-10;
replace age1978b = 1 if age1978 >-10 & age1978<=-5;
replace age1978b = 2 if age1978 >-5 & age1978<=0;
replace age1978b = 3 if age1978 >0 & age1978<=5;
replace age1978b = 4 if age1978 >5 & age1978<=10;
replace age1978b = 5 if age1978 >10 & age1978<=15;
replace age1978b = 6 if age1978 >15 & age1978<=20;
replace age1978b = 7 if age1978 >20 & age1978<=25;
replace age1978b = 8 if age1978 >25 & age1978<=30;
replace age1978b = 9 if age1978 >30 & age1978<=35;
replace age1978b = 10 if age1978 >35 & age1978<=40;
replace age1978b = 11 if age1978 >40 & age1978<=45;
replace age1978b = 12 if age1978 >45 & age1978<=50;
replace age1978b = 13 if age1978 >50 & age1978<=55;
replace age1978b = 14 if age1978 >55 & age1978<=60;
replace age1978b = 15 if age1978 >60 & age1978<=65;
replace age1978b = 16 if age1978 >65 & age1978<=70;
replace age1978b = 17 if age1978 >70 & age1978<=75;
replace age1978b = 18 if age1978 >75 & age1978<=80;
replace age1978b = 19 if age1978 >80;

gen midptage1978 = -18 if age1978b==-1;
replace midptage1978 = -13 if age1978b==0;
replace midptage1978 = -8 if age1978b==1;
replace midptage1978 = -3 if age1978b==2;
replace midptage1978 = 2 if age1978b==3;
replace midptage1978 = 7 if age1978b==4;
replace midptage1978 = 12 if age1978b==5;
replace midptage1978 = 17 if age1978b==6;
replace midptage1978 = 22 if age1978b==7;
replace midptage1978 = 27 if age1978b==8;
replace midptage1978 = 32 if age1978b==9;
replace midptage1978 = 37 if age1978b==10;
replace midptage1978 = 42 if age1978b==11;
replace midptage1978 = 47 if age1978b==12;
replace midptage1978 = 52 if age1978b==13;
replace midptage1978 = 57 if age1978b==14;
replace midptage1978 = 62 if age1978b==15;
replace midptage1978 = 67 if age1978b==16;
replace midptage1978 = 72 if age1978b==17;
replace midptage1978 = 77 if age1978b==18;
replace midptage1978 = 82 if age1978b==19;

gen cohortage = midptage1978+(year-1978);

assert cohort~=.;
assert cohortage~=.;

rename agehd1 age;

drop midptage* dob age1978 age1978b*;

/*merge in mortality statistics*/
merge m:1 year age using fesmort, keepusing(female_qx male_qx);
keep if _merge==3;
drop _merge;

gen     mortrisk = male_qx if sexhead==1;
replace mortrisk = female_qx if sexhead==0;
assert  mortrisk ~=.;

drop female_qx male_qx;

gen lnmortrisk = ln(mortrisk);
drop mortrisk;
rename sexhead sex;
sort year cohort eqrtx_mh eqrhhinc, stable;
forval i = 1/20 {;
    forval Y = 1978/2010 {;
        qui count if cohort==`i' & year==`Y';
        qui centile eqrtx_mh if cohort==`i' & year==`Y', centile(1 99);
        local exptop = r(c_2);
        local expbottom = r(c_1);
        qui centile eqrhhinc if cohort==`i' & year==`Y', centile(1 99);
        local inctop = r(c_2);
        local incbottom = r(c_1);

        qui drop if cohort==`i' & (eqrtx_mh<`expbottom'|eqrtx_mh>`exptop') & year==`Y';
        qui drop if cohort==`i' & (eqrhhinc<`incbottom'|eqrhhinc>`inctop') & year==`Y';
    };
};

bys year cohort: egen mincohortage = min(age);
bys year cohort: egen maxcohortage = max(age);

gen single = hassp==0;

rename numkids015 numkids;
label var numkids "# kids 0-15";

gen h_employed = headwork==1;
gen sp_employed = spousework==1;

gen bothwork = h_employed*sp_employed;
label var bothwork "both a head and a spouse working";

gen threeads = numads>=3;
label var threeads "num adults 3 or more";

assert sp_employed==0 if single==1;

/*make groups consistent with prices*/
replace poul_oth = poultry  + oth_meat;
replace oth_snac = restaur  + oth_snac + takeaway;
replace rec_toys = rec_toys + rec_tape + toysetc + cd_tape;
replace entertai = entertai + uk_hols + for_hols;
replace water    = water+wat_oth; 
replace telephon = telephon + telemess;
drop oth_meat restaur takeaway rec_tape cd_tape toysetc uk_hols for_hols wat_oth telemess;

/*merge in prices*/
merge m:1 year month using fesmonthprices_consistent;
assert _merge==2|_merge==3;
keep if _merge==3;
drop _merge;

/*generate shares of averages*/
foreach cat in $prodgroups {;
    gen w`cat' = 100*`cat'/ndx_mh;
};

foreach cat in $prodgroups {;
    bys year age: egen strataw`cat' = mean(w`cat');
};

gen lpfoodin = ln(prFOOD);
gen lpfoodout = ln(prCATERING);
gen prchcare = prdomservs;

drop prFOOD prCATERING;

gen othnd_lstone = 0;
foreach cat in ALCOHOL TOBACCO petcare CLOTH_SH p_servs chemgood book_new telephon chcare {;
    gen lp`cat' = ln(pr`cat');
    gen othnd_w`cat' = `cat'/(othnd-p_artic);
    bys year age: egen othnd_strataw`cat' = mean(othnd_w`cat');
    replace othnd_lstone = othnd_lstone+ othnd_strataw`cat'*lp`cat';
};

gen lpothnd = othnd_lstone;

drop ALCOHOL TOBACCO petcare CLOTH_SH book_new telephon chcare;
drop prALCOHOL prTOBACCO prpetcare prCLOTH_SH prbook_new prtelephon prchcare;
drop lpALCOHOL lpTOBACCO lppetcare lpCLOTH_SH lpbook_new lptelephon lpchcare;
drop othnd_lstone othnd_strataw* othnd_w*;

gen recrea_lstone = 0;
foreach cat in rec_toys entertai  {;
    gen lp`cat' = ln(pr`cat');
    gen recrea_w`cat' = `cat'/recrea;
    bys year age: egen recrea_strataw`cat' = mean(recrea_w`cat');
    if "`cat'"=="recsetc" {;
        replace recrea_lstone = recrea_lstone+ recrea_strataw`cat'*lp`cat' if year<1987; /*recsetc is missing from 1987 onwards*/
    };
    if "`cat'"~="recsetc" {;
        replace recrea_lstone = recrea_lstone+ recrea_strataw`cat'*lp`cat';
    };
};

gen lprecrea = recrea_lstone;

drop rec_toys prrec_toys lprec_toys;
drop recrea_lstone recrea_strataw* recrea_w*;

gen hrelated_lstone = 0;
foreach cat in water repair diy insgr FUEL_LIG postage domservs fees_sub tvlicen garden consuma {;
    gen lp`cat' = ln(pr`cat');
    gen hrelated_w`cat' = `cat'/hrelated;
    bys year age: egen hrelated_strataw`cat' = mean(hrelated_w`cat');
    replace hrelated_lstone  = hrelated_lstone+ hrelated_strataw`cat'*lp`cat';
};

gen lphrelated = hrelated_lstone;

drop water repair diy insgr FUEL_LIG postage fees_sub tvlicen garden consuma;
drop prwater prrepair prdiy prinsgr prFUEL_LIG prpostage prfees_sub prtvlicen prgarden prconsuma;
drop lpwater lprepair lpdiy lpinsgr lpFUEL_LIG lppostage lpfees_sub lptvlicen lpgarden lpconsuma;

drop hrelated_lstone hrelated_strataw* hrelated_w*;

gen transport_lstone = 0;
foreach cat in maintmot pet_oil tax_ins FARESET {;
    gen lp`cat' = ln(pr`cat');
    gen transport_w`cat' = `cat'/transport;
    bys year age: egen transport_strataw`cat' = mean(transport_w`cat');
    replace transport_lstone = transport_lstone+ transport_strataw`cat'*lp`cat';
	drop `cat' pr`cat' lp`cat';
};

replace transport_lstone = 0 if transport==0;

gen lptransport = transport_lstone;

drop transport_lstone transport_strataw* transport_w*;

replace p_servs_med = p_servs_med + healthin;
gen medical_lstone = 0;
foreach cat in p_servs chemgood entertai domservs {;
    gen medical_w`cat'_med = `cat'_med/(medical - p_artic_med - rec_toys_med);
	replace medical_w`cat'_med = . if (medical - p_artic_med - rec_toys_med)<0.001;
    egen medical_strataw`cat'_med = mean(medical_w`cat'_med), by(year age);
    replace medical_lstone = medical_lstone + medical_strataw`cat'_med*lp`cat';
	drop `cat' pr`cat' lp`cat';
};

gen lpmedical = medical_lstone;

drop p_servs_med healthin medical_lstone medical_strataw*_med medical_w*_med;

merge m:1 year age sex using ukhealth, keepusing($feshealthvarlist);
keep if _merge==3;
drop _merge;

drop if mincohortage<45;
drop if maxcohortage>79;

drop creditcards cashtransfers;

drop bread-mortgagetot;

drop domservs_med chemgood_med p_artic_med entertai_med rec_toys_med;
drop stratawfoodin stratawfoodout stratawfoodout stratawothnd stratawmedical stratawhrelated stratawrecrea stratawtransport;
drop mincohortage maxcohortage;
drop headspouse;
drop head;
drop ed ed1 ed2;
drop pie; 
drop yrm;
drop headwork spousework;
drop region goregion;

label var hhref "HH id";
label var datayear "LCFS edition";
label var week "Week of interview";
label var maxed "Highest education of head or spouse";
label define maxed 0 "Less than compulsory" 1 "Compulsory"; 
label values maxed maxed;
label var hassp "Has spouse";
label var ownmort "Owns home with mortgage";
label var ownout "Owns home outright";
label var renter "Rents home";
label var owner "Owns home";
label var hhinc "HH income";
label var medical "Spending on medical ($ per week)";
label var hlthser "Spending on health services ($ per week)";
label var medic "Spending on doctors ($ per week)";
label var drugs "Spending on drugs ($ per week)";
label var hospital "Spending on hospital ($ per week)";
label var elderly "Spending on elderly care ($ per week)";
label var othnd "Spending on other non-durables ($ per week)";
label var recrea "Spending on recreation ($ per week)";
label var foodin "Spending on foot at home ($ per week)";
label var foodout "Spending on food away from home ($ per week)";
label var hrelated "Spending on housing related ($ per week)";
label var transport "Spending on transport";
label var tx "Total spending";
label var ndx "Nondurable spending";
label var ndx_m "Nondurable spending including medical";
label var ndx_h "Nondurable spending including housing related";
label var ndx_mh "Nondurable spending including medical and housing related";
label var tx_mh "Total spending including medical and housing related";
label var totalx "Total spending including durables";
label var month "Month of interview";
label var all_item "All item RPI";
label var eq "Equivalence scale";
label var sp_employed "Spouse in work";
label var h_employed "Head of HH in work";
label var wfoodin "Share of nondurable spending on foodin";         
label var wfoodout "Share of nondurable spending on foodout";              
label var wothnd "Share of nondurable spending on othnd";                  
label var wmedical "Share of nondurable spending on medical";           
label var whrelated "Share of nondurable spending on hrelated";             
label var wrecrea "Share of nondurable spending on recrea";              
label var wtransport "Share of nondurable spending on transport";           
label var lpfoodin "log price foodin";                
label var lpfoodout "log price foodout";           
label var lpothnd "log price othnd";              
label var lprecrea "log price recrea";             
label var lphrelated "log price hrelated";                  
label var lptransport "log price transport";           
label var lpmedical "log price medical";                
label var ghs "Health data taken from General Household Survey";            
label var srh_na "Health non-applicable/missing";        
label var res_srh1 "Residualised srh1";   
label var res_srh2 "Residualised srh2";  
label var res_srh3 "Residualised srh3";     

foreach var in hlthser medic drugs hospital elderly tx ndx ndx_m ndx_h ndx_mh tx_mh {; 
	local varlabel : var label `var';
	local lowercaselab = lower("`varlabel'");
	label var r`var' "Real `lowercaselab'";
	label var eqr`var' "Real equivalised `lowercaselab'";
};

label var numkids1618 "# of kids 16-18";
label var cpi_u "Consumer prices index (US)";
label var rhealthin "Real health insurance spending";
label var eqrhealthin "Equivalised real health insurance spending";
label var rmedical "Real medical spending";
label var eqrmedical "Equivalised real medical spending";
label var lneqrtx_mh "Log equivalised real total spending";
label var lneqrndx "Log equivalised real nondurable spending";
label var lneqrndx_m "Log equivalised real nondurable spending (including medical)";
label var lneqrndx_h "Log equivalised real nondurable spending (including hrelated)";
label var lneqrndx_mh "Log equivalised real nondurable spending (including medical + hrelated)";
label var rhhinc "Real hh income";
label var eqrhhinc "Equivalised real hh income";
label var lneqrhhinc "Log equivalised real hh income";
label var lnrndx_mh "Log real nondurable spending";
label var lnrndx_h "Log real nondurable spending (including hrelated)";
label var lnrndx_m "Log real nondurable spending (including medical)";
label var lnrhhinc "Log real hh income";
label var cohort "Birth cohort";
label var cohortage "mid-point age of birth cohort";
label var lnmortrisk "Log mortality risk";
label var single "Single dummy";

save "fesolderages45_79", replace;