Formatting Table Output in Stata: Percentages per Column and Single Row per Diagnosis

[Tim Wallner]

Dear Stata Community,

I am using the table command in Stata to generate a summary table of in-hospital mortality for different ICD-10 codes. However, I am struggling with formatting the output correctly.

Current Output Issue

My current table output includes an extra row for "Frequency (Percent%)" under each diagnosis. Instead, I would like a single row per diagnosis, where the percentages are calculated per column (not per row). The diagnosis is stored in a index variable diag.


Current Output (Incorrect Format)

------------------------------------------------------
                       |           inhospdeath        
                       |  died in hospital    survived
-----------------------+------------------------------
J36                    |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
J390                   |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
K1020                  |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
K1220                  |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
K1221                  |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
K1222                  |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
K1223                  |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
K1228                  |                              
  Frequency (Percent%) |         1 (50.0%)   1 (50.0%)
Total                  |                              
  Frequency (Percent%) |         8 (50.0%)   8 (50.0%)
------------------------------------------------------

The problem is that the table adds an extra line for "Frequency (Percent%)" under each diagnosis, making it less readable.

Desired Output (Correct Format)

I would like the percentages to be calculated per column, and each diagnosis should have a single row:

------------------------------------------------------
                       |           inhospdeath        
Diagnosis              |  died in hospital    survived
-----------------------+------------------------------
J36                    |         1 (50.0%)   1 (50.0%)
J390                   |         1 (50.0%)   1 (50.0%)
K1020                  |         1 (50.0%)   1 (50.0%)
K1220                  |         1 (50.0%)   1 (50.0%)
K1221                  |         1 (50.0%)   1 (50.0%)
K1222                  |         1 (50.0%)   1 (50.0%)
K1223                  |         1 (50.0%)   1 (50.0%)
K1228                  |         1 (50.0%)   1 (50.0%)
Total                  |         8 (50.0%)   8 (50.0%)
------------------------------------------------------

My example code:

* Example generated by -dataex-. For more info, type help dataex
clear
input int diag int inhospdeath
1 0
1 1
2 0
2 1
3 0
3 1
4 0
4 1
5 0
5 1
6 0
6 1
7 0
7 1
8 0
8 1
end
label values diag diag
label def diag 1 "J36", modify
label def diag 2 "J390", modify
label def diag 3 "K1020", modify
label def diag 4 "K1220", modify
label def diag 5 "K1221", modify
label def diag 6 "K1222", modify
label def diag 7 "K1223", modify
label def diag 8 "K1228", modify
label values inhospdeath inhospdeath_labels
label def inhospdeath_labels 0 "survived", modify
label def inhospdeath_labels 1 "died in hospital", modify

table (diag) (inhospdeath) , statistic(freq) statistic(percent, across(inhospdeath)) totals(inhospdeath) nformat(%5.1f percent) sformat("(%s%%)" freq percent)
collect composite define both = frequency percent
collect style autolevels result both, clear
collect style autolevels inhospdeath 1 0 .m, clear
collect style header diag, title(hide)
collect preview

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input int diag int inhospdeath
1 0
1 1
2 0
2 1
3 0
3 1
4 0
4 1
5 0
5 1
6 0
6 1
7 0
7 1
8 0
8 1
end
label values diag diag
label def diag 1 "J36", modify
label def diag 2 "J390", modify
label def diag 3 "K1020", modify
label def diag 4 "K1220", modify
label def diag 5 "K1221", modify
label def diag 6 "K1222", modify
label def diag 7 "K1223", modify
label def diag 8 "K1228", modify
label values inhospdeath inhospdeath_labels
label def inhospdeath_labels 0 "survived", modify
label def inhospdeath_labels 1 "died in hospital", modify

table (diag) (inhospdeath) , statistic(freq) statistic(percent, across(inhospdeath)) ///
totals(inhospdeath) nformat(%5.1f percent) sformat("(%s%%)" percent)
collect composite define both = frequency percent
collect style autolevels result both, clear
collect style autolevels inhospdeath 1 0 .m, clear
collect style header diag, title(hide)
collect style header result, level(hide)
collect preview

Res.:

. collect preview

-------------------------------------
      |           inhospdeath        
      |  died in hospital    survived
------+------------------------------
J36   |         1 (50.0%)   1 (50.0%)
J390  |         1 (50.0%)   1 (50.0%)
K1020 |         1 (50.0%)   1 (50.0%)
K1220 |         1 (50.0%)   1 (50.0%)
K1221 |         1 (50.0%)   1 (50.0%)
K1222 |         1 (50.0%)   1 (50.0%)
K1223 |         1 (50.0%)   1 (50.0%)
K1228 |         1 (50.0%)   1 (50.0%)
Total |         8 (50.0%)   8 (50.0%)
-------------------------------------

[Hemanshu Kumar]

I think OP also wanted column-wise percentages instead of this. Here's my variation on the above code:

table diag inhospdeath, stat(freq) stat(percent, across(diag)) totals(inhospdeath)
collect style cell result[percent], sformat((%s%%))
collect composite define both = frequency percent, trim override
collect style header result, level(hide)
collect style header diag, title(hide)
collect layout (diag#result[both]) (inhospdeath)

which produces:

. collect preview

---------------------------------------
      |            inhospdeath         
      |     survived   died in hospital
------+--------------------------------
J36   |   1 (12.50%)         1 (12.50%)
J390  |   1 (12.50%)         1 (12.50%)
K1020 |   1 (12.50%)         1 (12.50%)
K1220 |   1 (12.50%)         1 (12.50%)
K1221 |   1 (12.50%)         1 (12.50%)
K1222 |   1 (12.50%)         1 (12.50%)
K1223 |   1 (12.50%)         1 (12.50%)
K1228 |   1 (12.50%)         1 (12.50%)
Total |  8 (100.00%)        8 (100.00%)
---------------------------------------

[Andrew Musau]

I think OP also wanted column-wise percentages instead of this.

Yes, I missed that part.