Hello! I'm using Stata 14.1 on Windows. I am creating a table that has mean plus/minus standard deviation for several different variables. The variables are mean scores for Likert scale surveys. Each question is scored from 0-100 (0, 25,50, 75, or 100), and a participant's survey score is the mean of their responses to each question. The table will have a mean and SD of each participant's survey score. I can generate those values fine with -summary- or -collapse-, but getting those values into a table (with mean and SD in the same cell) eludes me—so far I’ve been copying from Stata into Word by hand. I've tried a few ways (like using -matrix-, or using r() to call stored results) but haven't gotten any to work.
My data is confidential so I generated fake data in Stata using rnormal (mean 73, SD 15). Here's a sample of the generated data, let me know if there's a better way to upload it:
I tried using the estout package:
but the resulting excel file has a bunch of blank space and would require a lot of copy/pasting and doesn't have the plus/minus symbol. Additionally, numbers are actually formulas: ="17.3" instead of 17.3.
I also tried using collapse: my plan was to use the concatenate function in excel to concatenate the mean, plus/minus symbol, and the SD, then paste into word.
The exported excel file had too many decimals, so I tried using -round- but the excel file still went to 13 decimals. I used -round- then -tostring-, which worked in Stata but the excel file had what I think is a floating point error (e.g. number is 72.80000305 instead of 72.8). Next I tried to change the collapsed variables from float to int using -recast-:
but the output said "1 value would be changed; not changed".
Here's an example of the table I'm going for, with fake means/SDs:
I first used collapse to generate the means and SDs all at once (instead of separately like in my 2nd block of code), but that resulted in a single "observation" where half the variables were means and half were SDs, and I couldn't see an easy way to export them (I'll be making more tables, so doing 15 or so concatenate functions would be slower than copying by hand). Is there a way to have collapse create two "observations," where one row is means and the second is SDs? Because if so, reshaping it and then using concatenate would work really well.
My data is confidential so I generated fake data in Stata using rnormal (mean 73, SD 15). Here's a sample of the generated data, let me know if there's a better way to upload it:
I tried using the estout package:
Code:
estpost summarize pql_c_total_mean_wk0 pql_c_pf_mean_wk0 pql_c_ef_mean_wk0 pql_c_sf_mean_wk0 pql_c_sch_mean_wk0 qol_c_total_mean_wk0 qol_c_self_mean_wk0 qol_c_soc_mean_wk0 qol_c_env_mean_wk0 ///
pql_c_total_mean_wk21 pql_c_pf_mean_wk21 pql_c_ef_mean_wk21 pql_c_sf_mean_wk21 pql_c_sch_mean_wk21 qol_c_total_mean_wk21 qol_c_self_mean_wk21 qol_c_soc_mean_wk21 qol_c_env_mean_wk21 ///
pql_c_total_mean_6m pql_c_pf_mean_6m pql_c_ef_mean_6m pql_c_sf_mean_6m pql_c_sch_mean_6m qol_c_total_mean_6m qol_c_self_mean_6m qol_c_soc_mean_6m qol_c_env_mean_6m // gen table with mean and sd
eststo summstats
esttab summstats using "X:\Mark\Writing\Output\table3.csv", replace main(mean %6.1f) aux(sd %6.1f) noparentheses
I also tried using collapse: my plan was to use the concatenate function in excel to concatenate the mean, plus/minus symbol, and the SD, then paste into word.
Code:
preserve
collapse (mean) pql_c_total_mean_wk0 pql_c_pf_mean_wk0 pql_c_ef_mean_wk0 pql_c_sf_mean_wk0 pql_c_sch_mean_wk0 qol_c_total_mean_wk0 qol_c_self_mean_wk0 qol_c_soc_mean_wk0 qol_c_env_mean_wk0 ///
pql_c_total_mean_wk21 pql_c_pf_mean_wk21 pql_c_ef_mean_wk21 pql_c_sf_mean_wk21 pql_c_sch_mean_wk21 qol_c_total_mean_wk21 qol_c_self_mean_wk21 qol_c_soc_mean_wk21 qol_c_env_mean_wk21 ///
pql_c_total_mean_6m pql_c_pf_mean_6m pql_c_ef_mean_6m pql_c_sf_mean_6m pql_c_sch_mean_6m qol_c_total_mean_6m qol_c_self_mean_6m qol_c_soc_mean_6m qol_c_env_mean_6m
ds
foreach var in `r(varlist)' {
replace `var' = round(`var', 0.1)
}
export excel using "\\childrens\research\nik\Mark\Writing\Output\table3.xlsx", sheet(mean) sheetreplace
restore
preserve
collapse (sd) pql_c_total_mean_wk0 pql_c_pf_mean_wk0 pql_c_ef_mean_wk0 pql_c_sf_mean_wk0 pql_c_sch_mean_wk0 qol_c_total_mean_wk0 qol_c_self_mean_wk0 qol_c_soc_mean_wk0 qol_c_env_mean_wk0 ///
pql_c_total_mean_wk21 pql_c_pf_mean_wk21 pql_c_ef_mean_wk21 pql_c_sf_mean_wk21 pql_c_sch_mean_wk21 qol_c_total_mean_wk21 qol_c_self_mean_wk21 qol_c_soc_mean_wk21 qol_c_env_mean_wk21 ///
pql_c_total_mean_6m pql_c_pf_mean_6m pql_c_ef_mean_6m pql_c_sf_mean_6m pql_c_sch_mean_6m qol_c_total_mean_6m qol_c_self_mean_6m qol_c_soc_mean_6m qol_c_env_mean_6m
ds
foreach var in `r(varlist)' {
replace `var' = round(`var', 0.1)
}
export excel using "\\childrens\research\nik\Mark\Writing\Output\table3.xlsx", sheet(sd) sheetreplace
restore
Code:
ds
foreach var in `r(varlist)' {
recast int `var'
}
Here's an example of the table I'm going for, with fake means/SDs:
I first used collapse to generate the means and SDs all at once (instead of separately like in my 2nd block of code), but that resulted in a single "observation" where half the variables were means and half were SDs, and I couldn't see an easy way to export them (I'll be making more tables, so doing 15 or so concatenate functions would be slower than copying by hand). Is there a way to have collapse create two "observations," where one row is means and the second is SDs? Because if so, reshaping it and then using concatenate would work really well.
Attached Files
Comment