Hello, I need to merge household and individual LIS data as I want to calculate pre and post poverty rates for retired men and women for selected countries. But I am not sure if its correct as the results look slightly weird. Could anyone kindly help ? Thanks in advance!

Below example for women (sex==2)

---

di "** POVERTY RATE ELDERLY BRICS G20 **"
** POVERTY RATE ELDERLY BRICS G20 **

global varshh "hid dhi hifactor hi33 hiprivate hpopwgt nhhmem nhhmem17 nhhmem65"

global varspp "hid ppopwgt age sex if sex==2"

global datasets " cn13 in11 ru17 za17 br16 au14 ca17 fr10 de16 it16 mx18 kr12 uk16 us18"


program define make_variables
gen miss_comp = 0
quietly replace miss_comp=1 if dhi==. | hifactor==. | hi33==. | hiprivate==. | hpopwgt ==.| nhhmem ==. | nhhmem17 ==. | nhhmem65 ==.
quietly drop if miss_comp==1
sum dhi [w=hpopwgt*nhhmem], de

*Generate new income variables
*market income
gen mi = hifactor + hiprivate + hi33

global topline = 10*r(p50)

*Apply top and bottom codes and OECD equivalence scale
foreach var in mi dhi {
cap drop e`var'_b
gen e`var'_b = `var'
replace e`var'_b = 0.01*r(mean) if `var'<0.01*r(mean)
replace e`var'_b = $topline if `var'>$topline
replace e`var'_b = (e`var'_b/(1+.5*(nhhmem-1-nhhmem17)+.3*nhhmem17))
}

* Sample weights
* create person weight as hwgt times number of household member
quietly sum edhi_b [w=hpopwgt*nhhmem], de
* Child weights as hwgt times number of household members 17 or younger
quietly sum edhi_b [w=hpopwgt*nhhmem17], de
* create elderly weight as hwgt times number of household members 65 or older
quietly sum edhi_b [w=hpopwgt*nhhmem65], de

global povline = r(p50)*0.5
end

foreach ccyy in us18 mx18 it16 kr12 ca17 au14 uk16 de16 fr10 cn13 in11 ru17 br16 za15 {
quietly display "`ccyy'"
quietly use $varshh using $`ccyy'h, clear
quietly merge m:1 hid using $`ccyy'h, keepusing($varshh)
quietly make_variables
quietly foreach var in mi dhi{
quietly gen byte poor`var'=(e`var'_b<$povline)
quietly sum poor`var' [w=ppopwgt] if age >=65
quietly local povrate`var' : di %9.2f r(mean)*100

}
if "`ccyy'" == "us18" di "dataset, povrate_mi, povrate_dhi"
di "`ccyy',`povratemi',`povratedhi'"
}