topic Re: The hyperlink doesn't work when upgrade to ODS Excel in ODS and Base Reporting

The hyperlink doesn't work when upgrade to ODS Excel

daisy6 — Tue, 13 Mar 2018 14:33:38 GMT

Hello,

My code works when I used SAS 9.3 but once upgraded to 9.4, it doesn't work anymore for the hyperlink.

Here is the code of SAS 9.3

ODS noresults;
ODS listing close; /*Turn off the standard line printer destination*/
ods tagsets.ExcelXP path="&dir."

file="Report_&mon..xml"

proc report data=test;

.......

compute urlstring/character length=200;
urlstring=catt("&dir.\Report_&mon..xml#Items!A",cumsum);
endcomp;

Once I use

ODS noresults;
ODS listing close; /*Turn off the standard line printer destination*/
ods Excel file="&dir.\Report_&mon..xlsx";

proc report data=test;

......

compute urlstring/character length=200;
urlstring=catt("&dir.\Report_&mon..xlsx#Items!A",cumsum);
endcomp;

It still create the report excel file but the link cann't open the Items sheet/tab in Report excel file.

Even I change the code as :urlstring=catt("&dir.\Report_&mon..xlsx - Items!A",cumsum);

Can anyone help me? Thanks.

Re: The hyperlink doesn't work when upgrade to ODS Excel

daisy6 — Tue, 13 Mar 2018 15:24:16 GMT

I create some testing code here.

data class;
set sashelp.class;
cumsum=age-10;
urlstring=catt("C:\temp\exam_report.xlsx - Items!A",cumsum);
run;
data classfit;
set sashelp.classfit;
run;

ODS noresults;
ODS listing close; /*Turn off the standard line printer destination*/
ods Excel file="C:\temp\exam_report.xlsx"
style=minimal /*Styles to control appearance of output*/;
ods Excel options (Embedded_titles = 'yes' sheet_name= "Summary" frozen_rowheaders="8" frozen_headers= '2' absolute_column_width= '35,10,6,6,8,8,8,8,10,10,10,10,10,10,10,10,10,10,10,10');
options missing=" ";
title j=l "Premilary Item Analysis -&mon. ";
proc report data=class NOWD
style(header)=[foreground=white background=teal];
Column urlstring name sex age height weight ;
define urlstring/ noprint ;
define Name/left display;
define sex/left display ;
define age/left display ;
define height/left display ;
define weight/left display ;
compute name;
call define("name",'URL',urlstring);
call define("name",'style','style={textdecoration=underline color=blue}');
endcomp;
run;

ods Excel options ( Embedded_titles = 'yes' sheet_name= "Items" frozen_headers= '2' frozen_rowheaders="8" absolute_column_width= '25,10,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8');
title j=l color=blue height=10pt link="#'Summary'!A1" 'Click to return to Summary';
options missing=" ";
proc Report data=classfit NOWD
style(header)=[foreground=white background=teal];
Column name sex age weight height;
define Name/left display ;
define sex/left display ;
define age /left display ;
define height/left display ;
define weight/left display ;
run;

ods _all_ close; /* Close and release the xml file so it can be opened with Excel*/
ODS listing;

Re: The hyperlink doesn't work when upgrade to ODS Excel

SuzanneDorinski — Mon, 19 Mar 2018 17:45:35 GMT

Try changing the urlstring line in the class data step.

If I change that line to

	urlstring=catt("#'Items'!A", cumsum);

the hyperlinks on the Summary sheet work.