The SAS Output Delivery System and reporting techniques

hyperlink to the excel specific cells when using ODS excel_xp.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

hyperlink to the excel specific cells when using ODS excel_xp.

Hello, I have some datasets to output to excel files. The first sheet is the summary. second sheet is the questions problems. I want to create a hyperlink on Flags column of the summary sheet which links to questions sheet. Then when I click the number of the Flags column, it will go to questions sheet corresponding cells. For example, if I click on row 12 , flags column, the number 4, it will go to questions sheet row 3. Also the number of flags may change depending on the data. 

 

 

 

 

 


Accepted Solutions
Solution
‎01-16-2018 09:40 AM
SAS Super FREQ
Posts: 9,253

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Is cumsum numeric? If so, then you cannot refer to cumsum by the simple name. Did you notice that I define cumsum as having a usage of DISPLAY. When I run my code using
define cumsum / display noprint;

the URL string is built correctly for me.

cynthia

View solution in original post


All Replies
Frequent Contributor
Posts: 102

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Here is my excel examples

SAS Super FREQ
Posts: 9,253

Re: hyperlink to the excel specific cells when using ODS excel_xp.

[ Edited ]

Hi:
Can you post the code you've tried ... and some data? Seeing your desired output is good, but it will be hard for anyone to make any suggestions without code or data to test.  Are you using ODS? PROC EXPORT? The Excel LIBNAME engine?
cynthia

Frequent Contributor
Posts: 102

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Posted in reply to Cynthia_sas

Here is the code usually I used. 

 

ODS noresults;
ODS listing close; /*Turn off the standard line printer destination*/
ods tagsets.ExcelXP path="&dir."
file="Example.xml"
style=seaside /*Styles to control appearance of output*/;
ods tagsets.ExcelXP
options ( sheet_name= "Summary" autofit_height= 'yes' frozen_rowheaders="8" frozen_headers= '2' absolute_column_width= '30,15,15,6,8,8,8,8,10,10,10,10,10,10,10,10,10,10,10,10');
options missing=" ";
proc Report data=summary1 NOWD
style(header)=[foreground=white background=teal];
Column ReportingGroup exam form OP_Item en Raw_Score_Mean Raw_Score_Std Mean_Pval Mean_Ptbis qn;
define exam / center display "Exam" ;
define form / center display "Form" ;
define op_item / center display "OP Items" ;
define en /center display "N";
define Raw_Score_Mean /center display "Raw Score Mean" style(column)={tagattr="format:###.00"};
define Raw_Score_Std /center display "Raw Score Stdev" style(column)={tagattr="format:###.00"};
define Mean_Pval /center display "Mean P value" style(column)={tagattr="format:##0.00"};
define Mean_Ptbis /center display "Mean Point Biserial" style(column)={tagattr="format:##0.00"};
define qn /center display "Flags";
compute exam;
count+1;
if (mod(count,2)) then do;
call define(_row_,"style","style=[background=CXECEDEC]");
end;
endcomp;

compute form;
urlstring= "#'"||strip(Flags)||"'!A1";
call define(_col_,'URL',urlstring);
call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;*/
run;

ods tagsets.ExcelXP
options ( sheet_name= "Questions" autofit_height= 'yes' frozen_headers= '2' frozen_rowheaders="8" absolute_column_width= '20,10,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8,8');
options missing=" ";
proc Report data=questions NOWD
style(header)=[foreground=white background=teal];
Column ("General Info" ReportingGroup exam form Item status key n pval ptbis qn disturb)("Pval" propa propb propc propd propblank);
define exam / center display "Exam" ;
define form / center display "Form" ;
define item / center display "Items" ;
define status / center display "Status";
define key / center display "Key";
define n /center display "N";
define qn /center display "Question Item #";
define disturb / center display "Distractor" style(column)=[font_weight=bold];
compute exam;
count+1;
if (mod(count,2)) then call define(_row_,"style","style=[background=CXECEDEC]");
endcomp;

compute propa;
if propa=pval then call define(_col_,"style","style=[font_weight=bold]");
else if n>&numcount. and propa > pval+0.1 then call define(_col_,"style","style=[foreground=red]");
endcomp;

compute propb;
if propb=pval then call define(_col_,"style","style=[font_weight=bold]");
else if n>&numcount. and propb> pval+0.1 then call define(_col_,"style","style=[foreground=red]");
endcomp;

