How to pass in a macro list into a macro function & use WHERE with PROC SQL?

[narnia649]

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));

[PaigeMiller]

---

@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.

[narnia649]

Done

[PaigeMiller]

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;

[FreelanceReinh · Accepted solution]

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).

[PaigeMiller]

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.

[Quentin]

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)