topic Re: Inserting additional characters into a column in SAS Enterprise Guide

Inserting additional characters into a column

BillS — Wed, 10 Sep 2008 19:56:00 GMT

Currently, I have a column that contains the serial number of the row. I need to recode this column so that it is a weblink. For example:

currently the column contains SERIAL

I need to replace SERIAL with "a href=http://website.com/SERIALSERIAL"

Thanks

Bill

Re: Inserting additional characters into a column

Cynthia_sas — Wed, 10 Sep 2008 20:32:35 GMT

Hi:
This is something that is relatively easy to do with PROC REPORT. You have 2 ways to do it. You can either do it in a user-defined format and then use the URL= suboption to build the link. Or, you can use a CALL DEFINE statement with the URL attribute as argument 2 to the CALL DEFINE statement.

These 2 methods take advantage of the fact that ODS knows how to build an anchor tag. So you do not have to code the entire URL <A> tag. ODS does that for you.

Both methods are shown in the program below. (the URL= style attribute method will also work with PROC PRINT and PROC TABULATE and you could use it in a custom TABLE template. However, the CALL DEFINE method to build a dynamic URL can only be used with PROC REPORT. The advantage of the format method is that it's simpler to code (even if you have a LOT of serial numbers, you could generate the format in a program if you really needed to); the advantage of the other method (CALL DEFINE) is that the URL is built dynamically -- without having a user-defined format.

cynthia
[pre]
data serialnums;
infile datalines;
input serial $ val1 val2 state $;
return;
datalines;
abc123 12 4 WA
abc123 15 3 CA
abc123 25 10 ND
xyz123 10 5 NC
xyz123 10 4 MA
xyz123 5 2 DE
;
run;

proc format;
value $fmtser 'xyz123' = 'http://website.com/xyz123'
'abc123' = 'http://website.com/abc123';
run;

ods html path='c:\temp' (url=none)
file='linkserial.html' style=sasweb;

proc report data=serialnums nowd
split='#';
title 'Two Ways to Do URL Links';
column serial serial=otherway val1 val2 n;
define serial / group
style(column)={url=$fmtser.};
define otherway / group 'Alternate#Method';
define val1 / sum "Amt#Sold";
define val2 / sum "Amt#Discounted";
define n / 'Number#of States';
compute otherway;
length tmpvar $30;
tmpvar=catt('http://website.com/',serial);
call define(_COL_,'URL',tmpvar);
endcomp;
run;
ods html close;
title;

[/pre]

Re: Inserting additional characters into a column

yonib — Thu, 11 Sep 2008 07:18:54 GMT

My solution:

data a;
input x $20. ;
if substr(x,1,6)='serial' then y='wwwyony///ssa#$%';
else y=x;
cards;
serial
;
run;

Hope I understood what you want

Re: Inserting additional characters into a column

LinusH — Thu, 11 Sep 2008 09:26:02 GMT

My guess is:

data outputTable;
length serial $200;
set inputTable ;
serial=compbl("a href=http://website.com/"||SERIAL||">"||SERIAL);
run;

/Linus

Re: Inserting additional characters into a column

Cynthia_sas — Thu, 11 Sep 2008 20:40:58 GMT

This solution (putting the entire anchor tag) into an output data set is building a column that contains an anchor tag. The anchor tag will not turn into a link until HTML comes into play somehow -- this points to a report procedure and ODS HTML at a minimum or FILE PRINT ODS and ODS HTML. At the very least, in EG, you would have to run a List Data task on the output table in order to be able to see the links "in action".

Also, the URL that you build, must be syntactically correct. An <A> tag is not syntactically correct until the </A> is also provided. ( I suspect that you had the closing tag in your code, but that it was "disappeared" by the forum text editor.)

If you use the SERIALNUMS data from the above example, either of these will produce a detail report table with a hyperlink on every row -- assuming that the anchor tag is already a column in the data. However, if all you want is a hyperlink in the HTML output report, then PROC REPORT can also do that in a CALL DEFINE without needing to build a separate column.

EG is doing the equivalent of the ODS HTML "sandwich" for you, so the PROC PRINT on the output table is the equivalent of the List Data task. I highlighted the background of the link to make it stand out from the "regular" SERIAL column.

cynthia

[pre]
data serialnums;
infile datalines;
input serial $ val1 val2 state $;
return;
datalines;
abc123 12 4 WA
abc123 15 3 CA
abc123 25 10 ND
xyz123 10 5 NC
xyz123 10 4 MA
xyz123 5 2 DE
;
run;

ods html file='c:\temp\showlinks_detail.html' style=sasweb;

proc report data=serialnums nowd
split='#';
title 'Detail Report With URL Links directly from SERIAL value';
column serial val1 val2 state;
define serial / display;
define val1 / sum "Amt#Sold";
define val2 / sum "Amt#Discounted";
define State / 'State #Code';
compute serial;
length tmpvar $30;
tmpvar=catt('http://website.com/',serial);
call define(_COL_,'URL',tmpvar);
endcomp;
run;

title 'With Data Step directly';
data outputTable;
set serialnums ;
length ser_link $200;
ser_link=compbl("<a href=http://website.com/"||SERIAL||">"||SERIAL||'</a>');
file print ods;
put _ods_;
run;

proc print data=outputTable;
title 'Print on Data set: link contained in new column';
var serial val1 val2 state;
var ser_link / style(data)={background=cxeeeeee};
run;
ods html close;

[/pre]