compute propc;
if propc=pval then call define(_col_,"style","style=[font_weight=bold]");
else if n>&numcount. and propc > pval+0.1 then call define(_col_,"style","style=[foreground=red]");
endcomp;

compute propd;
if propd=pval then call define(_col_,"style","style=[font_weight=bold]");
else if n>&numcount. and propd > pval+0.1 then call define(_col_,"style","style=[foreground=red]");
endcomp;
run;

ods _all_ close; /* Close and release the xml file so it can be opened with Excel*/
ODS listing;

SAS Super FREQ
Posts: 9,253

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Hi, one issue I see is this:

Column ReportingGroup exam form OP_Item en Raw_Score_Mean Raw_Score_Std Mean_Pval Mean_Ptbis qn;

 

and then later you have this COMPUTE block for FORM where you try to use a variable called FLAGS to make the URLSTRING temp variable:

compute form;
  urlstring= "#'"||strip(Flags)||"'!A1";
  call define(_col_,'URL',urlstring);
  call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;

 

But I did NOT see a FLAGS variable in your COLUMN statement. You can only use the items in your COLUMN statement or temporary variables. In your code, I did not see a temp variable for FLAGS. I do see an item called QN, which you label as 'Flags' -- but you cannot refer to an item by the label, you'd have to use the variable name.

 

  The other issue is that PROC REPORT works from left to right in allowing references. So, given the placement of the FORM item, you could only use items to the left (ReportingGroup and Exam) to build URLSTRING. My guess is that the URL string is being built incorrectly. If you want to revise your report to use QN in the creation of the URLSTRING, then you have to move it on the other side of the FORM variable:

Column ReportingGroup exam qn form OP_Item en Raw_Score_Mean Raw_Score_Std Mean_Pval Mean_Ptbis ;

 

and then change the COMPUTE block:

compute form;

  length urlstring $100;
  urlstring= "#'"||strip(QN)||"'!A1";
  call define(_col_,'URL',urlstring);
  call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;

 

  Hope this helps. Here are some previous postings on creating internal links within TAGSETS.EXCELXP output:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Excel-XP-and-Hyperlink/td-p/32709
https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-Report-Hyperlink-using-ExcelXP/td-p/29817...
https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Tagsets-ExcelXP-and-placing-hypelinks-to-o...

 

cynthia

Frequent Contributor
Posts: 102

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Posted in reply to Cynthia_sas

Thank you very much Cynthia_sas! You are right. I did make some mistakes here because I usually use the hyperlink link to the different sheets with the form name. If I still use the code here

 

compute qn;
urlstring= "#'"||strip(qn)||"'!A1";
call define(_col_,'URL',urlstring);
call define(_col_,'style','style={textdecoration=underline color=blue}');
endcomp;
run;

 

When I click the values of qn column which is the flag column in Summary excel sheet, it will go to the first row of the sheet named with qn value. That is the way I usually used before.

 

However this time I prefer to go to the corresponding row of the sheet named questions. That means hyperlinks link to the rows of specific sheet. Different the qn's value(1,3,3,4,2) correspond to different rows. If qn =

0

1

0

0

5

1

2

When I click 1, it will go the first row of question sheet. When I click 5, it will got to second row of question sheet. If I click 1, it will go to 7th row of question sheet, and so on. 

SAS Super FREQ
Posts: 9,253

Re: hyperlink to the excel specific cells when using ODS excel_xp.

[ Edited ]

Hi: It seems to me that your URLSTRING is always going to A1 because that is what you have hardcoded:
urlstring= "#'"||strip(qn)||"'!A1";

If you need it to be some value OTHER than A1, such as A2 or A5 or A3, then you would need to code for that -- SAS doesn't know what cell will be used in Excel when the output file is opened but you seem to know that information. So you'd have to test using the qn value instead of the hardcoded A1.

cynthia

Frequent Contributor
Posts: 102

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Posted in reply to Cynthia_sas

many thanks!

Frequent Contributor
Posts: 102

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Hello Cynthia_SAS,

 

I am still struggling in this problem. I reshaped my code but it still didn't work. Here is part of my code. 

 

