SAS macro resolution in Stored Process behaving inconsistently based on prompt values selected

[chawkins]

I'll admit that I'm a little rusty on my use of the macro facility, so it's entirely possible that I'm missing something fundamental here, but here's the scenario - I'm trying to generate a dynamic where clause in a proc sql statement based on the values selected from a multiple values prompt in a stored process, including the "all possible values" option. In this particular case they are values based on a geographical hierarchy.

If the user selects "all possible values" it moves on. If not, depending on if one or more values is selected, it adds an "and" phrase to the where clause with the appropriate in operator statement. Here's the syntax I went with at first:

%if  &designationregion. = _ALL_VALUES_ %then %do;%end;

%else %do;

and d.designationregion in (

%if &designationregion_count. = 1 %then "&designationregion.";

%else %do i=1 %to &designationregion_count.;

"&&designationregion&i."

%end;

)

%end;

Everything was working splendidly until I happened to choose a value for the prompt with the word "and" in it. For example

Mexico and N Central America.

If this is either the only value or the first in a series of values selected the SP errors out with this:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &designationdivision. = _ALL_VALUES_

ERROR: The macro SPTEST will stop executing.

So if values selected are like so:

Mexico and N Central America
Brazil

it gets fussy. But if values are like so:

Brazil
Mexico and N Central America

everything is lovely.

This appears to happen because of the word "and" interfering with its thinking. I thought I needed quotes around the macro variable being resolved so I tried this syntax which adds quotes around the first occurrence of the &designationregion. macro variable.

%if  "&designationregion." = _ALL_VALUES_ %then %do;%end;

%else %do;

and d.designationregion in (

%if &designationregion_count. = 1 %then "&designationregion.";

%else %do i=1 %to &designationregion_count.;

"&&designationregion&i."

%end;

)

%end;

This does in fact solve the problem and the error no longer occurs, but now, when I select "all possible values" from the prompt, the query returns nothing and I want instead for it to return everything.

So I'm clearly missing something in how to properly get it to ignore the and AND properly compare against the default _ALL_VALUES_ value.

We're running all of this in SAS 9.4 on Linux but my Spidey sense tells me that it's not versioning, but syntax that is at the heart of my problem.

So I ask you, wise and powerful onlookers, where am I going wrong?

Chris

[Astounding · Accepted solution]

You got very close to a solution by adding the double quotes.  Try adding double quotes to the other side of the comparison as well and see if you find the match that you expect:

= "_ALL_VALUES_"

[chawkins]

For lack of a more elegant response....Aaaaaaaaaaaaaaaaaaaaaaaaaaargh.

@Astounding, thanks for the quick review. I was all set to tell you that I had already tried that without improvement, but I think I must have missed one (there are a boatload with parallel syntax), because now all the robots are singing in perfect harmony.

On to more troubling mysteries I suppose.

Thanks again!

Chris