Multiple-value prompts except eg_param function.

[Chris999]

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!

[jimbarbour]

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

[jimbarbour]

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