topic Re: ODS Tagsets.ExcelXP and placing hypelinks to other tabs within same file in ODS and Base Reporting

ODS Tagsets.ExcelXP and placing hypelinks to other tabs within same file

RobW — Tue, 03 Feb 2009 21:28:58 GMT

I've thoroughly confused myself, and I am sure there will be a simple answer. I've created a summary report where I want to have 'hyperlinks' for certain cell counts to drilldown sheets within the same file. So, if this was a simple 1x1 table with a single count reported, there would be two sheets - one for the summary 1x1 tables and another for the drilldown of corresponding data contributing to that count.

I'm having trouble creating the link using the ExcelXP tagset. should I be using HREF? is there a tagattr for internal file destinations?

I foresee a palm to the forehead gesture when I get the answer 😜

Re: ODS Tagsets.ExcelXP and placing hypelinks to other tabs within same file

Cynthia_sas — Tue, 03 Feb 2009 23:46:16 GMT

Hi:
In general, the way to set hyperlinks with ODS is through the use of:
1) URL= style attribute
2) with PROC REPORT, using the CALL DEFINE statement with the URL option
3) for ODS HTML, only, coding the proper <A> tag

TAGATTR is limited to setting formats, formulas and types, when you use the ExcelXP tagset. I have used the URL= style attribute with TAGSETS.EXCELXP without any issues. You do need to know what your sheets will be named in order to be able to build the format that you need.

For example, I have my first sheet called, Main; a second sheet called Asia and a third sheet called Canada. I set those using the sheet_name option in TAGSETS.EXCELXP suboption list (as shown below).

Now that I know the names of my sheets, I can make a user defined format that will give Excel the proper "link" syntax it wants. Excel doesn't really want a fully qualified hyperlink, this seems to be the form of link that it uses:
[pre]
#SheetName!A1
[/pre]

Next, I built a user-defined format like this:
[pre]
proc format ;
value $reglnk 'Asia'= '#Asia!A1'
'Canada' = '#Canada!A1';
run;
[/pre]

Finally, I used the user-defined format here:
[pre]
define region / group
style(column)={url=$reglnk.};
[/pre]

I show a PROC REPORT example, but you could use similar syntax with PROC TABULATE or PROC PRINT or even with a custom Table Template. The full program is below.

cynthia

[pre]
proc format ;
value $reglnk 'Asia'= '#Asia!A1'
'Canada' = '#Canada!A1';
run;

ods tagsets.excelxp file='c:\temp\try_hyper.xml' style=sasweb
options(sheet_name='Main');

proc report data=sashelp.shoes nowd ;
column region product sales;
where region in ('Asia', 'Canada');
define region / group
style(column)={url=$reglnk.};
define product / group;
define sales / sum;
rbreak after / summarize;
run;

ods tagsets.excelxp options(sheet_name='Asia') ;

proc report data=sashelp.shoes nowd ;
column region product sales;
where region = 'Asia';
define region / display;
define product / display;
define sales / sum;
rbreak after / summarize;
run;

ods tagsets.excelxp options(sheet_name='Canada');

proc report data=sashelp.shoes nowd ;
column region product sales;
where region = 'Canada';
define region / display;
define product / display;
define sales / sum;
rbreak after / summarize;
run;

ods tagsets.excelxp close;
[/pre]

Re: ODS Tagsets.ExcelXP and placing hypelinks to other tabs within same file

anandbillava — Wed, 02 Jul 2025 11:09:09 GMT

I have used this option in the past and it was working for me. Recently I am using a new instance of SAS in a new company and its not working. Is there anything to do with SAS options.? I am using SAS 9.4 M7 PC SAS.

Re: ODS Tagsets.ExcelXP and placing hypelinks to other tabs within same file

Tom — Wed, 02 Jul 2025 11:59:36 GMT

@anandbillava wrote:

I have used this option in the past and it was working for me. Recently I am using a new instance of SAS in a new company and its not working. Is there anything to do with SAS options.? I am using SAS 9.4 M7 PC SAS.

Provide a minimal reproducible example program that others can run.

That is provide a data step that creates enough data and the code to create an XML file where the hyperlinks do not work for you.

Re: ODS Tagsets.ExcelXP and placing hypelinks to other tabs within same file

Chevell_sas — Wed, 02 Jul 2025 13:21:35 GMT

Is the issue that the hyperlinks are not working or is the file not opening in Excel?