05-02-2017 05:57 PM
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
05-04-2017 05:43 AM
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_ )
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 );
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.