Your SAS programs, embedded in web apps and elsewhere

Where condition as a Stored process argument

Reply
Regular Contributor
Posts: 233

Where condition as a Stored process argument

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

Super Contributor
Posts: 282

Re: Where condition as a Stored process argument

Hi,

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

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

Regards,

Amir.

Regular Contributor
Posts: 233

Re: Where condition as a Stored process argument

Thanks ! You guessed right. It works.

Ask a Question
Discussion stats
  • 2 replies
  • 343 views
  • 0 likes
  • 2 in conversation