ODS Excel options(contents='ON') - but no table of contents

[vanja]

Hi!

I am trying to generate a sheet with a table of contents with my ods excel statement. I am not quite sure of how this works but I'm hoping that it will take the titles and title2:s and display them on a separate sheet. However, no table of contents appears in my Excel file.

I have several proc reports in my program. Is there anyone out there who knows what I am doing wrong or if there is something in my SAS installation that is missing?

I use SAS Enterprise Guide version 7.15.

ods excel file="&ROOTDIR.\&FILENAME...xlsx";
ods excel options(embedded_titles='YES' sheet_interval='proc' autofilter='all' contents='yes' );

ods excel options(sheet_name='TABLE1');
title "...";
title2 "...";

proc report;
etc etc...
run;

ods excel close;

[RW9]

Going to guess that your missing ods proclabel per this from a quick use of the search bar!

https://communities.sas.com/t5/ODS-and-Base-Reporting/Controlling-Visible-TOC-Levels-in-Files-Create...

[vanja · Accepted solution]

Thank you for trying to help me! ODS PROCLABEL alone didn't do the trick. By examining the code to the case you referred to I realized that instead of dividing the "ods excel file" and "ods excel options" into two lines of code and put it on the same line I actually got a Table of Contents. It is not very pretty and I get some lines in my table of contents that I would rather not have;

"Report"

"Table 1"

But still, good progress!

ods excel file="&ROOTDIR.&FILENAME..xlsx"
          options(embedded_titles='YES' contents='yes' autofilter='on' sheet_interval='proc');

[RW9]

Great, you can mark your own response as the correct answer.