Problem of macro var in SQL Execute ( )

[6656]

How to solve the problem of using macro varible in Proc SQL / Execute(set ....where .... ) instead of a hardcode value. There is no problem if using a hardcode (='ABC').
If using sigle quote (='&macv'), no update happen and no erro in log; Double quote will cause error (Error Code: -207 Invalid column name 'ABC').

e.g.
%let macv=ABC
proc sql;
connect to sybase(user=username password=sybpass ...... );
execute (update sybase_table
set column1=1
where column6='&macv')
by sybase;
disconnect from sybase;
quit;

Thanks in advance. Merry X'mas!

 

[klaz2002]

Try setting the macro var with the single quotes.
ex. %let mvar = 'ABC';
then in your SQL execute statement you can now say
where..... = "&mvar";
when the sas macro compiler resolves this macro var it will now be ....='ABC'. and this you say works. I hope this helps.
Have a merry xmas!!!

 

[6656]

Hi Klaz2002,

The method to set macro value with single quote, then passed it in double quote to Execute (update...) statement and it was till treated as column name.

The solution I used add one more Execute statement to turn off set_identifier option.
e.g. execute (set quoted_identifier off) by sybase;
execute (update............) by sybase;

 

[Queryman]

You could also try the following

%NBRQUOTE(argument) which masks from intrepretation during execution all special characters and mnemonic operators, including ampersands and percent signs, in th eresolved value of the argument.

%NRSTR(argument) which masks from intrepretation by the macro processor during macro compilation all special characters and mnemonic operators, including ampersands and percent signs, in the resolved value of the argument.

%SUPERQ(macro-variable) which masks from intrepretation by the macro processor at execution all special characters and mnemonic operators, including ampersands and percent signs, in the value of the macro-variable.

QueryMan