ODS EXCEL - Anchors and links

RichardAD — Tue, 19 Dec 2023 19:13:31 GMT

The documentation SAS Help Center: ODS EXCEL has an example that shows how to use the following statement

ODS EXCEL ANCHOR="anchor-name"

in conjunction with a style sheet, and it works great.

Question: Can an anchor point be created 'inside' the output created by Proc REPORT, i.e. for a cell or string inside a compute block.

Question: Is there a statement or way to add a link in the output to anchor inside the same Excel file? I.e. in HTML parlance, click on link 'X' and go to anchor '#X'

This detailed example shows the linkages (that work in HTML) that I hope to achieve in ODS EXCEL.

data links;
  length topic link1 link2 $20 ;
  input topic link1 link2 ;
  datalines;
Cars    Ford   Volkswagen
Class   James  Mary
Stocks  Intel  Microsoft
;

%let excel = * ods excel ;
%let excel =   ods excel ;

ods escapechar '^';

ods html path='c:\temp' file='demo.html' style=plateau;
&excel file='c:\temp\demo.xlsx' style=plateau;

&excel options(sheet_name="Summary");
proc report data=links;
  column topic link1 link2;
  compute topic;
    call define ('topic', 'url', '#' || trim(topic)) ;
  endcomp;
  compute link1;
    call define ('link1', 'url', '#' || trim(lowcase(topic)) || '_'  || trim(link1));
  endcomp;
  compute link2;
    call define ('link2', 'url', '#' || trim(lowcase(topic)) || '_'  || trim(link2));
  endcomp;
run;

&excel options(sheet_name="Cars");
ods text='^{dest [html] ^{raw <a name="Cars">}} ^{newline 3} Custom narrative for CARS ^{dest [html]^{raw </a>}} ^{newline 4}';
title "Cars";
proc report data=sashelp.cars ; 
  where type='Sedan' and drivetrain = 'Rear' and mpg_city > 19 or mpg_city < 13 ;
  column make make_anchor model type origin drivetrain ;
  define make / order noprint ;
  define make_anchor / 'Make' computed style=[protectspecialchars=off] ;
  compute make_anchor / character length=100 ; 
    if make ne ' ' then make_anchor = '<a name="cars_' || trim(make) || '">' || trim(make) || '</a>';
  endcomp ;
run;
  

&excel options(sheet_name="Class");
ods text='^{dest [html] ^{raw <a name="Class">}}^{newline 3}Custom narrative for CLASS ^{dest [html] ^{raw </a>}}^{newline 4}';
title "Class";
proc report data=sashelp.class; 
  column name name_anchor age sex height ;
  define name / order noprint;
  define name_anchor / 'Name' computed style=[protectspecialchars=off] ;
  compute name_anchor / character length=100; 
    if name ne ' ' then name_anchor = '<a name="class_' || trim(name) || '">' || trim(name) || '</a>';
  endcomp ;
run;

&excel options(sheet_name="Stocks");
ods text='^{dest [html] ^{raw <a name="Stocks">}}^{newline 3}Custom narrative for STOCKS ^{dest [html] ^{raw </a>}}^{newline 4}';
title "Stocks";
proc report data=sashelp.stocks;
  column stock stock_anchor date close volume ;
  where year(date) = 2005 and mod(month(date)-1,3)=0 ;
  define stock / order noprint ;
  define stock_anchor / computed style=[protectspecialchars=off] ;
  compute stock_anchor / character length=100;
    if stock ne ' ' then stock_anchor = '<a name="stocks_' || trim(stock) || '">' || trim(stock) || '</a>';
  endcomp ;
run;

ods _all_ close ;

/*
* Documentation example ;
filename css 'c:\temp\xl.css';
data _null_ ; file css ; input ; put _infile_ ; datalines ;
#Expense .header { background-color:green }
#Reports .header { background-color:red }
;
ods _all_ close;
ods excel file="c:\temp\demo.xlsx" options(sheet_interval="none") ; * cssstyle=css ;
ods excel anchor="expense";
proc print data=sashelp.class;
run;
ods excel anchor="reports" ; * cssstyle=css ;
proc print data=sashelp.class;
run;
ods excel close;
*/;

Re: ODS EXCEL - Anchors and links

Ksharp — Wed, 20 Dec 2023 09:30:04 GMT

https://communities.sas.com/t5/ODS-and-Base-Reporting/Create-Excel-with-Links-to-another-Sheet/m-p/814521

If you want link another excel from this excel,
use
call define(_col_,'url',' c:\temp\have.xlsx#Sheet1!A1 ' );

Re: ODS EXCEL - Anchors and links

Chevell_sas — Wed, 20 Dec 2023 16:31:34 GMT

Mentioned by Ksharp, linking with Excel is a little different than the other destinations. The ANCHOR= option with ODS Excel is only applicable when using CSS stylesheets.

topic Re: ODS EXCEL - Anchors and links in ODS and Base Reporting

ODS EXCEL - Anchors and links

Re: ODS EXCEL - Anchors and links

Re: ODS EXCEL - Anchors and links