BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
VA1
Calcite | Level 5 VA1
Calcite | Level 5

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?

VA1_1-1713474057533.png

 

 

 

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; 
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

 

View solution in original post

4 REPLIES 4
Ksharp
Super User

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
Calcite | Level 5 VA1
Calcite | Level 5

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
Super User
"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
Calcite | Level 5 VA1
Calcite | Level 5

Got it! Thanks so much for explaining!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1349 views
  • 1 like
  • 2 in conversation