Column labels in -collect-

[Geoff Dougherty]

Hi, everyone.

I am trying to make some small changes to a table that relies on -collect- syntax and having some trouble coming up with a workable approach after reading the manual.

Specifically, I want to remove some of the level labels, and add subtitles to columns reflecting that they refer to N and percentages.

Here is the code I'm using to generate the table.

sysuse cancer.dta

gen ageGroup=age>59

label define ageGroup 0 "Under 60" 1 "60 and older" 

label values ageGroup ageGroup 

table (var) (died), /// 
statistic(fvfrequency drug) statistic(fvpercent drug) ///
statistic(fvfrequency ageGroup) statistic(fvpercent ageGroup) 

collect style header result, level(hide)
  
collect style row stack, nobinder spacer
  
collect layout (var) (died[0 1 total]#result) 

collect preview

And here is a redlined version of what I would like the table to look like.

Thanks for any suggestions!

[Tim Plante]

Hi Geoff,

This doesn't at all answer your question, but I find using frames to build a table a bit more customizable. Here's some alternative code. It makes a frame called "mytable" that has the below contents, all as strings. After you make it, you just need to export it as a CSV file, spreadsheet, or word processor document. Finally, just merge the cells for "No" and "Death" and then also merge "Yes" and "Death".

Thanks,
Tim

frame reset
sysuse cancer.dta, clear

// make the age group
gen ageGroup=(age>59) if !missing(age)
label define ageGroup 0 "Under 60" 1 "60 and older"
label values ageGroup ageGroup

// make frame
frame create mytable /// frame's name
strL group /// 1st column
strL n1 /// 2nd column
strL perc1 /// 3rdcolumn
strL n2 /// 4th column
strL perc2 // 5th column

// now post first row values
frame post mytable ("") ("No") ("Death") ("Yes")  ("Death")
frame post mytable ("") ("N") ("%") ("N") ("%")

// let's look at the table so far
cwf mytable
bro

// back to our main frame
cwf default



foreach y of varlist drug ageGroup {
frame post mytable (" ") (" ") (" ") (" ") (" ") // blank spacer row

levelsof `y', local(variableops)

foreach x of numlist `variableops' {
count if died==0
local leftcoln = r(N)
count if died==0 & `y'==`x'
local leftcoln`x'= r(N)

count if died==1
local rightcoln = r(N)
count if died==1 & `y'==`x'
local rightcoln`x'= r(N)
local rowlab`x': label (`y') `x'
}

foreach x of numlist  `variableops' {
local n1out = trim("`: display %10.0fc `leftcoln`x'''")
local perc1out = trim("`: display %10.1fc 100*(`leftcoln`x''/`leftcoln')'")
local n2out = trim("`: display %10.0fc `rightcoln`x'''")
local perc2out = trim("`: display %10.1fc 100*(`rightcoln`x''/`rightcoln')'")
frame post mytable ("`rowlab`x''") ("`n1out'") ("`perc1out'%") ("`n2out'") ("`perc2out'%")
}
}

cwf mytable // now export the table as desired
export excel using "mytable.xlsx", replace
pwd // this is the folder where the above file just saved

[William Lisowski]

I've been able to improve (but not completely) the code in post #1. I got the column headings desired, and I now show row totals. Changes highlighted in red in the code below.

sysuse cancer.dta

gen ageGroup=age>59

label define ageGroup 0 "Under 60" 1 "60 and older" 

label values ageGroup ageGroup 

table (var) (died), /// 
statistic(fvfrequency drug) statistic(fvpercent drug) ///
statistic(fvfrequency ageGroup) statistic(fvpercent ageGroup) 

collect label levels result fvfrequency "N" fvpercent "%", replace
  
collect style row stack, nobinder spacer

collect layout (var) (died#result) 

collect preview

. collect preview

------------------------------------------------------
               |              Patient died            
               |      No           Yes         Total  
               |   N       %    N       %    N       %
---------------+--------------------------------------
Drug type      |                                      
  Placebo      |   1    5.88   19   61.29   20   41.67
  Other        |   8   47.06    6   19.35   14   29.17
  NA           |   8   47.06    6   19.35   14   29.17
ageGroup       |                                      
  Under 60     |  13   76.47   22   70.97   35   72.92
  60 and older |   4   23.53    9   29.03   13   27.08
------------------------------------------------------

What I can't figure out is how to suppress the variable names in the left column. Adding (before the table command)

label variable ageGroup "-"
label variable drug "-"

will change them to "-" for example, but I cannot find a collect command to suppress them entirely.