Reporting column percentages in two way table with Table command (Stata 17)

[Carlos Lagorio]

How can I report column percentages when using Table command in Stata 17?

. webuse auto, clear
(1978 automobile data)

. table rep78 foreign, stat(percent)



In the example above percentages are calculated conditional on the marginal totals.

Instead I'd like to calculate percentages by columns, i.e. by Car Origin in the example above.

Can someone explain how the new Table command should be written to report column percentages?

Thank you,

Carlos Lagorio

[Clyde Schechter]

. table rep78 foreign, stat(percent, across(rep78))

-------------------------------------------------
                   |           Car origin        
                   |  Domestic   Foreign    Total
-------------------+-----------------------------
Repair record 1978 |                             
  1                |      4.17               2.90
  2                |     16.67              11.59
  3                |     56.25     14.29    43.48
  4                |     18.75     42.86    26.09
  5                |      4.17     42.86    15.94
  Total            |    100.00    100.00   100.00
-------------------------------------------------

[Carlos Lagorio]

Thank you Clyde.

Follow up.

When I use the tabulate command and the "nof co m" options my percentages add up to 100% within columns.

Using the same dataset and variables, when I replicate your command from the previous message, my columns do not add up to 100%.

See image below.

How can I replicate my results from the tabulate command using the table command?

Thank you again.

[Clyde Schechter]

As you can see from #2, the approach I suggested worked in the auto.dta. Something is different about the data you are working with, but I cannot tell what. So please post back using the -dataex- command to show example data that reproduces this behavior and I will try to troubleshoot this. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Added: Just a guess, but try adding the -missing- option to the -table- command and see if that does the trick. It seems your -tabulate- command specifically treats missing as a regular value, whereas the -table- command does not. (But I still don't understand why it doesn't add the columns up to 100%, anyway.)

[David Radwin]

Maybe use tablecol by Nick Winter from SSC?

. ssc describe tablecol

------------------------------------------------------------------------------------------------------------
package tablecol from http://fmwww.bc.edu/repec/bocode/t
------------------------------------------------------------------------------------------------------------

TITLE
      'TABLECOL': module to add column percentages to table command

DESCRIPTION/AUTHOR(S)
      
      tablecol is a wrapper program for table which adds column
      percentages  to the output.  Essentially this provides the
      functionality of the column option of the tabulate command,
      along with the ability to  specify supercolumns and superrows in
      table. This is version 1.1.0 of the software.
      
      KW: tables
      KW: percentages
      
      Requires: Stata version 6.0
      
      
      Author: Nick Winter,  University of Virginia
      Support: email  [email protected]
      
      Distribution-Date: 20101105
      

INSTALLATION FILES                             (type net install tablecol)
      tablecol.ado
      tablecol.hlp
------------------------------------------------------------------------------------------------------------
(type ssc install tablecol to install)

. tablecol rep78 foreign, colpct col

----------------------------------------
Repair    |
record    |          Car origin         
1978      | Domestic   Foreign     Total
----------+-----------------------------
        1 |        2                   2
          |     4.17                2.90
          | 
        2 |        8                   8
          |    16.67               11.59
          | 
        3 |       27         3        30
          |    56.25     14.29     43.48
          | 
        4 |        9         9        18
          |    18.75     42.86     26.09
          | 
        5 |        2         9        11
          |     4.17     42.86     15.94

----------------------------------------

.

[Carlos Lagorio]

Hi Clyde,

Thanks again for your help.

See new table exports (comparing tabulate and table with and without missing option) and dataex output as requested below

-Carlos

David: thanks for the tablecol recommendation. I won't give up on table just yet, but will try tablecol eventually if table doesn't meet my need.

* Example generated by -dataex-. For more info, type help dataex
clear
input str2 id long temp
"in" 4
"vn" 4
"in" 6
"ng" 4
"us" 2
"us" 7
"ng" 4
"in" 4
"in" 6
"us" 1
"us" 1
"in" 7
"ng" 4
"us" 2
"vn" 7
"ng" 4
"us" 2
"vn" 4
"vn" 2
"ng" 4
"in" 7
"vn" 2
"ng" 4
"vn" 1
"in" 7
"us" 2
"us" 1
"in" 4
"vn" 2
"ng" 4
"us" 1
"us" 2
"in" 7
"in" 7
"us" 2
"vn" 1
"ng" 4
"ng" 4
"in" 6
"in" 7
"ng" 4
"us" 2
"vn" 2
"in" 2
"mx" 1
"in" 1
"vn" 2
"vn" 2
"vn" 2
"vn" 4
"in" 4
"us" 6
"in" 7
"in" 1
"in" 7
"in" 7
"vn" 1
"in" 4
"us" 2
"vn" 4
"in" 2
"vn" 4
"ng" 4
"mx" 1
"us" 4
"vn" 4
"mx" 4
"vn" 2
"mx" 2
"us" 2
"ng" 4
"in" 7
"ng" 4
"vn" 1
"in" 6
"mx" 7
"in" 7
"in" 7
"us" 2
"in" 1
"ng" 4
"us" 1
"vn" 2
"vn" 1
"ng" 5
"vn" 2
"mx" 1
"vn" 2
"vn" 4
"vn" 1
"vn" 2
"us" 4
"ng" 4
"us" 4
"vn" 4
"us" 5
"us" 4
"in" 7
"us" 2
"us" 2
end
label values temp temp
label def temp 1 "1", modify
label def temp 2 "2", modify
label def temp 4 "4", modify
label def temp 5 "5", modify
label def temp 6 "6", modify
label def temp 7 "7", modify

[Clyde Schechter]

I'm stumped! This may be a bug in -table-. I'm a little shy about asserting that because it may be that I am simply missing something about -table-. It is, after all, a new and complicated command, so maybe I'm just using it wrong. But I also find it hard to understand how the results I'm seeing are arrived it. If nobody comes up with a solution to this, I would report the issue to Stata tech support.

[Carlos Lagorio]

Yep, I was stumped too. More so because the command works for auto.dta but not for my own dataset. Incidentally, from the dataex, if I switch the rows and columns, the across option works as intended and columns add up to 100%.

Forgive my ignorance, how do I report the issue to Stata Tech support?

[Clyde Schechter]

Send an email to [email protected].

Your email should include the output of the -about- command (which will show them what setup you are working on and verify your license number), example data (use -dataex- for a small data set like this problem has), show the commands you are using and the output you are getting from Stata (exactly, by copy/paste from Results window or log file), and an explanation of why you think it's wrong. Basically your email should be just like a post to Statalist would be, except that for tech support you should also give the results of the -about- command.

[Federico Bindi]

Carlos Lagorio, is your ID variable encoded? I had the same issue with a categorical string variable, but it disappeared after encoding.

[Jeff Pitblado (StataCorp)]

The above bug in table was fixed in the Stata 17 update on 17nov2021.

[Leonardo Guizzetti]

Thanks, Jeff Pitblado (StataCorp) .