multiple formats for different data in one table row

[Doug Hess]

I feel like I should be able to easily solve this problem, but after pouring over the PDF manuals, I'm still stumped.

I wish to have a table where the rows are sums of county-level statistics by state (US) with three columns: two columns sum count variables and one column that gives the median ratio (first column/second column).

The three variables are 1) the total applications from HHS offices, 2) the total applications from all sources, and 3) the first divided by the second.

I want the first two columns to use commas and no decimal points, but I cannot get the commas in there and the CA sum keeps using use scientific notation. The third column comes out fine (three decimal points).

It's that simple. What am I missing? Is the problem because the data are county-level and I want data reported at the state level? I don't see why that would be the problem, but I am not sure what is doing on.

Thank you and, yes, I feel like a fool. I'm using Stata/MP 15.1.

drop hhs_rat*
format %13.0fc hhs_apps_total
format %13.0fc total_apps

gen hhs_ratio = hhs_apps_total/total_apps
format %5.3f hhs_ratio

table state_abb if statefips<10, c(sum hhs_apps_total sum total_apps median hhs_ratio) cellw(20)
/*
----------------------------------------------------------
State     |
Name      |
(Abbrevia |
tion)     |  sum(hhs_ap~l)   sum(total_~s)  mean(hhs_ra~o)
----------+-----------------------------------------------
       AK |           6090         1079008           0.006
       AL |          39093         1439361           0.035
       AR |          18507          556911           0.049
       AZ |           8059         2943553           0.012
       CA |         177354        1.35e+07           0.013
       CO |          38028         3195131           0.017
       CT |           5060         1247433           0.002
----------------------------------------------------------

[Ali Atia]

As far as I can tell there is no way to individually format columns in a table. The format() option allows you to specify a format, but it applies to all columns.

An alternative solution is to calculate the statistics yourself using -collapse-, format them as you wish, and use tabdisp to display them as in the following example:

sysuse auto, clear
replace mpg = mpg*1000
replace length = length*100000
gen new = mpg/length
expand 3

preserve
collapse (sum) mpg length (median) new, by(make)
format %13.0fc mpg length
format %5.3f new
tabdisp make in 1/5, c(mpg length new) cellw(20)
restore

------------------------------------------------------------------------------
Make and    |
Model       |            (sum) mpg          (sum) length            (p 50) new
------------+-----------------------------------------------------------------
AMC Concord |               66,000            55,800,000                 0.001
  AMC Pacer |               51,000            51,900,000                 0.001
 AMC Spirit |               66,000            50,400,000                 0.001
  Audi 5000 |               51,000            56,700,000                 0.001
   Audi Fox |               69,000            52,200,000                 0.001
------------------------------------------------------------------------------

[Doug Hess]

As far as I can tell there is no way to individually format columns in a table. The format() option allows you to specify a format, but it applies to all columns.

An alternative solution is to calculate the statistics yourself using -collapse-, format them as you wish, and use tabdisp to display them as in the following example:

Thanks. It's odd that -table-, which is powerful in many ways, doesn't allow formatting cells. I thought I was missing something obvious.

[Ali Atia]

It is somewhat odd that it wasn't added earlier, but I believe it exists in Stata 17. I haven't upgraded yet myself, but the new manual indicates that nformat() is a repeated option which can be applied to individual columns (https://www.stata.com/manuals/rtable.pdf).