How to correctly use multi-value prompt with a 'Contains' filter?

[sass_numero_uno]

I have a multi-valued prompt, with 4 drop down options:

Job title 

1) Plumber

2) Electrician

3) Carpenter 

4) Plaster

When I use this prompt with a 'Contains' filter in a query builder, I constantly get a syntax error and I cant figure out why. 

It will work with a 'In a list' filter, but I don't have high quality data and can't guarantee that the prompt values will match exactly, hence the need for a 'Contains' filter. 

Does anyone know how to use the method I'm looking at?

A final point, does anyone know why filters and prompts require the "Match Case" option to be enabled? Again, the query builder throws a syntax error if it is unticked.

[Patrick]

---

Set it up as shown below as else EG will wrap single quotes around the prompt (the macro variable) and it doesn't get resolved.

[sass_numero_uno]

That is exactly how my 'Contains' filter looks.

When I run the node, it runs correctly and the prompt window appears fine.

When I select only 1 variable, such as "Plumber" it runs fine and selects those with a plumber job, and outputs the data fine.

Its only when I select more than 1 variable in the prompt, e.g. "Plumber" and "Electrician" from the prompt's available values is when I get the syntax error.

Is the prompt not able to separate multiple variables to search for the "Contains" operator?

[Patrick · Accepted solution]

I've been poking around a bit with this multi-value case and it appears that the ootb EG macro %_eg_WhereParam() doesn't cover this case for a contains operator.

The prompt type you're using generates actually a set of separate macros. Below an example for a prompt with name MyPrompt:

/* macro variables created for MyPrompt with 3 values selected */
%let MYPROMPT0 =3;
%let MYPROMPT1 =Plumber;
%let MYPROMPT2 =Electrician;
%let MYPROMPT3 =Carpenter;

If you select operator in a list then macro %_eg_WhereParam() creates valid syntax. But it doesn't work for operator contains and I couldn't figure out "a hack" to get around this using the query builder.

You could of course write your own SAS code and then implement some macro logic which builds correct syntax from such a prompt.

Not sure if one should call this a defect or not. May be consider raising a SAS Tech Support track for this one.

What macro %_eg_WhereParam() does is to create a comma delimited list as required for an IN operator. It would need to create a list of separate OR conditions per prompt values for a contains operator to work.