Developers

A forum for collaboration, Q&A, and knowledge sharing on SAS and open source integration
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
krusader
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jatin_Jim
Obsidian | Level 7

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

 

View solution in original post

1 REPLY 1
Jatin_Jim
Obsidian | Level 7

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

 

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 2289 views
  • 1 like
  • 2 in conversation