The SAS Output Delivery System and reporting techniques

Proc Report Hyperlink using ExcelXP

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Proc Report Hyperlink using ExcelXP

Hello Folks,

 

I am having trouble using a variable containing URL values to insert hyperlinks in my Excel output that is generated using the ExcelXP tagset.  Here's a short program that generates the results I want in the first Proc Report but I need to use a variable like in the second Proc Report instead of the hard coding:

 

data air;
length URL $30;
set sashelp.air(obs=10);
URL = 'http://microsoft.com';
run;

 

ods tagsets.excelxp
file="/home/sasapps/dev/programs/universe/report/report_001.xml"
style=printer
options(sheet_name="tabone"
);

 

proc report
data=air
nowd
;
column air;

compute air;
call define(_col_,'url', 'http://google.com' );
endcomp;
run;

 

ods tagsets.excelxp
options(sheet_name="tabtwo"
);

 

proc report
data=air
nowd
;
column air URL;
define URL / group noprint;

compute air;
call define(_col_,'url', URL );
endcomp;
run;

 

ods tagsets.excelxp close;

 

I'm using SAS 9.3 TS Level 1M2 on AIX 7.1 and Excel 2013.

 

Thanks,

Paul OldenKamp


Accepted Solutions
Solution
‎09-14-2016 01:13 PM
SAS Super FREQ
Posts: 8,720

Re: Proc Report Hyperlink using ExcelXP

Hi, Paul:

  Both of these methods work for me. I tend to use the Format method more because it doesn't require a COMPUTE block but if you need to do the trafficlighting based on more than 1 value, then you are committed to the COMPUTE block and call define.

 

  You don't have a COLUMN statement in your code -- this may come back to bite you. I assume that URL is the first column because of your LENGTH statement, but remember that PROC REPORT works from LEFT to RIGHT. So as long as the URL variable is to the left of the variable in your COMPUTE block then everything should work hunky dory. But if the URL variable is to the right of the variable you want to use for the link, then the CALL DEFINE might not work.

 

  As a best practice, I always code a COLUMN statement. I got links in the HTML file and the XML file from TAGSETS.EXCELXP -- so I would expect this to work the same way in SAS 9.3.

 

cynthia

 

data newclass;
  length urlvar $100;
  set sashelp.class;
  if sex = 'M' then urlvar = 'http://www.google.com';
  else urlvar= 'http://www.etsy.com';
run;
title; footnote;
ods html file='c:\temp\useurl.html' ; ods tagsets.excelxp file='c:\temp\useurl.xml' style=htmlblue;
title 'Method 1 -- with variable'; proc report data=newclass; column name urlvar sex age height weight; define name / order; define urlvar / display noprint; define sex / display; compute sex; call define(_col_,'url',urlvar); endcomp; run; ods _all_ close; proc format; value $urlfmt 'M' = 'http://www.microsoft.com' 'F' = 'http://www.amazon.com'; run; ods html file='c:\temp\url_method2.html'; ods tagsets.excelxp file='c:\temp\url_method2.xml' style=htmlblue;
title 'Method 2 -- with format'; proc report data=sashelp.class; column name sex age height weight; define name / order; define sex / display style(column)={url=$urlfmt.}; run; ods _all_ close;

View solution in original post


All Replies
Grand Advisor
Posts: 17,396

Re: Proc Report Hyperlink using ExcelXP

So I'm assuming that the second doesn't work? what happens if you include URL in define statement before air and then do a compute air with the URL value? 

 

Your approach is correct according to page 11 here

http://support.sas.com/rnd/papers/sgf07/sgf2007-excel.pdf

 

I think you're running into the columns available when doing a calculation issue instead. 

Solution
‎09-14-2016 01:13 PM
SAS Super FREQ
Posts: 8,720

Re: Proc Report Hyperlink using ExcelXP

Hi, Paul:

  Both of these methods work for me. I tend to use the Format method more because it doesn't require a COMPUTE block but if you need to do the trafficlighting based on more than 1 value, then you are committed to the COMPUTE block and call define.

 

  You don't have a COLUMN statement in your code -- this may come back to bite you. I assume that URL is the first column because of your LENGTH statement, but remember that PROC REPORT works from LEFT to RIGHT. So as long as the URL variable is to the left of the variable in your COMPUTE block then everything should work hunky dory. But if the URL variable is to the right of the variable you want to use for the link, then the CALL DEFINE might not work.

 

  As a best practice, I always code a COLUMN statement. I got links in the HTML file and the XML file from TAGSETS.EXCELXP -- so I would expect this to work the same way in SAS 9.3.

 

cynthia

 

data newclass;
  length urlvar $100;
  set sashelp.class;
  if sex = 'M' then urlvar = 'http://www.google.com';
  else urlvar= 'http://www.etsy.com';
run;
title; footnote;
ods html file='c:\temp\useurl.html' ; ods tagsets.excelxp file='c:\temp\useurl.xml' style=htmlblue;
title 'Method 1 -- with variable'; proc report data=newclass; column name urlvar sex age height weight; define name / order; define urlvar / display noprint; define sex / display; compute sex; call define(_col_,'url',urlvar); endcomp; run; ods _all_ close; proc format; value $urlfmt 'M' = 'http://www.microsoft.com' 'F' = 'http://www.amazon.com'; run; ods html file='c:\temp\url_method2.html'; ods tagsets.excelxp file='c:\temp\url_method2.xml' style=htmlblue;
title 'Method 2 -- with format'; proc report data=sashelp.class; column name sex age height weight; define name / order; define sex / display style(column)={url=$urlfmt.}; run; ods _all_ close;
Occasional Contributor
Posts: 7

Re: Proc Report Hyperlink using ExcelXP

Hi Cynthia,

 

I think that I do have a column statemet in each of the Proc Reports but I committed exactly the error you are discussing.  If I change

 

Column air URL;

to

Column URL air;

 

in the second Proc Report, the hyperlinks are generated.

 

Thanks,

Paul

SAS Super FREQ
Posts: 8,720

Re: Proc Report Hyperlink using ExcelXP

Hi, Paul:
Sorry, you're right, you do have a COLUMN statement, I was reading too fast and skipped over it. But I'm glad that fixed it.

cynthia
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 627 views
  • 2 likes
  • 3 in conversation