The SAS Output Delivery System and reporting techniques

The hyperlink doesn't work when upgrade to ODS Excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

The hyperlink doesn't work when upgrade to ODS Excel

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.

 

 

 

 

 

 


Accepted Solutions
Solution
‎03-23-2018 04:29 PM
Frequent Contributor
Posts: 123

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

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 102

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

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;

Solution
‎03-23-2018 04:29 PM
Frequent Contributor
Posts: 123

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 211 views
  • 0 likes
  • 2 in conversation