topic ODS Excel options(contents='ON') - but no table of contents in ODS and Base Reporting

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

vanja — Thu, 07 Mar 2019 13:32:44 GMT

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;

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

RW9 — Thu, 07 Mar 2019 14:11:41 GMT

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-Created-using-the-ODS/td-p/341265

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

vanja — Thu, 07 Mar 2019 15:31:15 GMT

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');

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

RW9 — Fri, 08 Mar 2019 08:02:59 GMT

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