How to generate a TOC-sheet when the rest of the sheets are generated automatically?

[Syntas_error]

I need to create a Table-of-contents-sheet with hyperlinks to the rest of my excel sheets. That part is doable I think, using this as a template:

https://www.lexjansen.com/sesug/2022/SESUG2022_Paper_232_Final_PDF.pdf  

I can also see how we might generate the urls and the names for the links dynamically (we just perform a query with PROC SQL and select the distinct sheet names for the data set we're working with).

What I can't wrap my head around is how I'm supposed to generate a TOC-sheet when the rest of my sheets are generated automatically (I'm talking about just the actual sheet now, not the text or the hyperlinks within it):

ods excel file="[path]");
PROC REPORT data=export;
by bladnamn;
column Redovisningsgrupp bladnamn variabel_cat_varde alder_grupp (kon,(andel CI_lower CI_upper lillan));
/* define variabelnamn / order order=internal;*/
define Redovisningsgrupp / group order=data noprint;
define order / order order=internal noprint;
define variabel / order order=internal noprint;
define bladnamn / group order=data noprint;
define variabel_cat_varde / order=data group;
define alder_grupp / order=data group;
define andel / mean;
define CI_lower / mean;
define CI_upper / mean;
define lillan / mean;
DEFINE kon / ACROSS ORDER=INTERNAL; 

compute before Redovisningsgrupp /style={just=left};;
line Redovisningsgrupp $char200.;
endcomp;

run;

ods excel close;

How do I do that?

I know I can set options(Contents="yes") in the ods statement, but that won't really let me customize the text and in addition, adds unappealing subheadings to each line in the table of contents. 

[Quentin]

I can't answer, but wanted to share that Bari Lawhorn, from SAS Tech Support, presented an EXCELLENT webinar for customizing ODS PDF TOC's for BASUG.  The recording is: https://www.basug.org/videos?wix-vod-video-id=a17c9abb623e4f64b2ef843b0589dad3&wix-vod-comp-id=comp-...  .  Slides https://www.basug.org/_files/ugd/6fce8c_aba2be0893314fa3bb3ca2c22fc1fc90.pdf

Paper: https://support.sas.com/resources/papers/proceedings11/252-2011.pdf .  

It's not explicitly about EXCEL, but I imagine many of the approaches she mentions would apply, and there are references at the end that mention other ODS destinations.

[Syntas_error · Accepted solution]

After a lengthy discussion with chatGPT, we arrived at the following solution which does seem to work:

ods excel file="[path]";

/* Create the Table of Contents (TOC) sheet using ODS EXCEL */
ods excel options(sheet_name="Table of Contents");
proc print data=toc noobs label;
   var tab_name tab_description;
run;

ods excel options(sheet_interval='bygroup' sheet_name="#byval(bladnamn)");


PROC REPORT data=export;
by bladnamn;
column Redovisningsgrupp bladnamn variabel_cat_varde alder_grupp (kon,(andel CI_lower CI_upper lillan));
/* define variabelnamn / order order=internal;*/
define Redovisningsgrupp / group order=data noprint;
define order / order order=internal noprint;
define variabel / order order=internal noprint;
define bladnamn / group order=data noprint;
define variabel_cat_varde / order=data group;
define alder_grupp / order=data group;
define andel / mean;
define CI_lower / mean;
define CI_upper / mean;
define lillan / mean;
DEFINE kon / ACROSS ORDER=INTERNAL; 

compute before Redovisningsgrupp /style={just=left};;
line Redovisningsgrupp $char200.;
endcomp;

run;