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
