Hyperlink from ODS Excel to external

[AndrewZ]

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

[Cynthia_sas · Accepted solution]

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

[AndrewZ]

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.