topic Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables in ODS and Base Reporting

Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

ruegsegs_us_ibm_com — Fri, 18 Oct 2019 02:00:36 GMT

I want to make hyperlinks in Excel -- so that the user would open the spreadsheet and see some cells with (familiar) blue, underlined text which are hyperlinks to a URL HREF (http://...). One click on the <text> to the link, and the web browser opens up. I created a column in a dataset where the char content is '=HYPERLINK("<link>","<text>")'. If Excel sees '=HYPERLINK(...)' then it should create the link, right?

I started with proc export dbms=xlsx, but when I opened the xlsx file in Excel app, it would show the "=HYPERLINK(...)" text and not the (blue) hyperlink <text> with the link "hidden". That is, the cell is not a hyperlink. However, if they "edit" that cell, change nothing, and resubmit the exact text, then Excel will "recognize" the =HYPERLINK() function and convert the cell to the (blue) hyperlink <text> (as desired) .

Then I read up on ods excel with the options(flow='tables'). So I tried that. Now it gets interesting. For "long" <links> (as in string length # chars), the Excel app shows the '=HYPERTEXT(...)' text, i.e. the cell is not a hyperlink. But if the links are all "short" (as in < 50 chars in length) then the cell IS a hyperlink and a nice short "click here" text shows up blue and clickable (what I want).

There seems to be going on with the string length of the <link> -- but options(flow='tables') didn't 100% solve it.

Example code attached.

Excel: Office 2019

SAS Current version: 9.04.01M6

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

ruegsegs_us_ibm_com — Fri, 18 Oct 2019 02:10:22 GMT

Is this my answer: link? Do I have to change my proc print to a proc report? (I'm not as proficient in proc report.)

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

Cynthia_sas — Fri, 18 Oct 2019 13:45:26 GMT

Hi:

Take a look at this previous posting with a PROC REPORT example:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Hyperlink-from-ODS-Excel-to-external/m-p/579186/highlight/true#M23126

I think that will get you started.

Cynthia

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

ruegsegs_us_ibm_com — Fri, 18 Oct 2019 20:19:17 GMT

Thanks Cynthia. Exactly. I thought I would get pointed to PROC REPORT.

So, to clarify... the problem I described cannot be solved with a proc print (in ods) -- correct?

Therefore, the "only" solution is to change over from a proc print to a proc report -- correct?

Those two statements are how I'm interpreting the solutions I'm seeing.

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

Cynthia_sas — Fri, 18 Oct 2019 21:22:51 GMT

Hi:

I would not expect the PROC EXPORT method to work at all.

I can envision PROC PRINT working, possibly. However, my "go-to" procedure of choice would be PROC REPORT because the URL capability was built into PROC REPORT's COMPUTE block. THIS Proc Print code works for me in both HTML and ODS Excel:


title; footnote;

proc format;
  value $nfmt 'Alfred'='https://www.google.com/search?&q=Alfred+Lord+Tennyson'
              'Alice'='https://www.google.com/search?&q=Alice+in+Wonderland'
              'Barbara'='https://www.google.com/search?&q=Barbara+Kingsolver';
run;

ods excel file='c:\temp\testurl.xlsx';

proc print data=sashelp.class(obs=3);
  var name / style(data)={url=$nfmt.};
  var age height weight;
run;

ods excel close;

In Excel, I have to float my mouse over the cell (which is not highlighted like a hyperlink), but then I see a message to click once to follow the link or hold and click to select the cell....but the link works. However, if I just change some more style attributes, then both PRINT and REPORT will make the NAME look like a Hyperlink:

** proc print;
title; footnote;

proc format;
  value $nfmt 'Alfred'='https://www.google.com/search?&q=Alfred+Lord+Tennyson'
              'Alice'='https://www.google.com/search?&q=Alice+in+Wonderland'
              'Barbara'='https://www.google.com/search?&q=Barbara+Kingsolver';
run;

ods excel file='c:\temp\testurl.xlsx';

proc print data=sashelp.class(obs=3);
  var name / style(data)={url=$nfmt. color=cx0000FF textdecoration=underline};
  var age height weight;
run;

ods excel close;

** proc report;
title; footnote;
         

ods excel file='c:\temp\testurl2.xlsx';

proc report data=sashelp.class(obs=3);
  column name age height weight;
  define name / style(column)={url=$nfmt. color=cx0000FF textdecoration=underline};
run;

ods excel close;

Hope this helps,

Cynthia

(last thought -- if your URLs are fixed, then I think you can make PROC PRINT work. If you need your URLs to be dynamically created, then you might have to use PROC REPORT and a COMPUTE block.)

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

ballardw — Fri, 18 Oct 2019 21:23:30 GMT

I think that Proc Print would require the Style element URL or possibly HREFTARGET to be set to appear the way you want.

To make the link change with the value of the variable you want to display I suspect you would need a format to associate the URL text with the value as is done to change colors for report highlighting. Something that would look like

Proc print data=foobar;

var url / style (data) = [url= formatforhref. ];

run;

If you have a data set with the values and the needed text it is pretty simple to make a format with the cntlin feature of proc format.

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

ruegsegs_us_ibm_com — Fri, 18 Oct 2019 23:23:11 GMT

Thanks. I'll dig into the proc print styles (that's new to me too)

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

Cynthia_sas — Sat, 19 Oct 2019 00:07:55 GMT

Hi:
Here's a paper that will get you started. It shows style overrides with PRINT, REPORT and TABULATE.
http://support.sas.com/resources/papers/proceedings13/366-2013.pdf

Cynthia

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

Smora0713 — Tue, 28 Apr 2020 14:31:09 GMT

Hi Cynthia_sas,

Thank you so much for this, it helps a lot. When I try your second approach I get the entire column showing up as underlined and blue. How can I write this so that only the codes with the hyperlink show up as blue and underlined?

Re: Hyperlinks via ODS are not hyperlinks in Excel if link text is too long, even with flow=tables

avellani — Sat, 08 May 2021 19:07:52 GMT

Cynthia, You Rock!