Hello,
The main goal is to take a data table that is user input & filter a database table for just the values from the user input data table. This occurs multiple times so it would be nice to have a function that can be called and have the filters pass into the function.
test ~ User Input Table
test_two ~ Database Table
It works fine outside of the macro function, but not within.
I was able to fix one of the errors relating to using %BQUOTE, but now it gives me a syntax error:
Error:
304 options mprint; 305 306 DATA test; 307 INPUT x1 $ x2; 308 DATALINES; NOTE: The data set WORK.TEST has 6 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 315 ; 316 RUN; 317 318 PROC SQL; 319 SELECT DISTINCT quote(trim(x1)) 320 INTO :x_list separated BY "," 321 FROM test 322 ; WARNING: No output destinations active. 323 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 324 325 DATA test_two; 326 INPUT x1 $ x2; 327 DATALINES; NOTE: The data set WORK.TEST_TWO has 6 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 334 ; 335 RUN; 336 337 %PUT &x_list; "A","B" 338 339 340 PROC SQL; 341 CREATE TABLE query_result_no_macro AS 342 SELECT * 343 FROM test_two 344 WHERE x1 IN (&x_list) 345 ; NOTE: Table WORK.QUERY_RESULT_NO_MACRO created, with 4 rows and 2 columns. 346 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 347 348 %MACRO query_test(x1_list=); 349 PROC SQL; 350 CREATE TABLE query_result_macro AS 351 SELECT * 352 FROM test_two 353 WHERE x1 IN (&x1_list) 354 ; 355 QUIT; 356 %MEND query_test; 357 358 %query_test(x1_list=%BQUOTE(&x_list)); MPRINT(QUERY_TEST): PROC SQL; MPRINT(QUERY_TEST): CREATE TABLE query_result_macro AS SELECT * FROM test_two WHERE x1 IN ("A","B") ; MPRINT(QUERY_TEST): QUIT; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the macro variable "X1_LIST". 1 "A","B" - 22 - 200 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, (, -, SELECT. ERROR 200-322: The symbol is not recognized and will be ignored.
Code:
DATA test; INPUT x1 $ x2; DATALINES; A 1 A 2 A 3 B 1 B 2 B 3 ; RUN; PROC SQL; SELECT DISTINCT quote(trim(x1)) INTO :x_list separated BY "," FROM test ; QUIT; DATA test_two; INPUT x1 $ x2; DATALINES; A 1 A 2 B 3 B 4 C 5 C 6 ; RUN; %PUT &x_list; PROC SQL; CREATE TABLE query_result_no_macro AS SELECT * FROM test_two WHERE x1 IN (&x_list) ; QUIT; %MACRO query_test(x1_list=); PROC SQL; CREATE TABLE query_result_macro AS SELECT * FROM test_two WHERE x1 IN (&x1_list) ; QUIT; %MEND query_test; %query_test(x1_list=%BQUOTE(&x_list));
Hello @narnia649,
The easiest fix is to replace the comma in the INTO clause with a blank:
INTO :x_list separated BY " "
The IN operator doesn't require commas between the values.Then you don't need %BQUOTE to mask the comma in the macro call and %UNQUOTE to unmask it in the macro (which is what you omitted).
@narnia649 wrote:
Hello,
The main goal is to take a data table that is user input & filter a database table for just the values from the user input data table. This occurs multiple times so it would be nice to have a function that can be called and have the filters pass into the function.
test ~ User Input Table
test_two ~ Database Table
It works fine outside of the macro function, but not within.
I was able to fix one of the errors relating to using %BQUOTE, but now it gives me a syntax error:
Error:
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, (, -, SELECT. ERROR 200-322: The symbol is not recognized and will be ignored.
Please turn on the macro debugging option
options mprint;
and then run your code again.
Please show us the ENTIRE log for this macro, not selected parts of the log. For everyone's benefit, please note that in the future, we do not want to see parts of the log separated from the code that generated it. We need the ENTIRE log for the PROC or DATA step or macro that has the error.
Also, no macro or macro variables needed here.
PROC SQL;
CREATE TABLE query_result_no_macro AS
SELECT *
FROM test_two
WHERE x1 IN (select distinct x1 from test)
;
QUIT;
Hello @narnia649,
The easiest fix is to replace the comma in the INTO clause with a blank:
INTO :x_list separated BY " "
The IN operator doesn't require commas between the values.Then you don't need %BQUOTE to mask the comma in the macro call and %UNQUOTE to unmask it in the macro (which is what you omitted).
This works
%query_test(x1_list=%QUOTE(&x_list))
but this does not work
%query_test(x1_list=%BQUOTE(&x_list))
I leave it to others to explain why %QUOTE works but %BQUOTE does not work.
Hi,
This looks like a known problem where SAS doesn't always automatically remove the special characters used to implement the macro quoting (%bquote).
You could change your macro to explicitly unquote the value:
%MACRO query_test(x1_list=);
PROC SQL;
CREATE TABLE query_result_macro AS
SELECT *
FROM test_two
WHERE x1 IN (%unquote(&x1_list))
;
QUIT;
%MEND query_test;
%query_test(x1_list=%BQUOTE(&x_list));
Often it's easier to avoid introducing macro quoting. In this case, you're using quoting to mask the comma. But the IN operator doesn't require the comma, you can use a space delimited list. So the below would work without any macro quoting:
PROC SQL noprint;
SELECT DISTINCT quote(trim(x1))
INTO :x_list separated BY " " /*space delimited list rather than comma delimited*/
FROM test
;
QUIT;
%MACRO query_test(x1_list=);
PROC SQL ;
CREATE TABLE query_result_macro AS
SELECT *
FROM test_two
WHERE x1 IN (&x1_list)
;
QUIT;
%MEND query_test;
%query_test(x1_list=&x_list)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: