row and column totals from tab matrix

[tim bradshaw]

Hi there

I am using the following putexcel command to insert my simple two-way frequency table (agegroup by year) into an Excel spreadsheet:

tab year agegrp, matcell(freq) matrow(years)
putexcel A1=("Year") B1=("0-4") C1=("5-9") D1=("10-14") E1=("15-19") F1=("20-24") G1=("25+"), sh(1)
putexcel A2=matrix(years) B2=matrix(freq) using "\\:myspreadsheet.xls", sh(1) modify

.. which works nicely. However, I am missing the row and column totals. These are displayed in the Stata results window so I expect they are stored somewhere. I'd be grateful for any advice on how these can be recalled so that I can place them into the Excel spreadsheet too.

(I could, of course, easily do this in Excel itself but I am trying to get to grips with using putexcel!).

Many thanks for reading.

[Nick Cox]

The bad news is that they are not stored, even optionally.

The better news is that there are one-line ways of getting sums using either Stata or Mata. Here are two:

putexcel I have never used. But that doesn't seem to be the issue here.

. sysuse auto, clear
(1978 Automobile Data)

. tab fore rep78, matcell(mymat)

           |                   Repair Record 1978
  Car type |         1          2          3          4          5 |     Total
-----------+-------------------------------------------------------+----------
  Domestic |         2          8         27          9          2 |        48
   Foreign |         0          0          3          9          9 |        21
-----------+-------------------------------------------------------+----------
     Total |         2          8         30         18         11 |        69


.
. mata : st_matrix("coltot", colsum(st_matrix("mymat")))

. mata : st_matrix("rowtot", rowsum(st_matrix("mymat")))

.
. matrix coltotal = J(1, rowsof(mymat), 1) * mymat

. matrix rowtotal = mymat * J(colsof(mymat), 1, 1)

.
. mat li coltot

coltot[1,5]
    c1  c2  c3  c4  c5
r1   2   8  30  18  11

. mat li coltotal

coltotal[1,5]
    c1  c2  c3  c4  c5
r1   2   8  30  18  11

.
. mat li rowtot

rowtot[2,1]
    c1
r1  48
r2  21

. mat li rowtotal

rowtotal[2,1]
    c1
r1  48
r2  21

[tim bradshaw]

That works perfectly, thanks Nick.
I hesitate to follow up straight away with another query, but I am now wondering if it is possible to store/fetch multiple matrices using "by" in conjunction with the tab command?
The following code generates two tables (one for domestic, one for foreign), but only the second matrix is "remembered".

sysuse auto, clear
bysort foreign: tab headroom rep78, matcell(mymat)
mat list mymat

[Nick Cox]

You have to loop there. Or do it differently.

contract foreign headroom rep78

etc.