Does the prompt manager escape characters?

Question

I am working on a SAS program and I created a prompt which should allow a user to enter a comma separated list of values. The values are then used in a WHERE IN clause. The problem is that the prompt manager appears to be escaping the quotes, somehow, resulting in weird error messages.

To show the error, I've created this sample data set:

DATA Work.Birds;

    LENGTH  Category    $   20
            CommonName  $   27
            ;

    INPUT   Category    $   1-20
            CommonName  $   22-48
            ;

DATALINES;
Ostriches            Common Ostrich
Ostriches            Greater Rhea
Cassowaries and Emus Southern Cassowary
Cassowaries and Emus Dwarf Cassowary
Kiwis                Brown Kiwi
Kiwis                Little Spotted Kiwi
Waterfowls           Horned Screamer
Waterfowls           Magpie-goose
Penguins             Emperor Penguin
Penguins             King Penguin
RUN;

Now, using a %LET statement, I can write a more-or-less-dynamic query for particular birds:

%LET BirdCategories = 'Kiwis', 'Penguins';

%PUT "&BirdCategories.";

PROC SQL NOPRINT;
    CREATE TABLE Work.SelectedBirds AS
    SELECT      *
    FROM        Work.Birds
    WHERE       Category IN ( &BirdCategories. )
    ;
QUIT;

This code prints the following to the log:

"'Kiwis', 'Penguins'"

It also creates a dataset with four records.

Now I try to create a prompt using the prompt manager with the instructions found in the official documentation. The goal is to replace the single %LET line with a prompt without adding any additional code.

So I created a prompt named BirdCategories and set the Number of Values to "Single Value":

I then run the program again (this time commenting out the %LET statement):

The log outputs the following for BirdCategories:

"'Kiwis', 'Waterfowls'"

But I get an error in my SQL statement:

43             PROC SQL NOPRINT;
44                 CREATE TABLE Work.SelectedBirds AS
45                 SELECT      *
46                 FROM        Work.Birds
47                 WHERE       Category IN ( &BirdCategories. )

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

48                 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
49             QUIT;

(I get the same error whether I use PROC SQL or a DATA step with a WHERE IN clause.)

Then I tried inputting Kiwis", "Waterfowls with the following code:

PROC SQL NOPRINT;
    CREATE TABLE Work.SelectedBirds AS
    SELECT      "&BirdCategories."
    FROM        Work.Birds
    ;
QUIT;

which should create two columns, but instead I get this:

If I set the macro variable with a SYMPUT, like so:

DATA _NULL_;    
    CALL SYMPUT( 'BirdCategories', 'Kiwis", "Penguins' );
RUN;

I get two columns as expected:

I can't see it, but the prompt manager must be escaping my quote characters somehow. Any clue what's going on? I've tried my google-fu to no avail.

Answer 1

Try this:

PROC SQL NOPRINT;
    CREATE TABLE Work.SelectedBirds AS
    SELECT      *
    FROM        Work.Birds
    WHERE       Category IN ( %unquote(&BirdCategories.) )
    ;
QUIT;

I think it works as expected from my testing. %UNQUOTE is one of the many handy tricks for dealing with this stuff.