topic Re: Replace blank with a specific character in SAS Data Science

Replace blank with a specific character

garag — Wed, 06 Feb 2013 11:46:29 GMT

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

Re: Replace blank with a specific character

Tom — Wed, 06 Feb 2013 11:57:54 GMT

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( )));

Re: Replace blank with a specific character

garag — Wed, 06 Feb 2013 12:13:56 GMT

The compress seems works better that tranword:-)

Re: Replace blank with a specific character

jaredp — Wed, 06 Feb 2013 14:28:15 GMT

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

Re: Replace blank with a specific character

Tom — Thu, 07 Feb 2013 05:28:40 GMT

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.