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

[RobW]

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 😜

[Cynthia_sas]

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]