ods tagsets.excelxp INDEX option

Yaya1024 — Mon, 09 Mar 2015 19:31:46 GMT

Hi Gurus,

I have generated an excel report which contains several tabs with SAS 9.4. I want to produce a table of contents as the first sheet which contains all the sheet names with links that could jump to corresponding tabs.

I used the INDEX='yes' option. But the links in the result TOC is based on the full path of the excel document. If I move the excel or send it buy email, the links will become invalid. Is there any way to fix this issue?

My code is as below:

ods escapechar="^";

ods tagsets.excelxp options(sheet_name="&xlsheetname" index='yes' frozen_headers='yes' frozen_RowHeaders='2' Zoom='80' embedded_titles='yes') ;

title justify=left link="#'Contents'!B1" color=blue underlin=1 '^S={font_style=italic font_size=11pt} TAKE ME TO THE CONTENTS TABLE';

proc report data=&printdata1 nowd split='*' spacing=1 headline missing;

..........; run;

ods tagsets.excelxp options(sheet_name="&xlsheetname" index='yes' frozen_headers='yes' frozen_RowHeaders='2' Zoom='80' embedded_titles='yes') ;

title justify=left link="#'Contents'!B1" color=blue underlin=1 '^S={font_style=italic font_size=11pt} TAKE ME TO THE CONTENTS TABLE';

proc report data=&printdata2 nowd split='*' spacing=1 headline missing;

..........; run;

ods tagsets.ExcelXP close;

Thanks a lot!

Re: ods tagsets.excelxp INDEX option

Chevell_sas — Tue, 22 Mar 2016 14:13:38 GMT

See the SAS Note below which will allow you to create relative links in the index sheet which will allow them to function correctly.

http://support.sas.com/kb/45107

topic Re: ods tagsets.excelxp INDEX option in ODS and Base Reporting

ods tagsets.excelxp INDEX option

Re: ods tagsets.excelxp INDEX option