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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.