Hello everyone,
I am looking for some help on how to hyperlink between excel sheet 1 cell data to excel sheet 2 cell data – both excel sheets can have values in random cells (of column A & D), I want to Match the values & hyperlink between those matched cells. Ex: as given below, this data can vary from report to report so I want to keep this match & link dynamic. Can this be achieved with sas code?
Right now I am just able to hyperlink to certain cell in Sheet 2 in proc report, not by values.
compute r_id; urlstring= "#'"||strip('Rules')||"'!A2"; call define(_col_,'URL',urlstring); call define(_col_,'style','style={textdecoration=underline color=blue}'); endcomp;
Of course, You can.
/*
Yes. You can.
You need to save these positions firstly.
*/
data sheet1;
input a $;
cards;
123
234
AAA
;
data sheet2;
input a b c d $;
cards;
1 2 3 AAA
1 2 3 910
1 2 3 123
1 2 3 234
;
data _null_;
set sheet2;
call symputx(cats('_',d),_n_+1);
run;
ods excel file='c:\temp\temp.xlsx';
ods excel options(sheet_name='Sheet1');
proc report data=sheet1 nowd;
define a/display;
compute a;
urlstring= cats("#Sheet2!D",symget(cats('_',a)));
call define(_col_,'URL',urlstring);
call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;
run;
ods excel options(sheet_name='Sheet2');
proc report data=sheet2 nowd;
run;
ods excel close;
Of course, You can.
/*
Yes. You can.
You need to save these positions firstly.
*/
data sheet1;
input a $;
cards;
123
234
AAA
;
data sheet2;
input a b c d $;
cards;
1 2 3 AAA
1 2 3 910
1 2 3 123
1 2 3 234
;
data _null_;
set sheet2;
call symputx(cats('_',d),_n_+1);
run;
ods excel file='c:\temp\temp.xlsx';
ods excel options(sheet_name='Sheet1');
proc report data=sheet1 nowd;
define a/display;
compute a;
urlstring= cats("#Sheet2!D",symget(cats('_',a)));
call define(_col_,'URL',urlstring);
call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;
run;
ods excel options(sheet_name='Sheet2');
proc report data=sheet2 nowd;
run;
ods excel close;
Thanks Ksharp!
Your code worked smoothly! However, I didn't quite understand how symput/symget worked in this case, value matching and hyperlinking? Could you please explain a little?
Best,
VA1
Got it! Thanks so much for explaining!
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.