I wanted to add that I updated SAS to 9.4 TS1M3 and my code works as expected. Turns out that is all I needed.   Thank you for the help! ... View more

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$ ... View more