Search code examples
sassas-macroproc-sql

within proc sql replace string of a macro variable

Within Proc SQL, I'd like to use a macro variable &condition3, but I'd like to replace the string “t1” with “t6” in the macro variable. How can I make the following formula work: translate(&condition3,'T6','T1')?

By the way, &condition3 resolves to: and t1.store in ('1234')

The full query:

Proc sql;
  Create table xxx as
    Select....
      From ...
      Where condition1
        And condition 2
        &condition3
Solution

  • You would want to use the %SYSFUNC macro statement.

    %sysfunc(tranwrd(&condition3,t1,t6))

    You can use that directly , ie without assigning it to another macro variable. The fact that you're using this in PROC SQL is irrelevant, as you are just creating text to be passed to (anything).

    That said, you may want to consider using a macro here instead of a macro variable; this would be more logical if it was

    %macro condition3(table=);
  and &table..store in ('1234')
%mend condition3;

    then you can use the macro in SQL just like the macro variable.

    where condition1
  and condition2
  %condition3(table=t6)