topic Re: Dynamic hyperlinking of cell data in different sheets in an excel based on values in SAS Procedures

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

VA1 — Thu, 18 Apr 2024 21:04:17 GMT

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;

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

Ksharp — Fri, 19 Apr 2024 02:57:14 GMT

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;

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

VA1 — Thu, 25 Apr 2024 18:52:09 GMT

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

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

Ksharp — Fri, 26 Apr 2024 01:27:05 GMT

"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.

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

VA1 — Fri, 26 Apr 2024 15:37:19 GMT

Got it! Thanks so much for explaining!