topic Re: Bookmarks for Output Objects in ODS Excel in SAS Programming

Bookmarks for Output Objects in ODS Excel

Palmer_D — Thu, 27 Aug 2020 18:43:21 GMT

Hi everyone,

I'm trying to figure out how to include Excel bookmarks in my output file using ODS Excel. Here is some example code:

ods excel file = 'C:\Temp\Buy.xlsx';
	data _null_;
		set Sashelp.Buy end = done;
		if _n_ = 1 then
			do;
				array A[*]
						_all_
						_temporary_;
				declare odsout B();
				B.table_start();
					B.head_start();
						B.row_start();
							B.format_cell(text: 'Date');
							B.format_cell(text: 'Amount');
						B.row_end();
					B.head_end();
					B.body_start();
			end;
						B.row_start();
						do i = 1 to 2;
							B.format_cell(data: A[i]);
						end;
						B.row_end();
		if done then
			do;
					B.body_end();
				B.table_end();
			end;
	run;
	proc odstext;
		p 'Another output object';
	run;
ods excel close;

If you go to Home > Find & Select > Go To, you'll see the following:

I need each output object to be listed in this window. The names aren't super important, although if there is a way to name them manually that would be nice. It would also be nice to be able to bookmark the header and body of the Report Writing Interface separately, but that's also less important.

I'm using SAS 9.4M5 on Windows 10.

Thanks!

Re: Bookmarks for Output Objects in ODS Excel

ChrisNZ — Fri, 28 Aug 2020 04:30:05 GMT

Have you tried

ods excel anchor='TABLE1';

Re: Bookmarks for Output Objects in ODS Excel

Palmer_D — Fri, 28 Aug 2020 14:29:02 GMT

Hi Chris,

Thank you for your reply!

Yes, I've tried the anchor option. I forgot to mention in my original post, but the two closest things I've tried are:

1) Specifying the option anchor = 'Table1' (along with the dom option to display the html structure in the log). This shows me how Table1 replaces IDX, the default value of ID= in the section tag. The "Go To" window, however, is unchanged.

2) Specifying the suboption (contents = 'on'). This creates another sheet with links to the output objects on the main sheet, but "Go To" remains empty for both sheets. The fact that the Table of Contents sheet correctly identifies the cells in which I'm interested indicates to me that SAS knows how to communicate this information to Excel - I'm just not sure how to get it into the "Go To" window specifically.

Thanks again!

Re: Bookmarks for Output Objects in ODS Excel

ChrisNZ — Mon, 31 Aug 2020 02:36:40 GMT

My version is so old that the anchor syntax is unsupported, so I can't help much more. Maybe @Cynthia_sas knows?

Re: Bookmarks for Output Objects in ODS Excel

Cynthia_sas — Mon, 31 Aug 2020 15:43:12 GMT

Hi:
I think the closest you can get is the Table of Contents that ODS EXCEL generates. I am not sure that you have the ability to populate the "Go to" window in Excel. However, this would be a question for Tech Support. I believe that the ANCHOR= option really was only designed to work with the original ODS PDF and ODS HTML -- that feature was part of ODS for those 2 destinations and I do not believe it was retrofitted or revisited when ODS EXCEL came on the scene. Again, another question for Tech Support.
Cynthia