Hyperlinks within text string - Export to Excel

[jlwatts]

I have created a macro variable to hold a hyperlink:

%let CT_link = https://controller.vcu.edu/grants/;

I am using the PROC REPORT to generate the report.  I try to embed that hyperlink into a block of text using the Line Statement:

line @1 'deadline ^{style [url="&ct_link" linkcolor=white]via the ^{style [color=blue textdecoration=underline]cost transfer form} available on the G&C webpage.} ';

We then have a parameter to specify if the report should be output as either PDF or EXCEL.  When the report is written as PDF, the hyperlink works fine:

  But when I export the report to EXCEL, the hyperlink does not work:

Is there a way to embed the hyperlink in a line of text to have it work both in PDF and EXCEL format?

This is the code that we include to allow selection between PDF, EXCEL or HTML:

data _null_;
if upcase("&outtype") = "HTML" then call symput("odsdest","HTML");
if upcase("&outtype") = "PDF" then do;
rc = stpsrv_header('Content-type','application/pdf');
rc = stpsrv_header('Content-disposition','attachment; filename=temp.pdf');
call symput("odsdest","PDF");
end;
if upcase("&outtype") = "EXCEL" then do;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header('Content-disposition','attachment; filename=temp.xls');
call symput("odsdest","tagsets.excelxp");
end;
run;

[Cynthia_sas]

Hi: It looks like you're running a stored process and using the older TAGSETS.EXCELXP. You could start to work with Tech Support to figure out why your link does not work or you could consider switching to the newer Open XML Excel format, an XLSX file, which is created with ODS EXCEL (as an alternative to TAGSETS.EXCELXP). This would also mean changing your STPSRV_HEADER statements for content-type and content-disposition -- this previous Forum posting shows the content-disposition that should be used for an XLSX file: https://communities.sas.com/t5/SAS-Programming/Proc-HTTP-Content-Type-for-XLS-and-XLSX/td-p/552737
I don't have access to a configuration to test any stored process code however, if you are still stuck with the problem after trying an XLSX file, then working with Tech Support is your best resource.
Cynthia

[Tom]

Are you sure that is the code you ran?

line @1 'deadline ^{style [url="&ct_link" linkcolor=white]via
 the ^{style [color=blue textdecoration=underline]cost transfer form}
 available on the G&C webpage.} ';

Because the use of single quotes around the string will prevent the macro processor from replacing the reference to the CT_LINK macro variable with its value. 

Try using double quotes instead.

line @1 "deadline ^{style [url='&ct_link' linkcolor=white]via
 the ^{style [color=blue textdecoration=underline]cost transfer form}
 available on the G&C webpage.} ";

It will also prevent the macro processor from replacing the reference to the C macro variable with its value.  But you did not provide a value for that one.

[Ksharp]

In Excel file, the URL only can apply to a whole cell, not just a part of line .

Interesting thing is the URL seems to be unable to apply to LINE statement, I don't know why .

But FOOTNOTE could make a workaround way.

%let CT_link = https://controller.vcu.edu/grants/;
ods escapechar='^';
ods excel file='c:\temp\temp.xlsx' options(Sheet_Name='xx' Embedded_footnotes='yes');
footnote  j=l link="&ct_link" 'deadline ^{style [textdecoration=underline color=blue]cost transfer form} available on the G&C webpage.' ;

proc report data=sashelp.class nowd;
run;
ods excel close;