topic Re: TRANSTR works in a datastep but does not inside %SYSFUNC() in SAS Programming

TRANSTR works in a datastep but does not inside %SYSFUNC()

SPR — Sun, 14 Jun 2020 20:05:49 GMT

I have the following code that works fine. What I need is to replace commas in a=A.B,C string with asterisks to get s="A" "B" "C" string.

The data step version of the code works fine:

%let a=A,B,C;
data a; s='"'||TRANSTRN("&a",",",'" "')||'"'; run;

/* dataset a: */
/* s */
/*"A" "B" "C" */

However, using %SYSFUNC(TRANSTR(...)) fails or produces wrong result:

%let s=%SYSFUNC(TRANSTRN(&a,",",'" "')); %put s=&s;
/*ERROR: The function TRANSTRN referenced by the %SYSFUNC or %QSYSFUNC macro function has too many arguments.*/

%let s=%SYSFUNC(TRANSTRN("&a",",",'" "')); %put s=&s;
/*s="A,B,C"*/

%let s=%QSYSFUNC(TRANSTRN(&a,",",'" "')); %put s=&s;
/*ERROR: The function TRANSTRN referenced by the %SYSFUNC or %QSYSFUNC macro function has too many arguments.*/
Any help?

Re: TRANSTR works in a datastep but does not inside %SYSFUNC()

Tom — Sun, 14 Jun 2020 20:37:12 GMT

Why are you searching for quotes in the value of the macro variable A? There aren't any quotes in A. Your data step did not search for quotes.

So in the data step you did this:

s='"'||TRANSTRN("&a",",",'" "')||'"';

Which is changing commas to a space with double quotes around it.

But in the %SYSFUNC() call you did this:

%let s=%SYSFUNC(TRANSTRN(&a,",",'" "'));

Which is changing a comma with double quotes around it to a space with double quotes around it that also has single quotes around it.

You probably meant to do this instead.

%let s=%SYSFUNC(TRANSTRN(&a,%str(,)," "));

You also need to add macro quoting to A because it also has commas in it.

Note there is no need to use TRANSTRN instead of plain old TRANWRD because you aren't converting the values to null strings.

%let s="%SYSFUNC(TRANWRD(%superq(a),%str(,)," "))";

13    %let a=One,Two,Three;
14    %let s="%SYSFUNC(TRANWRD(%superq(a),%str(,)," "))";
15    %put &=a &=s;
A=One,Two,Three S="One" "Two" "Three"

Re: TRANSTR works in a datastep but does not inside %SYSFUNC()

Patrick — Sun, 14 Jun 2020 20:33:27 GMT

SAS Macro level is purely textual and though you don't need to quote text. But then because you're dealing with commas which also have a meaning on macro level you do need to macro quote these strings. Alternatively use a data _null_ step to create the macro variable. That's often easier to formulate and debug.

%let a=A,B,C;

/* option 1 */
%let s="%sysfunc(transtrn(%nrbquote(&a),%nrbquote(,)," "))";
%put &=s;

/* option 2 */
data _null_;
  s=cats('"',TRANSTRN("&a",",",'" "'),'"');
  call symputx('s',s);
  stop;
run;
%put &=s;

Re: TRANSTR works in a datastep but does not inside %SYSFUNC()

SPR — Sun, 14 Jun 2020 20:44:30 GMT

Thanks for the advice, however, I've got the following result with your code:

ERROR: The function TRANSTRN referenced by the %SYSFUNC or %QSYSFUNC macro function has too many arguments.
27 %let s=%SYSFUNC(TRANSTRN(&a,%str(,)," ")); %put s=&s;

/* s= */

Re: TRANSTR works in a datastep but does not inside %SYSFUNC()

SPR — Sun, 14 Jun 2020 20:46:04 GMT

Thanks for the solution. It works fine.

Re: TRANSTR works in a datastep but does not inside %SYSFUNC()

SPR — Sun, 14 Jun 2020 21:01:39 GMT

Using clues from both replies it seems to I found a shorter code:

%let a=A,B,C:

%let s=%SYSFUNC(TRANSTRN("&a",%str(,)," ")); %put s=&s;

/*s="A" "B" "C"*/