SAS - Replacing substrings in a macro variable containing semicolons

Question

I need to be able to copy paste a long SQL script that contains variables back and forth between Aginity Workbench and SAS. To make this easier, I've been storing the SQL query in a macro variable like this: (take note of the Netezza style variables)

%let myQuery = %str(
                      DROP TABLE this;
                      SELECT *
                      INTO SomeTable
                      FROM OtherTable
                      WHERE field = ${myVariable};
                      UPDATE TABLE foo
                      SET x = 1
                      WHERE
                          field = ${anotherVariable};
                    );

When my SAS program runs, I need it to replace the ${netezzaVariables} with text from other macro variables that are determined earlier in the process flow. So far, I've not been able to successfully replace text within this macro variable and I am suspicious that the semicolons are causing issues.

Here's what I'm attempting to do below:

%let formattedText = %sysfunc(tranwrd(&myQuery,'${myVariable}','replacementText'));

The log for that shows:

NOTE: Line generated by the macro function "SYSFUNC".
DROP TABLE this;
! SELECT <the rest of the query is printed to console here>

I didn't go on writing the rest of the log above, because the error is on the word SELECT, with a red line underneath it. Just below this red line is the text:

ERROR 180-322: Statement is not valid or it is used out of proper order.

In fact, if I go on scrolling through the log, every first set of characters following a semicolon in the query is underlined with that same exact error code. This leads me to believe that SAS picked up the semicolon before that SELECT, used it to terminate what I was doing, and now thinks the text following this semicolon is out in the open code.

In conclusion, I need to know how to replace substrings within a macro variable who's value is a large string containing semicolons.

Thanks in advance!

Answer 1

Not the most elegant of solutions but it does the job:

%let myQuery = %str(
                      DROP TABLE this;
                      SELECT *
                      INTO SomeTable
                      FROM OtherTable
                      WHERE field = ${myVariable};
                      UPDATE TABLE foo
                      SET x = 1
                      WHERE
                          field = ${anotherVariable};
                    );
data _null_;
call symput('formattedtext',tranwrd("%quote(%superq(myquery))","${myVariable}","replacementText"));
run;

%put %superq(formattedText);

There's probably a way to do it using only macro functions but I couldn't get it to work.

For your particular example, the call symput could have been simplified to

call symput('formattedtext',tranwrd("&myquery","${myVariable}","replacementText"));

but this would fail if your query contained double quotes whereas the way I wrote it above supports that.

Answer 2

Joshua:

A general purpose resolver is useful if your 'templated' expression has many parameters. Note: a template is different than a parameterized query, and potentially more dangerous.

Without a resolver you will need to code a TRANWRD for each parameter.

Consider this macro that assumes a template contains parameters that are specified by ${macro-var} and parameters are replaced with the macro-var value. Also presume there are no parameters that start with underscore (_) which could collide with the macros internal variables.

%macro resolver(_template);
  %local _result;
  %local _tokenRx;
  %local _start _stop _position _length _token _macrovar _guard;

  %let _tokenRx = %sysfunc(prxparse(m/\${([^}]+)}/));
/*%put &=_tokenRx;*/

  %let _guard = 0;
  %let _start = 1;
  %let _stop = %length(&_template);
  %let _position = 0;
  %let _length = 0;

  %let _result = &_template;

  %syscall prxnext(_tokenRx, _start, _stop, _template, _position, _length);

  %do %while (&_position > 0);
/*  %put &=_start &=_stop &=_position &=_length; */
    %let _token = %qsubstr(&_template,&_position,&_length);
    %let _macrovar = %substr(&_token,3,%eval(%length(&_token)-3));

/*
    %put &=_token;
    %put &=_macrovar;
*/
    %if %symexist(&_macrovar) %then %do;
      %let _result = %qsysfunc(tranwrd(&_result,&_token,&&&_macrovar));
    %end;

    %syscall prxnext(_tokenRx, _start, _stop, _template, _position, _length);

    %let _guard = %eval (&_guard+1);
    %if &_guard > 1000 %then %let _position = 0;
  %end;

  %syscall prxfree(_tokenRx);

  %superq(_result)
%mend;

Here is the resolver applied to your templated SQL query (adjusted to Proc SQL).

%let myQuery = %str(

    DROP TABLE this
;
    INSERT INTO SomeTable /* sas insert syntax */
    SELECT * FROM OtherTable
    WHERE ${field} = ${target}
;
    UPDATE foo
    SET x = 1
    WHERE
        field = ${anotherVariable}
;
);


%let field = name;
%let target = 'Jane';
%let myVariable = XYZ;
%let anotherVariable = 'John';

%put %resolver (%superq(myQuery));

proc sql;
  create table this (id int);
  create table SomeTable like sashelp.class;
  create table OtherTable as select * from sashelp.class;
  create table foo as select name as field, 0 as x from sashelp.class;

  %unquote(%resolver(%superq(myQUery)))
quit;

Answer 3

You don't need to use quotes when using string functions with %sysfunc(). In this case %qsysfunc is what you're looking for, I think:

%let myQuery = %str(
                      DROP TABLE this;
                      SELECT *
                      INTO SomeTable
                      FROM OtherTable
                      WHERE field = ${myVariable};
                      UPDATE TABLE foo
                      SET x = 1
                      WHERE
                          field = ${anotherVariable};
                    );
%put &myQuery;

%let formattedText = %qsysfunc(tranwrd(&myQuery,${myVariable},replacementText));
%put &formattedText;