Table with mean, SD, percent in one row per var

[Daniel Schnyder]

Dear all
I am struggling to build a simple table with the table command.
There are 25 variables, named HSUS_* (ratings from Likert scales).
I want to use hosp_off as grouping var.
I need a table with means, SDs and % with a specific value of HSUS_* (HSUS_*>4), stratified by values of hosp_off. So HSUS_* vars would be listed as rows and six values in columns.
This code brings me nearly there but I do not manage to get the percentage values of HSUS_* in the same row as the other statistics.

table (var) (hosp_off), statistic(mean HSUS_1 HSUS_2)  statistic(sd HSUS_1 HSUS_2  ) statistic(fvpercent 5.HSUS_1 5.HSUS_2 )
collect recode result mean = column1  sd  = column2 fvpercent=column3
collect layout (var) (hosp_off#result[column1 column2 column3] )

Any help would be very appreciated.
Thank you, and happy holidays!

* Example generated by -dataex-. For more info, type help dataex
clear
input int CASE byte hosp_off int(HSUS_1 HSUS_2 HSUS_3)
1291 1 4 5 4
1309 1 4 4 4
1478 1 4 3 3
1610 1 4 4 3
1916 1 . . .
 549 1 3 4 3
 903 1 4 4 3
 556 1 5 3 3
1855 1 4 4 3
1132 1 . . .
end

------------------ copy up to and including the previous line ------------------

[Andrew Musau]

Thanks for the reproducible example. Look at levels of your dimension "var".

* Example generated by -dataex-. For more info, type help dataex
clear
input int CASE byte hosp_off int(HSUS_1 HSUS_2 HSUS_3)
1291 1 4 5 4
1309 1 4 4 4
1478 1 4 3 3
1610 1 4 4 3
1916 1 . . .
 549 1 3 4 3
 903 1 4 4 3
 556 1 5 3 3
1855 1 4 4 3
1132 1 . . .
end

table (var) (hosp_off), statistic(mean HSUS_1 HSUS_2)  statistic(sd HSUS_1 HSUS_2  ) statistic(fvpercent 5.HSUS_1 5.HSUS_2 )
collect recode result mean = column1  sd  = column2 fvpercent=column3
collect levelsof var

Res.:

. collect levelsof var

Collection: Table
 Dimension: var
    Levels: HSUS_1 HSUS_2 5.HSUS_1 5.HSUS_2

.

These define the row alignment. You want "5.HSUS_1" to be aligned with "HSUS_1" and "5.HSUS_2" to be aligned with "HSUS_2". You need to reach out for collect remap to achieve this, see

help collect remap

* Example generated by -dataex-. For more info, type help dataex
clear
input int CASE byte hosp_off int(HSUS_1 HSUS_2 HSUS_3)
1291 1 4 5 4
1309 1 4 4 4
1478 1 4 3 3
1610 1 4 4 3
1916 1 . . .
 549 1 3 4 3
 903 1 4 4 3
 556 1 5 3 3
1855 1 4 4 3
1132 1 . . .
end

table (var) (hosp_off), statistic(mean HSUS_1 HSUS_2)  statistic(sd HSUS_1 HSUS_2  ) statistic(fvpercent 5.HSUS_1 5.HSUS_2 )
collect recode result mean = column1  sd  = column2 fvpercent=column3
collect remap var[5.HSUS_1]=var[HSUS_1], fortags(result[column3])
collect remap var[5.HSUS_2]=var[HSUS_2], fortags(result[column3])
collect layout (var) (hosp_off#result[column1 column2 column3] )

Res.:

. collect layout (var) (hosp_off#result[column1 column2 column3] )

Collection: Table
      Rows: var
   Columns: hosp_off#result[column1 column2 column3]
   Table 1: 2 x 6

---------------------------------------------------------------------
       |                            hosp_off                         
       |                1                            Total           
       |  column1    column2   column3   column1    column2   column3
-------+-------------------------------------------------------------
HSUS_1 |        4   .5345225      12.5         4   .5345225      12.5
HSUS_2 |    3.875   .6408699      12.5     3.875   .6408699      12.5
---------------------------------------------------------------------

[Daniel Schnyder]

Ah, many thanks - that does the trick!!

A second problem I encounter: Is there any way to show fvpercent not only for one level (HSUS_*=5) but summarized over two (HSUS_*=4|HSUS_*=5). Bascially, I want to show percentages of respondents how agreed OR agreed strongly to the statement of the scale.

Or would I need to create separate indicator variables (pos/neg responses) for which I then compute and print the mean?

Thank you once more

[Andrew Musau]

There is no syntax for factor variables that allows you to combine categories on the go. You need to create a new variable for that, e.g., using recode.

forval i=1/2{
    recode HSUS_`i' (5=4), gen(HSUS_`i'_R)
}
table (var) (hosp_off), statistic(mean HSUS_1 HSUS_2)  statistic(sd HSUS_1 HSUS_2 ) ///
statistic(fvpercent 4.HSUS_1_R 4.HSUS_2_R )

[Daniel Schnyder]

yes, okay, that was what I was thinking!