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
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.
SAS INNOVATE 2024
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.