The SAS Output Delivery System and reporting techniques

Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

I have the following code for creating a spreadsheet with the ODS Excel Destination:

 

 

ods excel file="&SASRoot.\&DataRoot.\E-mail Tables.xlsx"
     options(embedded_titles="yes" contents="yes" embedded_footnotes="yes");
ods proclabel= "Table 1:  Review Status by Review Type FY 2017";
 
title "Table 1:  Review Status by Review Type FY 2017";
footnote link="#'The Table of Contents'!a1"  "Return to TOC"; run;
proc tabulate data=temp1(where=(fiscal_year=2017));
class TableReviewType stat;
table TableReviewType=' ',stat="Review Status"*(n*f=comma5. rowpctn*f=comma7.2)
       / box="Review Type";
keylabel N='#' RowPctN='%';
run;

 

 

 

This works beautifully except that the “Table of Contents” spreadsheet creates three rows (“nodes”) that link to the table’s spreadsheet so the TOC sheet  looks like this:

 

Table 1:  Review Status by Review Type FY 2017

Cross-tabular summary report

Table

 

When I click on each row, I do jump to the table, but I would like to have only the first Row (with the Table Name) to be on the TOC spreadsheet.  I know it is possible with ODS PDF (and perhaps some other ODS destinations) to restrict the TOC to one “node” for a table and I have tried to use code similar to the code that accomplishes this with other ODS destinations within my ODS Excel code, but have been unsuccessful. 

 

I have examined the ODS Excel documentation on the SAS® website (and available through the help menu within SAS®), but could not find instructions for anything beyond simply creating the TOC sheet.  I have also reviewed a few SGF papers, but still have not found a solution. 

 

Any help would be appreciated.


Accepted Solutions
Solution
‎08-03-2017 11:26 AM
SAS Super FREQ
Posts: 8,820

Re: Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

Hi:

  Try using CONTENTS=' ' on the PROC TABULATE statement and on the TABLE statement, like this:

ods excel file="c:\temp\Tables.xlsx"
     options(embedded_titles="yes" contents="yes" embedded_footnotes="yes");
 
title "Table 1:  Review Status by Review Type FY 2017";
footnote link="#'The Table of Contents'!a1"  "Return to TOC"; run;

ods proclabel= "Table 1:  Review Status by Review Type FY 2017";
proc tabulate data=sashelp.class contents=' ';
class age sex;
table sex=' ',age="Review Status"*(n*f=comma5. rowpctn*f=comma7.2)
       / box="Review Type" contents=' ';
keylabel N='#' RowPctN='%';
run;

ods proclabel= "Table 2:  Review Cholesterol Status";
proc tabulate data=sashelp.heart contents=' ';
class chol_status sex;
table sex=' ',chol_status="Review Status"*(n*f=comma5. rowpctn*f=comma7.2)
       / box="Review Type" contents=' ';
keylabel N='#' RowPctN='%';
run;

ods proclabel= "Table 3:  Review BP Status";
proc tabulate data=sashelp.heart contents=' ';
class bp_status sex;
table sex=' ',bp_status="Review Status"*(n*f=comma5. rowpctn*f=comma7.2)
       / box="Review Type" contents=' ';
keylabel N='#' RowPctN='%';
run;
ods excel close;

  I did not have your data, so used SASHELP.CLASS and SASHELP.HEART to make 3 tables.

 

cynthia

View solution in original post


All Replies
Solution
‎08-03-2017 11:26 AM
SAS Super FREQ
Posts: 8,820

Re: Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

Hi:

  Try using CONTENTS=' ' on the PROC TABULATE statement and on the TABLE statement, like this:

ods excel file="c:\temp\Tables.xlsx"
     options(embedded_titles="yes" contents="yes" embedded_footnotes="yes");
 
title "Table 1:  Review Status by Review Type FY 2017";
footnote link="#'The Table of Contents'!a1"  "Return to TOC"; run;

ods proclabel= "Table 1:  Review Status by Review Type FY 2017";
proc tabulate data=sashelp.class contents=' ';
class age sex;
table sex=' ',age="Review Status"*(n*f=comma5. rowpctn*f=comma7.2)
       / box="Review Type" contents=' ';
keylabel N='#' RowPctN='%';
run;

ods proclabel= "Table 2:  Review Cholesterol Status";
proc tabulate data=sashelp.heart contents=' ';
class chol_status sex;
table sex=' ',chol_status="Review Status"*(n*f=comma5. rowpctn*f=comma7.2)
       / box="Review Type" contents=' ';
keylabel N='#' RowPctN='%';
run;

ods proclabel= "Table 3:  Review BP Status";
proc tabulate data=sashelp.heart contents=' ';
class bp_status sex;
table sex=' ',bp_status="Review Status"*(n*f=comma5. rowpctn*f=comma7.2)
       / box="Review Type" contents=' ';
keylabel N='#' RowPctN='%';
run;
ods excel close;

  I did not have your data, so used SASHELP.CLASS and SASHELP.HEART to make 3 tables.

 

cynthia

New Contributor
Posts: 4

Re: Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

Thank you very much Cynthia.  Originally, I posted this question to SAS-L and Joe Matise responded and suggested that I post the question here to "see if Cynthia has a solution (on communities.sas.com) - I never fail to be surprised by the ways to work around this sort of thing..."

 

