Where condition as a Stored process argument

gamotte — Wed, 03 Oct 2012 15:02:28 GMT

Hi,

I have a stored process (SP) which contains the following proc sql

proc sql;

SELECT NAME

FROM a.TABLE

WHERE NAME NOT IN (&cond);

quit;

&cond is dynamically determined by the calling SP and passed

through the URL :

SASStoredProcess/do?_program=/BIP Tree/ReportStudio/StoredProcesses/my_sp&cond="NAME1" "NAME2"

The above sql procedure is not executed because of a syntax error

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, -.

However the log indicates that the macrovariable cond generates "NAME1" "NAME2" as wished.

Moreover, when I copy/paste the resolved proc sql displayed in the log in SAS and execute it, it works.

proc sql;

SELECT NAME FROM a.TABLE WHERE NAME NOT IN ("NAME1" "NAME2");

quit;

How can I get this proc to work ?

Thanks

Re: Where condition as a Stored process argument

Amir — Wed, 03 Oct 2012 16:30:33 GMT

Hi,

Just a guess, is it worth trying to use the %unquote() function, e.g.:

WHERE NAME NOT IN (%unquote(&cond))

Regards,

Amir.

gamotte — Thu, 04 Oct 2012 07:59:35 GMT

Thanks ! You guessed right. It works.