topic Re: prompt containing a list of strings to filter while using a where condition in SAS Enterprise Guide

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

EBB — Thu, 02 Aug 2018 14:03:20 GMT

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!!!

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

RW9 — Thu, 02 Aug 2018 14:18:05 GMT

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.

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

TomKari — Thu, 02 Aug 2018 14:58:21 GMT

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/m-p/477044#M30977

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

EBB — Fri, 03 Aug 2018 06:26:21 GMT

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( )," "))"
);