Text mining and content categorization

Replace blank with a specific character

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Replace blank with a specific character

Hi,

I was trying to replace each occurrence of blank with a specific character.

%let myfilename=aaa bbb;

%let tmpattachedfilename=/mydir/%sysfunc(tranwrd(&myfilename., " ", "")).csv;

if i run

%put &tmpattachedfilename;

the result is /mydir/aaa bbb.csv

but the blank between aaa and bbb has not been replaced.

What is wrong in my code?

many thanks.

Antonio


Accepted Solutions
Solution
‎02-06-2013 06:57 AM
Super User
Super User
Posts: 6,133

Re: Replace blank with a specific character

In macro code you do not need to put quotes around strings, and if you do they will be treated as part of the string.

So your TRANWRD function did nothing as it did not find any matches for the three character sequence, " " , that you told it to replace.

Use %STR( ) to represent a single space.

%let tmpattachedfilename=%sysfunc(tranwrd(/mydir/&myfilename..csv,%str( ),));

Or more simply if you want to remove the spaces then use the COMPRESS function.

%let tmpattachedfilename=%sysfunc(compress(/mydir/&myfilename..csv,%str( )));

View solution in original post


All Replies
Solution
‎02-06-2013 06:57 AM
Super User
Super User
Posts: 6,133

Re: Replace blank with a specific character

In macro code you do not need to put quotes around strings, and if you do they will be treated as part of the string.

So your TRANWRD function did nothing as it did not find any matches for the three character sequence, " " , that you told it to replace.

Use %STR( ) to represent a single space.

%let tmpattachedfilename=%sysfunc(tranwrd(/mydir/&myfilename..csv,%str( ),));

Or more simply if you want to remove the spaces then use the COMPRESS function.

%let tmpattachedfilename=%sysfunc(compress(/mydir/&myfilename..csv,%str( )));

Contributor
Posts: 58

Re: Replace blank with a specific character

The compress seems works better that tranword:-)

Contributor
Posts: 71

Re: Replace blank with a specific character

The compress functions is the appropriate method in your case.  I'd personally stick with that solution.

It's interesting that the tranwrd example does not work but the translate function seems to:

%let A=/mydir/aaa bbb.csv;

%let B=%sysfunc(translate(&A, '_', ' '));

%put &B;

It's a good practice to avoid using spaces in file names for files that will be inputs to computer process (i.e. data files for data analysis).  Most people use underscores instead of spaces. 

Tranwrd seems to work fine if used in a data step:

%let myfilename=/mydir/aaa bbb.csv;

data temp;

newfilename = tranwrd("&myfilename",' ', '_');

run;

proc print data = temp;run;

Or this one (which is more helpful because the new file name is in a macro instead of an observation):

%let myfilename=aaa bbb.csv;

data _null_;

call symput('myfilename', tranwrd("&myfilename",' ', '_'));

run;

%put &myfilename;

(But here I don't think we are assigning a new value to myfilename but rather overwritting the macro itself.)

A final thought is to read about the double and single quotes in SAS.  Here is a good start: http://www.lexjansen.com/pharmasug/2002/proceed/techtech/tt07.pdf

Super User
Super User
Posts: 6,133

Re: Replace blank with a specific character

The reason your %SYSFUNC(TRANSLATE()) call works and your %SYSFUNC(TRANWRD()) call does not is related to differences in how those two functions work.  (And in the fact that you do not use quotes in macro code to indicate that a value is string. In macro code everything is a string.)

TRANSLATE converts individual characters to other individual characters.  TRANWRD replaces strings with other strings.

So when you use %SYSFUNC(TRANSLATE(&A,'_',' ')) you are telling SAS to translate single quote to single quote, space to underscore and single quote to single quote.  So any single quotes that happen to appear in your source string are not changed but the spaces are changed to underscores.  I frequently use this to allow me to specify spaces and other characters that the macro pre-processor would interpret as having special meaning without requiring me to use macro quoting functions like %STR().  It is much easier to type '%' then to type %STR( %%).

Your %SYSFUNC(TRANWRD()) call doesn't work because the TRANWRD function is trying to match the full string of characters.  So when you give it ' ' it is looking for the three character sequence single quote, space, single quote.  Any other space characters that are not surrounded on both sides by single quotes will not match the search criteria.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 14979 views
  • 1 like
  • 3 in conversation