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.
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.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.