BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chawkins
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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_"

View solution in original post

2 REPLIES 2
Astounding
PROC Star

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
Obsidian | Level 7

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

 

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1047 views
  • 0 likes
  • 2 in conversation