topic Re: Hyperlink from ODS Excel to external in ODS and Base Reporting

Hyperlink from ODS Excel to external

AndrewZ — Mon, 05 Aug 2019 17:03:13 GMT

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

Re: Hyperlink from ODS Excel to external

Cynthia_sas — Tue, 06 Aug 2019 16:36:23 GMT

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

Re: Hyperlink from ODS Excel to external

AndrewZ — Wed, 07 Aug 2019 03:21:08 GMT

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.