topic Re: URL in spanned headers of proc report with ods excel in SAS Enterprise Guide

URL in spanned headers of proc report with ods excel

sasuser_8 — Thu, 19 Jan 2023 15:44:29 GMT

Hello,

I would like to know how to add a hyperlink on a spanned header in a proc report with ods excel. I tried the 2nd solution offered by this answer but it didn't work in my case.

Re: URL in spanned headers of proc report - SAS Support Communities

Here is a sample code. I would like, by clicking on the 'EMPLACEMENT' header in the excel file, to open the TEST.xlsx file.

%LET FICHIER = file:///\\repertoire\information\TEST.XLSX;

DATA SHOES;
SET SASHELP.SHOES;
RUN;

ODS EXCEL FILE="\\repertoire\information\Rapport.xlsx";

PROC REPORT DATA= SHOES;
COLUMN ('EMPLACEMENT' Region Product)
('AUTRES' Subsidiary Stores Sales Inventory Returns);
RUN;

ODS EXCEL CLOSE;

Re: URL in spanned headers of proc report with ods excel

Ksharp — Fri, 20 Jan 2023 08:02:02 GMT

Then you could try first one .or @Cynthia_sas know the solution?

DATA SHOES;
SET SASHELP.SHOES;
RUN;

proc format;
value $ fmt
'EMPLACEMENT'='C:\TEMP\TEST.XLSX'
;
value $ color
'EMPLACEMENT'='blue'
;
value $ style
'EMPLACEMENT'='underline'
;
run;

ODS EXCEL FILE="c:\temp\Rapport.xlsx";

PROC REPORT DATA= SHOES nowd
style(header)={url=$fmt. color=$color. textdecoration=$style. };
COLUMN ("EMPLACEMENT" Region Product)
('AUTRES' Subsidiary Stores Sales Inventory Returns);
RUN;

ODS EXCEL CLOSE;

Re: URL in spanned headers of proc report with ods excel

sasuser_8 — Fri, 20 Jan 2023 13:19:15 GMT

I would like to link to a different file for EMPLACEMENT and AUTRES, that's why I did not choose the 1st solution...

Re: URL in spanned headers of proc report with ods excel

Cynthia_sas — Fri, 20 Jan 2023 20:42:08 GMT

Hi:

I found that only a slight modification of the posted program was needed in order to provide a different file for the AUTRES header, as shown below:

And, when I open the Rapport.xlsx file, you can see that I can examine the hyperlink for the AUTRES header and the file that opens when I click will be the c:\temp\autres.xlsx file:

So using 9.4M7, the format example worked for me for ODS Excel to specify 2 different files as the URL= value.

Cynthia

Re: URL in spanned headers of proc report with ods excel

sasuser_8 — Fri, 20 Jan 2023 21:33:47 GMT

It works perfectly!

Thank you 🙂

Re: URL in spanned headers of proc report with ods excel

sasuser_8 — Mon, 23 Jan 2023 20:36:01 GMT

In your example, if I replace the file autres.xlsx by autres.pdf, can I specify the page number ? I already tried autres.pdf#page=3. I saw that it works in a ods html but I don't know if it can work in ods excel...

Re: URL in spanned headers of proc report with ods excel

Cynthia_sas — Mon, 23 Jan 2023 22:06:16 GMT

Hi: I think that would be a question for Tech Support. I have never tried to supply a link to a specific page in a PDF document from an Excel file. In my example, the link worked because all of the files being referenced were located on the same physical file system. What if the Excel file is down on a local machine but the PDF file is on a different server. I'm not confident that the HTML technique would work. So it's worth checking with Tech Support.
Cynthia

Re: URL in spanned headers of proc report with ods excel

sasuser_8 — Tue, 24 Jan 2023 14:41:15 GMT

Hi,

By technical support, do you mean the contract my organization has witht sas company ?

Re: URL in spanned headers of proc report with ods excel

Cynthia_sas — Tue, 24 Jan 2023 17:07:56 GMT

Hi: It depends. Some companies want their SAS Admin to open tracks with SAS Tech Support. Other companies allow their employees to contact Tech Support directly. To open a track with Tech Support, fill out the form at this link: http://support.sas.com/ctx/supportform/createForm and be sure to provide them with your site license number, a sample of your data and the code that they can test.
Cynthia