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

I am trying to find a character within a string and replace it with something else, within the data step.
For eg.
My&Name&Is&Simon
Hello&World
Simon

I want to replace the & with a space.

I have tried a few of the macros available on here but they only seem to replace the first instance only.
Any ideas?

thanks
Simon
1 ACCEPTED SOLUTION

Accepted Solutions
yonib
SAS Employee

You can use translate function.

For example:

 

data a;
 input x $40.;
 y=translate(x,' ','&');
cards;
My&Name&Is&Simon
Hello&World
Simon
;
run;

Result:

 

x                   y

My&Name&Is&Simon    My Name Is Simon
Hello&World         Hello World     
Simon               Simon           

From @sbb: For more than one character in a sequence, use the TRANWRD function.

 

From @Patrick: And since it looks like you might be trying to decode URL strings, check out the URLDECODE function which handles your specific task.

 

data _null_;
 infile datalines truncover;
 input text $40.;
 text=translate(text,' ','&');
 text=urldecode(text);
 put text;
datalines;
My&Name&Is&Simon
Simon%27s&World
%27 - '
%28 - (
%29 - )
;

Output:

 

My Name Is Simon
Simon's World
' - '
( - (
) - )

View solution in original post

7 REPLIES 7
yonib
SAS Employee

You can use translate function.

For example:

 

data a;
 input x $40.;
 y=translate(x,' ','&');
cards;
My&Name&Is&Simon
Hello&World
Simon
;
run;

Result:

 

x                   y

My&Name&Is&Simon    My Name Is Simon
Hello&World         Hello World     
Simon               Simon           

From @sbb: For more than one character in a sequence, use the TRANWRD function.

 

From @Patrick: And since it looks like you might be trying to decode URL strings, check out the URLDECODE function which handles your specific task.

 

data _null_;
 infile datalines truncover;
 input text $40.;
 text=translate(text,' ','&');
 text=urldecode(text);
 put text;
datalines;
My&Name&Is&Simon
Simon%27s&World
%27 - '
%28 - (
%29 - )
;

Output:

 

My Name Is Simon
Simon's World
' - '
( - (
) - )
LinusH
Tourmaline | Level 20
textVar2=translate(textVar,' ','&');

/Linus
Data never sleeps
deleted_user
Not applicable
Hi

Thanks for that...it works, but now have another problem....
My string also contains other things i wanted to replace, like
'%27' i want to replace with an apostrophe
so my original string is
Simon%27s&World

translate works at replacing the & but i tried
translate(translate(title,' ','&'),"%27","'")
but it still only replces the &
Help would be much appreciated.

Thanks
deleted_user
Not applicable
in fact i have half a dozen replacements to do:
%27 - '
%28 - (
%29 - )
and a few others... any ideas?

thanks
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Use the TRANWRD function - similar to TRANSLATE.

Refer to SAS Language DOC at the link below:


Scott Barry
SBBWorks, Inc.

SAS 9.2 TRANWRD DOC:

http://support.sas.com/documentation/cdl/en/lrdict/59540/HTML/default/a000215027.htm


SAS 9.1 DOC:

http://support.sas.com/documentation/onlinedoc/91pdf/index.html


SAS DOC main page (choose your SAS component and version):

http://support.sas.com/documentation/index.html
deleted_user
Not applicable
Perfect thanks!!
Patrick
Opal | Level 21
Hi Simon

Looks like URL escape syntax.
Try this - it should also cover cases which might not yet be in your current data:

data _null_;
infile datalines truncover;
input text $40.;
text=translate(text,' ','&');
text=urldecode(text);
put text;
datalines;
My&Name&Is&Simon
Simon%27s&World
%27 - '
%28 - (
%29 - )
;

http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a001191534.htm

HTH
Patrick

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
  • 7 replies
  • 108016 views
  • 2 likes
  • 5 in conversation