Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • row and column totals from tab matrix

    Hi there

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

    Code:
    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.

  • #2
    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.


    Code:
    . 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

    Comment


    • #3
      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".
      Code:
      sysuse auto, clear
      bysort foreign: tab headroom rep78, matcell(mymat)
      mat list mymat

      Comment


      • #4
        You have to loop there. Or do it differently.

        Code:
        contract foreign headroom rep78
        etc.

        Comment

        Working...
        X