Your solution almost worked.  I had to place the "contents = ' '" option on both the PROC TABULATE statement and the TABLE statement to turn the 2nd and 3rd level TOC's invisible.  However, you will note that i said this made the lower TOC levels invisible.  Thia is because there sre still three rows of TOC generated for each table and if you click on the (now blank) 2nd or 3rd row, you still get taken to the table's location in the spreadsheet file. 

 

It would be nice if i could get rid of the rows altogether, but I will be satisfied with this soloution even if if that is not possible.

 

Thanks again.  I very much appreciate your help. 

Super User
Posts: 11,134

Re: Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

One option if the result does not really have to be Excel (my feeling is most reports should not be in Excel but that's me...) is to use RTF output. Word processors such as Word are pretty good about having tools to say only show one or two levels of the TOC and do not have invisible lines in the result.

SAS Super FREQ
Posts: 8,820

Re: Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

Hi:
  You will notice that I DO have the CONTENTS= option in my code on the TABULATE statement AND on the TABLE statement.

  Unfortunately, I find that contents= works differently for PDF than for ODS EXCEL. I will post a screen shot of what I mean.

  I have to use contents=' ' (quote-space-quote) with ODS Excel to just blank out the text for the link (and yes, that leaves a space); but in ODS PDF, I can just do contents=''(quote-quote - -NO space between the quotes) and the TOC looks lovely -- no extra space.

 

In the code on the left, for ODS EXCEL, I had to do quote-space-quote for contents=, meaning by space, 1 hit of the space bar. But for ODS PDF, i just put 2 quotes next to each other '' (quote-quote).
cynthia

control_contents.png

Valued Guide
Posts: 505

Re: Controlling Visible TOC Levels in Files Created using the ODS Excel Destination

Adding a single spaced table of contents to an existing workbook received from an another company

Even easier to add at creation time.

Issue
The TOC is single spaced but you need to hit enter once to activate each link. This only has to be done once.


for output workbook
https://www.dropbox.com/s/8ya5gvqxhn6nymr/tsttoc.xlsx?dl=0

This code allow you to add TOC after the workbook was created


HAVE (A toc dataset and excel workbook with two sheets from and external company without a table of contents)


Up to 40 obs from toc total obs=2

Obs                       TABLE_OF_CONTENTS

 1     =HYPERLINK("[d:\xls\tsttoc.xml]class!A1","Report Class")
 2     =HYPERLINK("[d:\xls\tsttoc.xml]cars!A1","Report Cars")



SHEET [SEX=Males] in workbook d:/xls/xlxp2sas.xml (NOTE XML extension)


  ----------------------------------------------------+
  |     A      |     B      |    C       |    D       |
  ----------------------------------------------------+
1 |NAME        |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+
2 | ALFRED     |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+
N | WILLIAM    |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+

[MALES]


SHEET [SEX=Females]

  ----------------------------------------------------+
  |     A      |     B      |    C       |    D       |
  ----------------------------------------------------+
1 | NAME       |    AGE     |  HEIGHT    |  WEIGHT    |
  +------------+------------+------------+------------+
2 | ALICE      |    14      |    69      |  112.5     |
  +------------+------------+------------+------------+
   ...
  +------------+------------+------------+------------+
N | BARBARA    |    15      |   66.5     |  112       |
  +------------+------------+------------+------------+

[FEMALES]


WANT ( to add a linked table of contents)
=========================================

TABLE_OF_CONTENTS
Report Females
Report Males

  +------------------------+
  |            A           |
  --------------------------
1 | TABLE_OF_CONTENTS      |
  +------------------------+
2 | Report Females         |
  +------------------------+
3 | Report Males           |
  +------------------------+


WORKING CODE - This is actually the full solution because the libname engine can add a sheet
There is an issue to activate the link you have to hit enter before using the link.
============================================================================================

libname tbl "d:/xls/tsttoc.xlsx";
data tbl.toc;
    TABLE_OF_CONTENTS  = '=HYPERLINK("[d:\xls\tsttoc.xlsx]Females!A1","Report Females")';
    output;
    TABLE_OF_CONTENTS  = '=HYPERLINK("[d:\xls\tsttoc.xlsx]cars!A1","Report Males")';
    output;
run;quit;
libname tbl clear;


* create an excel workbook with two sheets;
%utlfkil(d:/xls/tsttoc.xlsx);
ods excel file="d:/xls/tsttoc.xlsx" style=statistical
     options(embedded_titles="yes" embedded_footnotes="yes");
ods excel options(sheet_name="FEMALES" start_at="A1");
proc report data=sashelp.class(where=(sex="F"));
title "FEMALES";
run;quit;
ods excel options(sheet_name="MALES" start_at="A1");
proc report data=sashelp.class(where=(sex="M"));
title "MALES";
run;quit;
ods excel close;

* The solution.
libname tbl "d:/xls/tsttoc.xlsx";
data tbl.toc;

    TABLE_OF_CONTENTS  = '=HYPERLINK("[d:\xls\tsttoc.xlsx]Females!A1","Report Females")';
    output;
    TABLE_OF_CONTENTS  = '=HYPERLINK("[d:\xls\tsttoc.xlsx]Males!A1","Report Males")';
    output;
run;quit;
libname tbl clear;


Note you can get the sheet names using

libname xel "d:/xls/tsttoc.xlsx";
proc sql;
  create
     table dic as
  select
     memname
  from
     sashelp.vtable
  where
     libname='XEL'
;quit;
libname xel clear;


Obs    MEMNAME

 1     'The Table of Contents$'
 2     cars$
 3     clas$

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 161 views
  • 0 likes
  • 4 in conversation