BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KBou
Fluorite | Level 6

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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)

 

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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)

 

KBou
Fluorite | Level 6
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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2005 views
  • 0 likes
  • 2 in conversation