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
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;
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.