BookmarkSubscribeRSS Feed
mrdlau
Obsidian | Level 7

I asked this question in the "SAS programming forum" and I was given suggestion to use %let, so I think it might be better to ask here, since I'm actually using SAS EG.

 

I have a proc sql query as follows:

proc sql;
select * from table 
where ID in %Bquote('&ID');
quit;

I want it to do the equivilant as

proc sql;
select * from table 
where ID in ('2','3','5';
quit;

I have my prompt set up to accept multiple values but no matter what, it always just filters my results based on the first value ('2').

 

I want the user to pick ID in the prompts, not in the code itself using the %let

 

 

2 REPLIES 2
TomKari
Onyx | Level 15

Easy, but a bit complicated.

 

First of all, make sure that your program is linked to the prompt. If you right-click on Program and select Properties, use the Prompts tab to link the prompt to your program.

 

A good tip is that you can see what macro variables are being set up by the prompt manager by including this line in your program:

%put _all_; (It will display all of the macro variables in existence.)

 

Now in terms of your situation, the SAS prompt manager will set up multiple macro variables. The important ones are (assuming your prompt name is ID):
ID_COUNT - the count of prompt responses
ID - the first prompt response
ID2 through ID5 - the remaining prompt responses (assuming you've entered five prompt responses).

 

So you need a small macro loop to set up your SQL where clause. The following program demonstrates it using SASHELP.SHOES.

 

%macro UseID;
	%do i = 1 %to &ID_COUNT;
		%if &i = 1 %then
			%let IDString = "&ID";
		%else %let IDString = &IDString, "&&ID&i";
	%end;

	proc sql;
		select * from sashelp.shoes
			where Region in (&IDString);
	quit;

%mend;

%UseID

 

Damo
SAS Employee

Hi @mrdlau

 

With SAS Enterprise Guide (EG), you may want to start with the Query Builder.

For the below example, I'm using the SASHELP.CLASS dataset with EG7.1.

 

First, I have to create the VALUE prompt, this is what the Prompt Type and Values tab looks like.

prompt.PNG

I'm using a static list but if you want to use a dynamic one, remember your table has to be registered with the metadata. So it's not possible if you use a local server, from an EG perspective.

 

Then moving on to the Query Builder, based on SASHELP.CLASS.

After you moved your variables, from the Filter Data tab, create a new filter (far right icon).

FilterData.PNG

 

Select the Basic Filter one, pick Name (in my case) and:

  - In a list is the operator

  - Check Generate filter for a prompt value

  - Select the down arrow (on the right) next to Value.

  - From the Prompts tab, select &value (the prompt we created earlier)

Filter.PNG

 

Running the Query Builder will allow the user to pick multiple values from the list.

If I select Jane and John from the prompt, this is the output dataset I get.Selection.PNG

 

Output.PNG

 

 

You can directly use the clause with a Proc Print for instance:

proc print data=sashelp.class;
WHERE %_eg_WhereParam(Name, value, IN, TYPE=S, IS_EXPLICIT=0 );
run;

 

Hope that helps.


Cheers,
Damo

 

 

 

 

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2609 views
  • 1 like
  • 3 in conversation