* Attempt to match table 11.19 in the NFHS-5 final report

* Specify workspace
cd e:\DHS\DHS_data\IA

use "C:\Users\26216\ICF\Analysis - Shared Resources\Data\DHSdata\IAIR7EFL.DTA", clear

* Loop to rename and relabel the s365* variables
local lletters a b c d e f g h i j k l m n o p q r s x
local ltopic=1
foreach ll of local lletters{
local llabel : variable label s365`ll'
scalar slabel=regexr("`llabel'","services/matters talked about in last 3 months: ","")
scalar slabel_`ll'=strproper(slabel)
quietly gen S`ltopic'=100*s365`ll'
local ltopic=`ltopic'+1
}

* Prepare for a line for the totals
gen S100=.

keep if s359==1 | s361==1
gen wt=v005/1000000

* Define the subpopulations in the four columns
gen col_1=1 if v501==0
gen col_2=1 if v501>0 & v213==1
gen col_3=1 if v501>0 & v213==0 & v313>0 & v313<.
gen col_4=1 if v501>0 & v213==0 & v313==0

keep wt S* col_*
save temp0.dta, replace

* Loop over the columns
quietly forvalues lc=1/4 {
use temp0.dta, clear
keep if col_`lc'==1
rename col_`lc' col
total col [iweight=wt]
matrix B=e(b)
scalar stot=B[1,1]
keep wt S* col
collapse (mean) S* col [iweight=wt]
reshape long S, i(col) j(topic)
replace S=stot if topic==100
rename S S_`lc'
  if `lc'>1 {
  merge 1:1 topic using temp_1.dta
  drop _merge col
  }
save temp_1.dta, replace
}

* Cleanup and formatting
rename S* column*
gen str30 topic_label="."
  local ltopic=1
  quietly foreach ll of local lletters {
  replace topic_label=slabel_`ll' if topic==`ltopic'
  local ltopic=`ltopic'+1
  }
replace topic_label="Number of women" if topic==100
order topic topic_label *1 *2 *3 *4

format col* %10.1fc
list, table clean