Table Percent Command and Exporting to Excel with a Title

[Elena Draghici]

Hello Statalist,

I am running a large number of tables in Stata 17 and am writing them to different sheets in an Excel workbook. I have two questions that I am hoping someone could assist me with. I cannot share the details because it is on my work computer (confidentiality), but I created a comparable example on my personal laptop.

First, I want to create a table that displays counts and percentages by province and year of men and women. In my real example, I do not use sex, but I do use a binary variable which I think works fine for this example. The code my work computer is forcing me to run is:

table ( year prov ) ( sex ) (), statistic(frequency) statistic(percent, across(prov sex)) nformat(%9.0f  percent) sformat(`"%s%%"'  percent)

In the code above, I get a table that displays the percentage of each province across total. However, I want only the percentage of men and women and NOT the province. So the code that I want to use is:

table ( year prov ) ( sex ) (), statistic(frequency) statistic(percent, across( sex)) nformat(%9.0f  percent) sformat(`"%s%%"'  percent)

This works on my home computer (SE edition), but not my work computer (MP edition). The error I get on my work computer is "r(3204) and indicates that _collect_style_header():3204 matrix found where scalar required." Any ideas as to why this is happening and what I could do about it?

Second, I want to include titles in my Excel Sheets (as the header of each table). So far the code is:

table ( year prov ) ( sex ) (), statistic(frequency) statistic(percent, across( sex)) nformat(%9.0f  percent) sformat(`"%s%%"'  percent)
collect export "file_path_sheet_name", as(xlsx) sheet(poverty) cell(A1)

So far, I have tried:

collect export "file_path_sheet_name", as(xlsx) title(this is the title) sheet(poverty) cell(A1)

I get the error code: "option title() not allowed"

I also tried:

table ( year prov ) ( sex ) (), statistic(frequency) statistic(percent, across( sex)) nformat(%9.0f  percent) sformat(`"%s%%"'  percent)
collect title :This is my title:
collect export "file_path_sheet_name", as(xlsx) sheet(poverty) cell(A1)

I get the error code "collect title not recognized"

Any help or suggestions will be greatly appreciated!

Thank you in advance.

[William Lisowski]

The collect title command was added to Stata 17 in the 17nov2021 update. I suspect your copy of Stata has not been updated for more than a year.

help update

sysuse auto, clear
table (rep78) (foreign)
collect title :This is my title:
collect preview

. collect preview

:This is my title:
------------------------------------------------
                   |          Car origin        
                   |  Domestic   Foreign   Total
-------------------+----------------------------
Repair record 1978 |                            
  1                |         2                 2
  2                |         8                 8
  3                |        27         3      30
  4                |         9         9      18
  5                |         2         9      11
  Total            |        48        21      69
------------------------------------------------

[Elena Draghici]

Thank you so much, I suspect you are right. I will follow-up.