SAS EG prompts only returning values based on 1 item

[mrdlau]

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

[TomKari]

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]

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.

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).

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)

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.

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