Adding a line for observations in the -collect- system

[Hemanshu Kumar]

Hello,

I am sure this must be trivial, but I have had no luck with it so far. I am trying the new -collect- system of building tables in Stata 17, and cannot figure out how to make a couple types of edits.

Consider the MWE:

        sysuse auto, clear
        collect clear
        table (foreign) ()  if price < 10000, stat(mean mpg) name(t1)
        table (foreign) ()  if price >= 10000, stat(mean mpg) name(t1) append
        collect layout (foreign) (cmdset)
        collect label levels cmdset 1 "Cheap" 2 "Expensive", modify
        collect style header cmdset, title(hide)
        collect style cell cell_type[row-header]#foreign[.m],border(top,pattern(single))        
        collect style cell cell_type[item]#foreign[.m],border(top,pattern(single))
        collect style cell , nformat(%5.2f)                
        collect preview

which produces the output

-------------------------------
           |  Cheap   Expensive
-----------+-------------------
Car origin |                   
  Domestic |  20.73       14.88
  Foreign  |  25.70       15.50
-----------+-------------------
  Total    |  22.28       15.00
-------------------------------

Can anyone tell me

1. how can I add a row to the bottom of the table that would tell us the observations in each column?
2. how can I remove the "Car origin" title from its current location and into the top-left box that is currently empty?
3. is there a way to introduce a gap between columns, for instance if we wanted the line above "Total" to be separated for the "Cheap" and "Expensive" columns?

Thank you in advance!

[Hemanshu Kumar]

Any thoughts on this, Stata gurus?

[Mark Horowitz]

I'm sure you've answered these questions by now. Here's my version for questions 1 & 2:

sysuse auto, clear
collect clear
gen value = price >= 10000

table (foreign) (value), stat(mean mpg) total(value)
table (var) (value), stat(count mpg) total(value) append // Answer to #1

collect recode result mean = column1 count = column1

collect label levels value 0 "Cheap" 1 "Expensive"
collect label levels cmdset 2 "N"

collect style header cmdset, title(hide)
collect style header foreign, title(hide) // Answer to #2

collect style header result, level(hide)
collect style header value, title(hide)

collect style cell cell_type[row-header]#foreign[.m],border(top,pattern(single)) border(bottom,pattern(single))
collect style cell cell_type[item]#foreign[.m],border(top,pattern(single)) border(bottom,pattern(single))

collect layout (foreign cmdset[2]) (value#result[column1])

--------------------------------
         |     Cheap   Expensive
---------+----------------------
Domestic |  20.72727      14.875
Foreign  |      25.7        15.5
---------+----------------------
Total    |  22.28125          15
---------+----------------------
N        |        64          10
--------------------------------

I thought the showcounts option on line 4 would work, but it provides counts for all cells, and I wasn't sure how to isolate the totals for just the categories of value.

For #3, I found an inelegant solution: put an empty category of value between "Cheap" and "Expensive" and then change the font color for that column to white. You can see the col title and count (0) in Stata, but when exported to Word, they're not visible. Curious though if you found better solutions to all of these.

sysuse auto, clear
collect clear
gen value = price >= 10000
// 3 new lines
insobs 1
replace value = 2 in l
recode value (2=1) (1=2)

table (foreign) (value), stat(mean mpg) total(value)
table (var) (value), stat(count mpg) total(value) append

collect recode result mean = column1 count = column1

collect label levels value 0 "Cheap" 2 "Expensive" // modified
collect label levels cmdset 2 "N"

collect style header cmdset, title(hide)
collect style header foreign, title(hide)

collect style header result, level(hide)
collect style header value, title(hide)

collect style cell value[1], font(,color(white)) // new

collect style cell cell_type[row-header]#foreign[.m],border(top,pattern(single)) border(bottom,pattern(single))
collect style cell cell_type[item]#foreign[.m],border(top,pattern(single)) border(bottom,pattern(single))

collect layout (foreign cmdset[2]) (value#result[column1])

------------------------------------
         |     Cheap   1   Expensive
---------+--------------------------
Domestic |  20.72727          14.875
Foreign  |      25.7            15.5
---------+--------------------------
Total    |  22.28125              15
---------+--------------------------
N        |        64   0          10
------------------------------------

[Hemanshu Kumar]

Thanks, Mark! No, I haven't solved these issues myself -- I'd just let this be.

On question #2 though, I knew already how to hide the title. What I wanted was to move it to the top-left corner box that's empty. Any thoughts on how to do that?

[Mark Horowitz]

Ah, got it. That's a good question about the upper left corner. I tried to relabel the corner cell_type, and it didn't work:

. collect label list cell_type, all

   Collection: Table
    Dimension: cell_type
        Label: Table cell type
 Level labels:
column-header  
       corner  
         item  
   row-header  

. collect label levels cell_type corner "Car origin"

. collect label list cell_type

  Collection: Table
   Dimension: cell_type
       Label: Table cell type
Level labels:
      corner  Car origin

. collect preview

--------------------------------
         |     Cheap   Expensive
---------+----------------------
Domestic |  20.72727      14.875
Foreign  |      25.7        15.5
---------+----------------------
Total    |  22.28125          15
---------+----------------------
N        |        64          10
--------------------------------

Stata's -tab- command does put the label of the rowvar in the corner, so maybe there's a way to have that in -table- also. Then again, Stata's tables.pdf (p.38) refers to the corner cell_type as the "no mans land", so maybe it's not designed to have any text (but you can format it using -collect style cell cell_type[corner]-).

Alternatively, maybe you can add the text using -putdocx table-. However, I haven't figured out a way to edit a memtable (a putdocx table stored in memory before being inserted into a document).