Stored Process Prompts - All Possible Values checkbox

[krusader]

How does "all possible values" work to set the value of the prompt macro variable? I am using prompt values entered by the user to decide an employee ID for example. Does all possible values essentially just remove the filter or does it set some special value that a PROC SQL filter recognizes?

In the macro variable output in the log I see that _ALL_VALUES_ is set as a value. How does SAS interpret this? I am not getting errors but I am also not getting results in my SQL that looks something like this:

Select * From EMPLOYEE

Where EMP_ID = "&employeeid"

-- I select all possible values but I get no result set and no errors

Any help on how to successfully use All Possible Values? I'm creating my stored process in SAS Enterprise Guide 7.11

[Jatin_Jim · Accepted solution]

Hello Krusader.

You can certainly use the below code to get the All Possible values in your output..

In my Case I have created Propmt for State Name so you can make the changes accordingly.

( I will have option to select more the one state or can select _ALL_VALUES_ )

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_STATEVIEW AS
   SELECT DISTINCT t1.STATE
      FROM SASORCL.SAS_BI_VW_SALESVIEW t1
      WHERE %_eg_WhereParam( t1.STATE, state, IN, TYPE=S, IS_EXPLICIT=0 );
QUIT;

Note:  Make sure you are using where clause for "In A List" operator not "=" operator..

Let me know if you need anymore infor for the same.

Regards;

Jim