Export multiple tabulations in one excel sheet

[Richard Hassan]

Hi folks,

I am doing several two-way tabulations using tab and table commands. I have the same col variable (gender) and different row variables (income, state, education level, etc.). Some of my row variables are categorial (I am using tab command for those) and some are continuous (using table command for those). I want to:

1) export those results in one excel sheet (so that the sheet display tabulation across one col)

2) export results of the same row variables with age-group as col variable in different excel sheet in the same excel file (so the file show different tabulation of same row variables across different col variables).

Any advice?

[Daniel Schaefer]

Hi Richard, welcome to the forum. We usually ask that you provide some example data as well as the exact code you are working with. This reduces ambiguities in your question and gives us something to work with on our end. In this case, I'll just generate some made up data.

clear
set obs 100
gen x = runiformint(0, 1)
gen y = runiformint(0, 5)
gen z = runiformint(0, 5)

It seems to me the best way to export these results is to use table for both the categorical and quantitative data. That way you can use the powerful table-building features everywhere. Here is how I would address part two of your question:

collect clear
table (y) (x), statistic(frequency) statistic(percent, across(x))
collect export "example_file.xlsx", sheet("table 1") replace

table (z) (x), statistic(frequency) statistic(percent, across(x))
collect export "example_file.xlsx", sheet("table 2") modify

Which gives the following two tables:

. collect clear

. table (y) (x), statistic(frequency) statistic(percent, across(x))

---------------------------------------
              |             x          
              |      0       1    Total
--------------+------------------------
y             |                        
  0           |                        
    Frequency |     10       8       18
    Percent   |  55.56   44.44   100.00
  1           |                        
    Frequency |      7       6       13
    Percent   |  53.85   46.15   100.00
  2           |                        
    Frequency |      8       7       15
    Percent   |  53.33   46.67   100.00
  3           |                        
    Frequency |     11       9       20
    Percent   |  55.00   45.00   100.00
  4           |                        
    Frequency |     14       6       20
    Percent   |  70.00   30.00   100.00
  5           |                        
    Frequency |      7       7       14
    Percent   |  50.00   50.00   100.00
  Total       |                        
    Frequency |     57      43      100
    Percent   |  57.00   43.00   100.00
---------------------------------------

. collect export "example_file.xlsx", sheet("table 1") replace
(collection Table exported to file example_file.xlsx)

. 
. table (z) (x), statistic(frequency) statistic(percent, across(x))

---------------------------------------
              |             x          
              |      0       1    Total
--------------+------------------------
z             |                        
  0           |                        
    Frequency |     11       4       15
    Percent   |  73.33   26.67   100.00
  1           |                        
    Frequency |     14       5       19
    Percent   |  73.68   26.32   100.00
  2           |                        
    Frequency |      6      11       17
    Percent   |  35.29   64.71   100.00
  3           |                        
    Frequency |      9       7       16
    Percent   |  56.25   43.75   100.00
  4           |                        
    Frequency |      8       8       16
    Percent   |  50.00   50.00   100.00
  5           |                        
    Frequency |      9       8       17
    Percent   |  52.94   47.06   100.00
  Total       |                        
    Frequency |     57      43      100
    Percent   |  57.00   43.00   100.00
---------------------------------------

. collect export "example_file.xlsx", sheet("table 2") modify 
(collection Table exported to file example_file.xlsx)

. 
end of do-file

When I inspect the excel file, I see the two tables are exported correctly. I'm not exactly sure what you are describing in part one, but I think it might be something like this table:

. collect layout (y#result z#result) (x)

Collection: Table
      Rows: y#result z#result
   Columns: x
   Table 1: 44 x 3

---------------------------------------
              |             x          
              |      0       1    Total
--------------+------------------------
y             |                        
  0           |                        
    Frequency |     10       8       18
    Percent   |  55.56   44.44   100.00
  1           |                        
    Frequency |      7       6       13
    Percent   |  53.85   46.15   100.00
  2           |                        
    Frequency |      8       7       15
    Percent   |  53.33   46.67   100.00
  3           |                        
    Frequency |     11       9       20
    Percent   |  55.00   45.00   100.00
  4           |                        
    Frequency |     14       6       20
    Percent   |  70.00   30.00   100.00
  5           |                        
    Frequency |      7       7       14
    Percent   |  50.00   50.00   100.00
  Total       |                        
    Frequency |     57      43      100
    Percent   |  57.00   43.00   100.00
z             |                        
  0           |                        
    Frequency |     11       4       15
    Percent   |  73.33   26.67   100.00
  1           |                        
    Frequency |     14       5       19
    Percent   |  73.68   26.32   100.00
  2           |                        
    Frequency |      6      11       17
    Percent   |  35.29   64.71   100.00
  3           |                        
    Frequency |      9       7       16
    Percent   |  56.25   43.75   100.00
  4           |                        
    Frequency |      8       8       16
    Percent   |  50.00   50.00   100.00
  5           |                        
    Frequency |      9       8       17
    Percent   |  52.94   47.06   100.00
  Total       |                        
    Frequency |     57      43      100
    Percent   |  57.00   43.00   100.00
---------------------------------------

If so, here is how I would modify the code above to produce this table. Notice the addition of the "append" option when I create the second table.

clear
set obs 100
set seed 12345
gen x = runiformint(0, 1)
gen y = runiformint(0, 5)
gen z = runiformint(0, 5)

collect clear
table (y) (x), statistic(frequency) statistic(percent, across(x))
collect export "example_file.xlsx", sheet("table 1") replace

table (z) (x), statistic(frequency) statistic(percent, across(x)) append
collect export "example_file.xlsx", sheet("table 2") modify

collect layout (y#result z#result) (x)
collect export "example_file.xlsx", sheet("Combined Table") modify