BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaulOK01
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
Reeza
Super User

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. 

Cynthia_sas
SAS Super FREQ

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;
PaulOK01
Fluorite | Level 6

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

Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2624 views
  • 3 likes
  • 3 in conversation