Hello Statalist Group,
I am attempting to create tables from survey data using 'svy:tabulate.' Ideally, I want the table with one column with smoking variables ("yes" or "no") and four data columns (the weighted counts, number of observations, weighted percentage, and confidence intervals).
My first problem was that I don't know how to extract the confidence intervals, which are represented as lower bound (lb) and upper bound (ub) in the output; please see attached. When I tried 'ereturn list' command, the lower bound and upper bound were not in the results. So, I tried to calculate the confidence intervals with code.
Below is my code:
local smk "smk_bef smk_dur smk_aft"
levelsof `smk', local (smks)
/*Survey loop*/
foreach var of varlist `demo' {
svy: tab `var', per ci obs cellw(15) missing for(%15.4g)
}
/*Calculate CIs*/
parmest, save
list
rename estimate prop
/*Get CI for logits, then transform*/
tempvar logit selogit bound
gen `logit' = logit(prop)
gen `selogit' = stderr/(prop*(1-prop))
gen `bound' = invttail(dof,.025)*`selogit'
gen llim = invlogit(`logit'-`bound')
gen ulim = invlogit(`logit'+`bound')
format prop stderr llim ulim %6.3f
foreach v of varlist prop stderr llim ulim {
replace `v' = round(100*`v',.1)
/*Create matrices for data*/
mat counts = e(N_pop)*prop
mat obs = e(Obs)
mat prop = e(Prop)
mat pprop = e(Prop)*100 /*Multiply proportion by 100*/
/*Creat macros for data columns*/
loc a = colsof(counts)
loc b = colsof(obs)
loc c = colsof(pprop)
loc d = colsof(llim)
loc e = colsof(ulim)
loc ++i
/*Create loop for each smoking variable*/
forv j=1/`a' {
loc str "`:lab (`smks') `=el(e(Row),1,`j')''"
loc val1 = round(el(counts,1,`j'))
di "`str' : `val'"
loc ++i
}
forv j=1/`b' {
loc val2 = round(el(obs,1,`j'))
di "`str' : `val'"
loc ++i
}
forv j=1/`c' {
loc val3 = round(el(pprop,1,`j'), 0.01)
di "`str' : `val'"
loc ++i
}
forv j=1/`d' {
loc val4 = round(el(llim,1,`j'), 0.01)
di "`str' : `val'"
loc ++i
}
forv j=1/`e' {
loc val5 = round(el(ulim,1,`j'), 0.01)
di "`str' : `val'"
loc ++i
}
//*I also want the "llim" and "ulim" to be in one column separated by a dash (for example, 4.5-7.8)*//
/*Create table*/
loc k 2
putexcel set smk.xlsx, sheet (smokingvars) replace open
putexcel A`i'=("Smoking Variable") B`i'=("Estimated Number of Smokers (N)") C`i'=("Counts") D`i'=("Weighted Estimate (%)") E`i'=("95% CI") using results, modif
putexcel A`k'=("`str'") B`k'=("`val1'") C`k'=("`val2'") D`k'=("`val3'") E`k'=("`val4'-`val5'") using results, modify
putexcel A`i':E`i', bold italic font("Arial", 12, "black")
putexcel close
Please let me know if there is a more efficient way to extract the survey data (including the confidence intervals), and create a table in Excel. Thank you.
I am attempting to create tables from survey data using 'svy:tabulate.' Ideally, I want the table with one column with smoking variables ("yes" or "no") and four data columns (the weighted counts, number of observations, weighted percentage, and confidence intervals).
My first problem was that I don't know how to extract the confidence intervals, which are represented as lower bound (lb) and upper bound (ub) in the output; please see attached. When I tried 'ereturn list' command, the lower bound and upper bound were not in the results. So, I tried to calculate the confidence intervals with code.
Below is my code:
local smk "smk_bef smk_dur smk_aft"
levelsof `smk', local (smks)
/*Survey loop*/
foreach var of varlist `demo' {
svy: tab `var', per ci obs cellw(15) missing for(%15.4g)
}
/*Calculate CIs*/
parmest, save
list
rename estimate prop
/*Get CI for logits, then transform*/
tempvar logit selogit bound
gen `logit' = logit(prop)
gen `selogit' = stderr/(prop*(1-prop))
gen `bound' = invttail(dof,.025)*`selogit'
gen llim = invlogit(`logit'-`bound')
gen ulim = invlogit(`logit'+`bound')
format prop stderr llim ulim %6.3f
foreach v of varlist prop stderr llim ulim {
replace `v' = round(100*`v',.1)
/*Create matrices for data*/
mat counts = e(N_pop)*prop
mat obs = e(Obs)
mat prop = e(Prop)
mat pprop = e(Prop)*100 /*Multiply proportion by 100*/
/*Creat macros for data columns*/
loc a = colsof(counts)
loc b = colsof(obs)
loc c = colsof(pprop)
loc d = colsof(llim)
loc e = colsof(ulim)
loc ++i
/*Create loop for each smoking variable*/
forv j=1/`a' {
loc str "`:lab (`smks') `=el(e(Row),1,`j')''"
loc val1 = round(el(counts,1,`j'))
di "`str' : `val'"
loc ++i
}
forv j=1/`b' {
loc val2 = round(el(obs,1,`j'))
di "`str' : `val'"
loc ++i
}
forv j=1/`c' {
loc val3 = round(el(pprop,1,`j'), 0.01)
di "`str' : `val'"
loc ++i
}
forv j=1/`d' {
loc val4 = round(el(llim,1,`j'), 0.01)
di "`str' : `val'"
loc ++i
}
forv j=1/`e' {
loc val5 = round(el(ulim,1,`j'), 0.01)
di "`str' : `val'"
loc ++i
}
//*I also want the "llim" and "ulim" to be in one column separated by a dash (for example, 4.5-7.8)*//
/*Create table*/
loc k 2
putexcel set smk.xlsx, sheet (smokingvars) replace open
putexcel A`i'=("Smoking Variable") B`i'=("Estimated Number of Smokers (N)") C`i'=("Counts") D`i'=("Weighted Estimate (%)") E`i'=("95% CI") using results, modif
putexcel A`k'=("`str'") B`k'=("`val1'") C`k'=("`val2'") D`k'=("`val3'") E`k'=("`val4'-`val5'") using results, modify
putexcel A`i':E`i', bold italic font("Arial", 12, "black")
putexcel close
Please let me know if there is a more efficient way to extract the survey data (including the confidence intervals), and create a table in Excel. Thank you.
Attached Files
Comment