Proc Report: Hyperlink cells in one sheet to cells in another sheet

Patrick — Mon, 30 Jan 2023 11:51:48 GMT

I'm trying to generate an Excel workbook with hyperlinks from a cell in one sheet to a cell in another sheet.

In below self-contained sample: What am I missing?

Why does this work with a hard-coded value but not when using variable C1ToC2_ref which got the correct value but appears to be missing at the time when I want to use it in the compute block.

%let excel_file=c:\test\test_linking.xlsx;

data class_1 class_2(drop=C1ToC2_ref);
  set sashelp.class;
  C1ToC2_ref=cats('#class_2!A',_n_);
run;

ods _all_ close;

ods escapechar='^';
ods excel file="&excel_file" 
  options(
  frozen_headers="1"
  frozen_rowheaders="1"
  sheet_interval='none'
  );

ods excel options(sheet_name="class_1" sheet_interval='now');

proc report data=class_1;
  compute name;
/*    urlstring = "#class_2!A3";*/
    urlstring = C1ToC2_ref;
    if not missing(urlstring) then
      do;
        call define(_col_, 'url',urlstring);
        call define(_col_, 'style', 'style={textdecoration=underline color=blue}');
      end;
  endcomp;
run;

ods excel options(sheet_name="class_2" sheet_interval='now');
proc report data=class_2;
run;

ods excel close;
ods listing;

What I want is an Excel workbook that when I click in sheet class_1 on Alice it takes me to sheet class_2, cell A3

Re: Proc Report: Hyperlink cells in one sheet to cells in another sheet

data_null__ — Mon, 30 Jan 2023 15:20:35 GMT

I think you need to put

C1ToC2_ref

in the column statement before NAME.

Re: Proc Report: Hyperlink cells in one sheet to cells in another sheet

Patrick — Mon, 30 Jan 2023 22:31:01 GMT

Ooooh! Variable order matters! Yep, that did the trick.

topic Proc Report: Hyperlink cells in one sheet to cells in another sheet in ODS and Base Reporting

Proc Report: Hyperlink cells in one sheet to cells in another sheet

Re: Proc Report: Hyperlink cells in one sheet to cells in another sheet

Re: Proc Report: Hyperlink cells in one sheet to cells in another sheet