SQL, Macro variable and WHERE clause

KBou — Mon, 13 Jan 2020 16:10:30 GMT

Hi, I'm actually having a problem with a macro variable in a Proc SQL statement. Here's my code for the creation of the variable and the SQL statement. I'll use fictive data, but the essential is there. The problem is when I try to recall the variable in the where clause on the second SQL statement.

I get a whole lot of

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.

edit 1:

But it puts an red x like an error, and my people won't allow to run a program which generates an "error" though it isn't counted as one

Proc SQL noprint;
Connect to oracle ();
Select distinct data into: macro_var separated by " ',' "

from connection to oracle
(select data
from table1
where ( maturitydate > today
)
);

quit;

%put %nrbquote(')&macro_var%nrbquote(');

So here my var outputs 'asdgsd','asdsgsg','affdg'

proc sql;
Connect to oracle();

Create table Ind as

Select *

From connection to oracle

(
Select *

From table2

Where (data in (%nrbquote(')&macro_var%nrbquote(')))

);
Quit;

Thank you !

Re: SQL, Macro variable and WHERE clause

novinosrin — Mon, 13 Jan 2020 16:06:40 GMT

Hi @KBou You can keep it very simple by using QUOTE function without having to use any macro quoting for your case

Example:

Select distinct quote(data) into: macro_var separated by  ','

And you can call the macro reference later like

Where data in (&macro_var)

Re: SQL, Macro variable and WHERE clause

KBou — Mon, 13 Jan 2020 16:22:28 GMT

Ty sir, very appreciated. Didn't knew about the quote function. Wasn't used in any of our codes yet, but certainly will be since i generate every macro variable by SQL.

topic SQL, Macro variable and WHERE clause in New SAS User

SQL, Macro variable and WHERE clause

Re: SQL, Macro variable and WHERE clause

Re: SQL, Macro variable and WHERE clause