topic Re: Multiple-value prompts except eg_param function. in SAS Enterprise Guide

Multiple-value prompts except eg_param function.

Chris999 — Fri, 18 Sep 2020 00:29:58 GMT

Hello guys,

In the SAS Enterprise Guide, a prompt defined that allows the user to select multiple values. Now in my PROC SQL, the prompt value returned is of a single value and not off of those I did type in like I expected. This is the code that I have:

%macro promptList(

promptVar = v_num,
quotedList = yes
listName = v_num);

%local i list;

%do i = 1 %to &&&promptVar._COUNT.;
%if &i. eq 1 %then
%if &quotedList eq yes %then
%let list = %quote(%'&&&promptVar.%');
%else
%let list = &&&promptVar.;
%else

%if &quotedList eq yes %then
%let list = &&&listName., %quote(%'&&&promptVar&i.%');
%else
%let list = &&&listName., &&&promptVar&i.;
%end;
%let list = %unquote(&&&listName.);
&list
%mend promptList;

PROC SQL;
CREATE TABLE Work.Payroll_History AS
SELECT t1.V_NUMBER FROM HRS_PAYROLL t1
WHERE t1.V_NUMBER in (%promptList(v_num));
QUIT;

It pops up 2 errors:
ERROR: More positional parameters found than defined.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value.

Anyone can help me with this? Thank you so much!

Re: Multiple-value prompts except eg_param function.

jimbarbour — Thu, 24 Sep 2020 03:16:58 GMT

Well, for one thing, you're missing a comma between the 2nd and 3rd parameters in your macro definition.

Also, it's super helpful if you use the Code icon whenever you include sample code otherwise the code loses all formatting and is tough to follow.

Lastly, I'm not clear on what you're trying to do. Can you include a screen shot of the options in the prompt? What are the choices? And what are you trying to do? Sorry; I'm just not following you.

Jim

Re: Multiple-value prompts except eg_param function.

jimbarbour — Thu, 24 Sep 2020 05:58:08 GMT

I'm still not 100% sure what you're trying to do. However, here's an example of taking values from a multi-value prompt (see below) and using them in an SQL statement, which I think is basically what you were trying to do. I don't have access to your tables, so I'm just using SAShelp.Cars.

One of the things I think may have been throwing you off was the use of a triple ampersand. A triple ampersand is used when you want to have the name of the macro variable be determined dynamically. I'm not sure that's really our use case here. A multi-value prompt is stored in a macro array. The most typical notation for accessing the values of a macro array is a double ampersand followed by a macro subscript: &&var&i.

The lame thing (in my opinion) about how SAS EG does multi-value prompts is that if there's only 1 occurrence in the array, then the numeric suffix is dropped -- meaning that I have to write special code if the COUNT macro variable is 1. Poor design in my opinion. So, if my code looks a little weird there, that's why.

Note: I have a macro value defined in my autoexec.sas named sQuote which is a single quote.

%MACRO	PromptList;
	%LOCAL	i;
	%IF	%BQUOTE(&Model_List_Count)	=	1	%THEN
		%DO;
			&sQuote&Model_List&sQuote
		%END;
	%ELSE
		%DO	i		=	1	%TO	&Model_List_Count;
			%IF	&i	<	&Model_List_Count	%THEN
				%DO;
					&sQuote&&Model_List&i&sQuote,
				%END;
			%ELSE
				%DO;
					&sQuote&&Model_List&i&sQuote 
				%END;
		%END;
%MEND	PromptList;

PROC SQL;
	CREATE TABLE Work.Cars AS
		SELECT	Make
				,Model
				,MSRP
			FROM	SAShelp.Cars
			WHERE Make IN (%UNQUOTE(%PromptList))
			ORDER	BY	Make, Model
			;
QUIT;

Here's where I'm selecting a couple of Makes of automobiles in my EG prompt pop-up:

And finally, here are the results of my SQL query after my PromptList macro has processed the values from the EG prompts:

Jim

Re: Multiple-value prompts except eg_param function.

DLehwidi — Mon, 08 Sep 2025 07:48:33 GMT

Good day.
Did you finally get it right?
I also need help with the same problem, my prompt only returns the first value or name I entered on my multiple values field

Re: Multiple-value prompts except eg_param function.

Kurt_Bremser — Mon, 08 Sep 2025 08:30:00 GMT

@Chris999 has not been here since 2020.

Please post your question in a new thread, and supply the EG prompt definition and the code you use to parse the user input.

Re: Multiple-value prompts except eg_param function.

BillSawyer — Thu, 11 Sep 2025 17:40:12 GMT

Hi Chris999,

Refer to my post from just a few minutes ago:

https://communities.sas.com/t5/SAS-Enterprise-Guide/SAS-Enterprise-Guide-multiple-values-in-Prompt/m-p/974391#M46156

It works for both types of prompts (entering multiple values, or selecting multiple values from a list).

Regards,

Bill

Re: Multiple-value prompts except eg_param function.

Quentin — Thu, 11 Sep 2025 18:24:57 GMT

There are already several answers in the thread you started Monday:

https://communities.sas.com/t5/SAS-Enterprise-Guide/SAS-Enterprise-Guide-multiple-values-in-Prompt/m-p/974391#M46156

Please review, and respond in that discussion thread if you have followup questions.

Re: Multiple-value prompts except eg_param function.

DLehwidi — Wed, 22 Oct 2025 12:11:25 GMT

This seem so be a great option to go with but I wish i could get more clarity on how you went about it. especial the "sQuote" , "%BQUOTE" part

Re: Multiple-value prompts except eg_param function.

DLehwidi — Wed, 22 Oct 2025 12:12:52 GMT

Ohh and another problem is that I don't have a list of names and I am fussy searching through a list of columns.