SAS Proc SQL Macro variables in Where clause

Question

I am trying to write a program in SAS using the Prompt manager variable within a PROC SQL statement. It isnt working for me and keeps saying that the symbol is not recognized and will be ignored.

Heres a sample of my code...

LIBNAME mylib ORACLE  SCHEMA = 'myschema'  PATH = prd  USER = 'admin'    PASSWORD = 'admin12';

PROC SQL;
   SELECT DISTINCT TEST_ID, COUNT(*), TERM                       
   FROM mylib.testtable 
   WHERE RELEASE = 'PRETEST' 
     AND TEST_ID IN (&TEST) /* 'MATH', 'READING', 'SCIENCE' */
     AND TERM = 'SPRING'  
   GROUP BY TEST_ID, TERM 
   ORDER BY TEST_ID, TERM;
QUIT;

And here is the problem in the log:

40          WHERE RELEASE = 'PRETEST'
41              AND TEST_ID IN (&TEST) /* 'MATH', 'READING', 'SCIENCE' */
NOTE: Line generated by the macro variable "TEST".
41         'MATH', 'READING', 'SCIENCE'
       _
       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.

My prompt variable is &TEST and should hold the list of tests to take but it dosent.

Answer 1

The issue here is one related to macro quoting. It's apparent that the token is enclosed in macro quotes (similar to %nrstr basically) for some reason, which cause it to work slightly differently than a normal %let. %unquote fixes it. I suspect there is also a better way to define the prompt to cause this not to occur, but I'm not completely sure - maybe one of the more experienced EG folks can answer.

(Define a TEXT - SINGLE VALUE prompt called type and attach it to a program containing this:)

proc sql;
  select name, age
    from sashelp.class;
    where name in (%unquote(&type.))
  ;
quit;

Answer 2

OK, I found a solution to my dilemma. As Joe stated, its a macro quoting issue, but it was also an array issue too. Both are solved by wrapping the variable in double quotes and some rudimentary replication.

Long story short,
the way SAS handles arrays and multiple values caused the first value to show only the first value so I had to assign multiple nullable values.

Working product below:

SELECT DISTINCT 
   TEST_ID, COUNT(*),  TERM
FROM mylib.&TABLE 

WHERE RELEASE IN ("&RELEASE", "&RELEASE1", "&RELEASE2", "&RELEASE3") 
  AND TEST_ID IN ("&TEST", "&TEST1", "&TEST2", "&TEST3", "&TEST4", "&TEST5") 
  AND TERM IN ("&TERM", "&TERM1", "&TERM2", "&TERM3", "&TERM4") 

GROUP BY TEST_ID, TERM
ORDER BY TEST_ID, TERM;  

Adding the &Release, &Release1, ect. allows multiple values to be captured should there be an array of values, otherwise it would accept the first value default the extra values to null and throw a warning.

This was sufficient enough to be able to provide a list of options to the user and allow them to run using one or more parameter in each field