URL in spanned headers of proc report with ods excel

[sasuser_8]

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;

[Ksharp]

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;

[sasuser_8]

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

[Cynthia_sas · Accepted solution]

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

[sasuser_8]

It works perfectly!

Thank you 🙂

[sasuser_8]

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

[Cynthia_sas]

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

[sasuser_8]

Hi,

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

[Cynthia_sas]

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