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
->
I think you need to put
C1ToC2_ref
in the column statement before NAME.
I think you need to put
C1ToC2_ref
in the column statement before NAME.
Ooooh! Variable order matters! Yep, that did the trick.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.