I would like to make pretty hyperlinks using ODS Excel.It almost works.
My code
data baseball;
set sashelp.baseball(keep=name obs=10);
url = cats('https://www.google.com/search?q=', urlencode(strip(name)));
hyperlink=cats('=hyperlink("',url,'", "',name,'")');
run;
ods _all_ close;
ods excel file="c:\temp\hyperlink.xlsx"
style=statistical
options (wraptext='no' flow='tables')
;
proc print data=baseball noobs;
quit;
ods _all_ close;
The SAS log has warnings that don't make sense. How could there be a circular reference without any cell references! 🙂
WARNING: Ignoring formula =hyperlink("https://www.google.com/search?q=Ashby,%20Alan",, which otherwise might cause a circular reference in Excel. WARNING: Ignoring formula =hyperlink("https://www.google.com/search?q=Davis,%20Alan",, which otherwise might cause a circular reference in Excel. WARNING: Ignoring formula =hyperlink("https://www.google.com/search?q=Griffin,%20Alfredo",, which otherwise might cause a circular reference in Excel. WARNING: Ignoring formula =hyperlink("https://www.google.com/search?q=Newman,%20Al",, which otherwise might cause a circular reference in Excel.
When it opens in Excel, the hyperlink looks almost perfect, but it isn't a formula. If in Excel I edit the formula and close it without any changes, then Excel turns it into a valid formula.
I use SAS 9.4 (TS1M6) on W32_10PRO
Hi:
Without using your =hyperlink technique, there are 2 methods in PROC REPORT that generate working links for me.
Here's the code:
data baseball;
set sashelp.baseball(keep=name team league division obs=10);
name2=name;
url = cats('https://www.google.com/search?q=', urlencode(strip(name)));
run;
ods _all_ close;
ods excel file="c:\temp\hyperlink.xlsx"
style=statistical
options (wraptext='no' flow='tables') ;
proc report data=baseball spanrows split='/';
column division league team url name name2;
define division / order;
define league / order;
define team / order;
define url / display /* noprint */;
define name/display 'Click to Google Search on Name/(using STYLE as 2nd argument)';
define name2 / display 'Click to Google Search/(using URL as 2nd argument)';
compute name;
** using 'STYLE' as 2nd argument and precalc URL with STYLE= as 3rd argument;
length svar $120;
svar = catt('style={url="',url,'"}');
call define(_col_,'style',svar);
endcomp;
compute name2;
** using "URL" as 2nd argument and pre-calculated URL as 3 argument;
call define(_col_,'url',url);
endcomp;
quit;
ods excel close;
And when I run this code, both the NAME and NAME2 columns have valid hyperlinks. When I float my mouse over a cell, I see the Google Search string that I am about to execute when I click.
The URL I'm using is the URL that you calculated in your DATA step, but I am not using the =HYPERLINK syntax. ODS has 2 ways to send a hyperlink in PROC REPORT. Using a COMPUTE block with CALL DEFINE, I can send a HYPERLINK in a CALL DEFINE with 'STYLE' as the second argument or with 'URL' as the second argument. With STYLE as the 2nd argument, I need to have a correct STYLE= override as the 3rd argument. With URL as the 2nd argument, I need to have just a valid URL as the 3rd argument.
When I open the hyperlink.xlsx file with Excel, the links in the NAME column work for me and the links in the NAME2 column work for me. I do not get any Excel warnings when I open the file. Note that I left URL as a visible column because it's needed for the CALL DEFINE statements, and so the URL is visible during testing. However, NOPRINT can be used to hide it on the report after testing.
Cynthia
Hi:
Without using your =hyperlink technique, there are 2 methods in PROC REPORT that generate working links for me.
Here's the code:
data baseball;
set sashelp.baseball(keep=name team league division obs=10);
name2=name;
url = cats('https://www.google.com/search?q=', urlencode(strip(name)));
run;
ods _all_ close;
ods excel file="c:\temp\hyperlink.xlsx"
style=statistical
options (wraptext='no' flow='tables') ;
proc report data=baseball spanrows split='/';
column division league team url name name2;
define division / order;
define league / order;
define team / order;
define url / display /* noprint */;
define name/display 'Click to Google Search on Name/(using STYLE as 2nd argument)';
define name2 / display 'Click to Google Search/(using URL as 2nd argument)';
compute name;
** using 'STYLE' as 2nd argument and precalc URL with STYLE= as 3rd argument;
length svar $120;
svar = catt('style={url="',url,'"}');
call define(_col_,'style',svar);
endcomp;
compute name2;
** using "URL" as 2nd argument and pre-calculated URL as 3 argument;
call define(_col_,'url',url);
endcomp;
quit;
ods excel close;
And when I run this code, both the NAME and NAME2 columns have valid hyperlinks. When I float my mouse over a cell, I see the Google Search string that I am about to execute when I click.
The URL I'm using is the URL that you calculated in your DATA step, but I am not using the =HYPERLINK syntax. ODS has 2 ways to send a hyperlink in PROC REPORT. Using a COMPUTE block with CALL DEFINE, I can send a HYPERLINK in a CALL DEFINE with 'STYLE' as the second argument or with 'URL' as the second argument. With STYLE as the 2nd argument, I need to have a correct STYLE= override as the 3rd argument. With URL as the 2nd argument, I need to have just a valid URL as the 3rd argument.
When I open the hyperlink.xlsx file with Excel, the links in the NAME column work for me and the links in the NAME2 column work for me. I do not get any Excel warnings when I open the file. Note that I left URL as a visible column because it's needed for the CALL DEFINE statements, and so the URL is visible during testing. However, NOPRINT can be used to hide it on the report after testing.
Cynthia
Cynthia,
I appreciate the quick reply. Yes, that works nicely here! (I'll research how to make the links blue.)
Too bad PROC PRINT doesn't work because that would be more concise than PROC REPORT. Still, it works, and I like aesthetically how it groups rows by division.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.