Dynamic hyperlinking of cell data in different sheets in an excel based on values

[VA1]

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; 

[Ksharp · Accepted solution]

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;

[VA1]

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

[Ksharp]

"call symputx(cats('_',d),_n_+1);"
Here I make four macro variables from dataset 'Sheet2';
Macro_Var_Name Macro_Var_Value
_AAA 2
_910 3
_123 4
234 5

You see 2,3,4,5 correspond to the row number of D column in Sheet2.

" urlstring= cats("#Sheet2!D",symget(cats('_',a)));"
Function cats('_',a) make a string like : '_123' , '_234' , '_AAA' from Sheet1.
Function symget('_AAA') means get the value of macro variable _AAA , same as '_123' , '_234' .
So when a='AAA' in Sheet1, I could get macro variable _AAA 's value 2. And urlstring= "#Sheet2!D2".
And I make a URL on the cell (a='AAA' in Sheet1) by "call define(_col_,'URL','#Sheet2!D2');" which redirect to Sheet2 's D2 cell.

[VA1]

Got it! Thanks so much for explaining!