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:
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.
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
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
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.
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.
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
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$
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.