prompt containing a list of strings to filter while using a where condition

[EBB]

Hi there,

I'm using SAS EG version 7.1 

I need to create a prompt where the user can input around 600 string values that would be used then in a where condition to filter a table.I can not ask the user to select those values from a list because it would be really inconvenient finding those 600 in a list containing millions of records. The user has a list of these string values and the idea is that they are able to copy paste those in the prompt window. The Prompt would be used in a Stored Procedure that will be running from SAS VA. 

Here is one example that represents what I need to do:

%let list_ID="A","B";
data raw;
input id $ vis a b c d e;
cards;
A 1 1 6 7 8 9
A 2 . 2 3 4 8
A 3 2 3 . . 5
B 1 4 5 . 5 .
B 3 7 5 . . .
B 4 6 7 3 5 3
C 2 9 4 5 7 9
C 3 . . . . .
C 4 7 . . . .
D 1 9 3 9 4 8
D 2 7 . . . .
D 3 . 6 . . 4
E 1 6 5 7 4 6
E 2 . 2 4 . 6
E 3 8 . 5 5 .
E 4 . 8 6 5 4
;
run;
data select;
	set raw;
	where id in (&List_ID.);
run;

Now, when I comment out the %let List_ID and try to use a prompt with the following configuration (see first picture) and input "A","B"  (see second picture) I get this error:

ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, -.

ERROR 76-322: Syntax error, statement will be ignored.

Does someone know how to create such functionality with a prompt??

Thanks!!!

[RW9]

A prompt (and macro) is really not the place to be asking a user to input 600 items.  There will end up being mistakes in it.  Get your users to provide a CSV file with the relevant paramters, then your prompt can be used to enter that filename.  Then your program loads the CSV and filters based on the CSV:

proc sql;
  create table want as
  select * 
  from   have
  where xyz not in (select xyz from csv_dataset);
quit;

If I was the user and was told to type in 600 paratmers I wouldn't be using it.

[TomKari · Accepted solution]

This may help get you on your way.

Tom

https://communities.sas.com/t5/SAS-Enterprise-Guide/How-to-create-a-prompt-to-enter-a-list-of-values...

[EBB]

Thanks! I read it and what is working for me is the following:

1- A multitext promp. The user should copy paste the list of strings in one line without "" and only with a space in between every string. Example: String1 String2 String3

2-The following let statement to process the content of the prompt

%let PromptText=%sysfunc(compbl(&PromptText));

3-And the following where condition

where barcode in (
"%sysfunc(tranwrd(&prompttext,%str( )," "))"
);