PROC REPORT/ODS EXCEL/Hyperlinks in Excel not working if they contain #IDX

PaigeMiller — Tue, 14 Mar 2023 19:21:45 GMT

I create an .HTML file with BY variables, each BY variable output in the .HTML file can be accessed by adding the appropriate #IDX to the url. But I can't get this to work from hyperlinks in Excel, although if I type the proper hyperlink with #IDX into my web browser, it works. Excel tells me it cannot open the specified file. So here's a simple example.

/* First create HTML File with by groups */
ods html path="f:\my documents" file="test.html";
proc sort data=sashelp.class out=class;
by sex age;
run;
proc print data=class;
by sex;
run;
ods html close;

/* Hyperlink to specific part of HTML file, hyperlinks from Excel don't work */
ods excel file="f:\my documents\test.xlsx";
proc report data=class;
    columns sex height weight;
    define sex/group;
    define height/mean;
    define weight/mean;
    compute weight;
        if sex='F' then call define('_c3_','style','style={url="f:\my documents\test.html#idx"}');
        if sex='M' then call define('_c3_','style','style={url="f:\my documents\test.html#idx1"}');
    endcompute;
run;
ods excel close;


/* Repeat without IDX -- now hyperlinks work */
ods excel file="f:\my documents\test1.xlsx";
proc report data=class;
    columns sex height weight;
    define sex/group;
    define height/mean;
    define weight/mean;
    compute weight;
        if sex='F' then call define('_c3_','style','style={url="f:\my documents\test.html"}');
        if sex='M' then call define('_c3_','style','style={url="f:\my documents\test.html"}');
    endcompute;
run;
ods excel close;

How do I get this to work?

Re: PROC REPORT/ODS EXCEL/Hyperlinks in Excel not working if they contain #IDX

ballardw — Tue, 14 Mar 2023 21:41:49 GMT

Maybe something Excel is expecting?

I ran your example but I don't have Excel and use LibreOffice instead. Adjusting your code so the #idx numbers matched the ones in the HTML I created it opened the link. But I didn't see anything that really indicated which of the two By group tables were considered, just opened the HTML in general. It might be that LibreOffice ignored the #idx bit and just opened the html though.

topic PROC REPORT/ODS EXCEL/Hyperlinks in Excel not working if they contain #IDX in ODS and Base Reporting

PROC REPORT/ODS EXCEL/Hyperlinks in Excel not working if they contain #IDX

Re: PROC REPORT/ODS EXCEL/Hyperlinks in Excel not working if they contain #IDX