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
... View more