proc Report data=table1 NOWD;
Column ("General Info" cumsum Reporting exam form n) ("Group" p1 p2 p3 p4 p5) ;
define cumsum/ noprint;
define Reporting / left display "Reporting_Group" group;
define exam / center display "Exam" ;
define form / center display "Form" ;
define n /center display "Flags";
define p1 /center display "P<=0" ;
define p2 /center display "0<P<=0.4";
define p3 /center display "0.4<P<=0.9" ;
define p4 /center display "0.9<P<=1.3" ;
define p5 /center display "P>1.3";
compute reporting;
count+1;
if (mod(count,2)) then do;
call define(_row_,"style","style=[background=CXECEDEC]");
end;

endcomp;

 

compute n;
if n~="0" then do;
urlstring= "C:\temp\exam_report_2017..xml#questions!A||'cumsum'";
call define("exam",'URL',urlstring);
call define("exam",'style','style={textdecoration=underline color=blue}');
end;
endcomp;

run;

 

cumsum is the cumulative number of n.

  n= 2               cumsum=2

       1                               3

       0                               3

       0                               3

       4                               7

       0                               7

............................................

If I use urlsting="C:\temp\exam_report_2017..xml#questions!A12"", i.e. hardcoding the rows number A1 , A3, A8......, the link works. If I want to refer to specific rows , row number= cumsum, how do I code? 

urlstring= "C:\temp\exam_report_2017..xml#questions!A||'cumsum'"  doesn't work.

 

Thanks a lot!

SAS Super FREQ
Posts: 9,253

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Hi:

  It is impossible to test your code without any data. I made some fake data using SASHELP.CLASS. The value for CUMSUM is arbitrary -- I used use the last digit of AGE.

 

  I believe you have a quoting issue. When you surround the variable name 'cumsum' in quotes, then you get the variable name in the value for URLSTRING.

 

  I prefer to use the CATT functions because then I get a warning if the resolved string is bigger than the item length and trimming of trailing spaces is automatically done for me. When I use just the variable name in my URLSTR2 variable, I believe I get something close to what you want. I did not test it in Excel, but the resolved value for URLSTR2 looks like what you described:

 

quote_issue.png

 

Hope this helps. If you continue to have issues, I'd recommend opening a track with Tech Support so they can look at ALL your code and ALL your data and help you find a resolution.

 

cynthia

Frequent Contributor
Posts: 102

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Posted in reply to Cynthia_sas

Hello Cynthia_sas,

Thank you very much for your suggestion. I appreciate. I rewrite my code just like belows:

 

proc Report data=table1 NOWD;
Column ("General Info" cumsum urlstring Reporting exam form n) ("Group" p1 p2 p3 p4 p5) ;
define cumsum/ noprint;

define urlstring/ computed noprint;
define Reporting / left display "Reporting_Group" group;
define exam / center display "Exam" ;
define form / center display "Form" ;
define n /center display "Flags";
define p1 /center display "P<=0" ;
define p2 /center display "0<P<=0.4";
define p3 /center display "0.4<P<=0.9" ;
define p4 /center display "0.9<P<=1.3" ;
define p5 /center display "P>1.3";
compute reporting;
count+1;
if (mod(count,2)) then do;
call define(_row_,"style","style=[background=CXECEDEC]");
end;

endcomp;

 

compute urlstring/character length=200;
urlstring=catt("&dir.\Passrate_report_&mon..xml#Items!A",cumsum);
endcomp;

 

compute n;
if n~="0" then do;
call define("exam",'URL',urlstring);
call define("exam",'style','style={textdecoration=underline color=blue}');
end;

endcomp;

run;

 

The funny thing is if I use noprint  on cumsum, my hyperlinks do not work, and if  I print cumsum, the hyperlinks work. But in most cases, I don't want cumsum shows on my report.  Can you give some suggestion about that?  

 

Solution
‎01-16-2018 09:40 AM
SAS Super FREQ
Posts: 9,253

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Is cumsum numeric? If so, then you cannot refer to cumsum by the simple name. Did you notice that I define cumsum as having a usage of DISPLAY. When I run my code using
define cumsum / display noprint;

the URL string is built correctly for me.

cynthia
Frequent Contributor
Posts: 102

Re: hyperlink to the excel specific cells when using ODS excel_xp.

Posted in reply to Cynthia_sas

Thank you very much for the helps. It works!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 739 views
  • 0 likes
  • 2 in conversation