topic OR operator and where clause in Developers

OR operator and where clause

AshleyM — Thu, 02 Feb 2012 16:38:06 GMT

I'm trying to write code that allows an end user the option to select either the "var1" or "var2" date range. Currently I have the following code below, which works only for "var1" date range. How would I incorporate code so the end user has the option of choosing "var1" or "var2" (not both)? I tried using the OR operator (|) but to no avail.

where filedate between "&VAR1_MIN"d and "&VAR1_MAX"d;

OR operator and where clause

Hima — Thu, 02 Feb 2012 20:09:58 GMT

Have you tried IN operator.

OR operator and where clause

Tom — Thu, 02 Feb 2012 20:16:50 GMT

Not sure what you mean by one but not both. If you are saying that on set of macro variables will be empty then you might need some macro logic. If you just want to find records in one range or the other then use () to group your conditions.

where (filedate between "&VAR1_MIN"d and "&VAR1_MAX"d)

or (filedate between "&VAR2_MIN"d and "&VAR2_MAX"d)

;

Re: OR operator and where clause

Astounding — Fri, 03 Feb 2012 02:25:20 GMT

Assuming I understand the question, this would do the trick.

%macro subset (variable_to_use=);

where filedate between "&&&variable_to_use._MIN"d and "&&&variable_to_use._MAX"d;

%mend subset;

You might add more code inside the macro if you would like, and you need to values assigned to &VAR1_MIN, &VAR1_MAX, &VAR2_MIN, and &VAR2_MAX. Again if I understand the question properly, the user would call the macro using either of these:

%SUBSET (variable_to_use=VAR1)

%SUBSET (variable_to_use=VAR2)

And the macro generates the appropriate WHERE statement.

Good luck.

OR operator and where clause

AshleyM — Fri, 03 Feb 2012 13:32:35 GMT

I'm including my original code below. Yes, what we are hoping to do is allow the end user the option to either enter a filedate_min and filedate_max or termdate_min and termdate_max. In other words, if the user enters a date range in the prompts for the stroed process for only filedate_min and filedate_max, the code needs to suggest that termdate_min an termdate_max remain NULL or blank and vice verse.

%stpbegin;
libname IDB "/res/IDB/updates";

%macro data();
data work.test;
set idb.cv00on;
where
(NOS in
(
%DO I = 1 %TO &NOS_COUNT;
%if &i =1 %then %do;
&nos
%end;
%else %do;
&&nos&i
%end;
%END;
)
)
and (filedate between "&FILEDATE_MIN"d and "&FILEDATE_MAX"d);
run;
%mend;
%data
proc print data=work.test;
run;
%stpend;

OR operator and where clause

art297 — Fri, 03 Feb 2012 13:43:16 GMT

It looks like you ask the user to fill in two of four entries, i.e.,

filedate_min

filedate_max

termdate_min

termdate_max

You could either allow just one set or both and, in either case, have defaults such that if a field isn't entered, it gets the highest or lowest possible value. Thus, if filedate_min or termdate_min are left blank, they are set to the lowest possible value (e.g., sometime in the 1800s maybe). And, if filedate_max or termdate_max are left blank, they are set to some seemingly absured high value (e.g., 31DEC5000);

That way you could just use a where statement that includes both.

OR operator and where clause

AshleyM — Fri, 03 Feb 2012 14:10:46 GMT

Art,

Yes, basically if an end user is interested in the filedate they are not interested in the termdate. Therefore, how would I default to keep filedate_min and filedate_max blank if the user enters information for termdate_min and termdate_max?

OR operator and where clause

art297 — Fri, 03 Feb 2012 14:19:54 GMT

If you default all four to their respective min and max possible values, then it becomes a non-issue.

Thus, that way, if one doesn't put a value into any of those fields, they get all of the records.

If they only enter a filedate_min, they get all records after than min date.

If they enter all four values, they would only get records that meet the two ranges.

In short, going that way, your where statement would only have to include (I don't know the variable names):

where=((filedate between "&FILEDATE_MIN"d and "&FILEDATE_MAX"d) and

(termdate between "&termDATE_MIN"d and "&termDATE_MAX"d));

Of course, that is assuming that I correctly understand what you are trying to do.

OR operator and where clause

Astounding — Fri, 03 Feb 2012 16:17:04 GMT

Ashley,

The programming part is easy. The part that you have to work through is setting up the rules. In real life, an end user might enter anywhere from 0 to 4 pieces of information. You'll need to spell out what should happen in each case. There's no right and wrong here, from a programming point of view. For example, if the end user enters only FILEDATE_MIN, what should happen? Should the program send a message to the user, and allow the user a second chance? Should the program shut down? Should it send a warning to the user and select all records from FILEDATE_MIN going forward? These decisions are much harder then the programming part. It's fairly trivial to program that if the user enters all four, only use one set and ignore the other set.

Good luck.

OR operator and where clause

Astounding — Fri, 03 Feb 2012 18:20:48 GMT

Sorry, forgot to add the code. You have:

and (filedate between "&FILEDATE_MIN"d and "&FILEDATE_MAX"d);

Here's one possible replacement:

and %if %length(&termdate_min) > 0 %then (termdate between "&TERMDATE_MIN"d and "&TERMDATE_MAX"d);

%else (filedate between "&FILEDATE_MIN"d and "&FILEDATE_MAX"d);

;

Don't omit that final semicolon.

As long as the user entered something for &TERMDATE_MIN, the program uses TERMDATE in the WHERE clause. Otherwise it uses FILEDATE.

Good luck.

Re: OR operator and where clause

AshleyM — Fri, 03 Feb 2012 18:28:20 GMT

Thank you "Astounding." Your replacement code works perfectly. Now, onto figuring out how to set the prompts in Management Console.

OR operator and where clause

AshleyM — Wed, 22 Feb 2012 16:26:36 GMT

I apologize for the confusion. I have since modified the capabilities I'd like the users to have. Essentially, I want the users to be able to select the following parameters below. Thus, giving them the option of choosing filedate or termdate or filedate and termdate.

where (filedate between "&VAR1_MIN"d and "&VAR1_MAX"d)

or (termdate between "&VAR2_MIN"d and "&VAR2_MAX"d) or ((filedate between "&VAR1_MIN"d and "&VAR1_MAX"d) and (termdate between "&VAR2_MIN"d and "&VAR2_MAX"d))

;

OR operator and where clause

art297 — Wed, 22 Feb 2012 17:01:35 GMT

Ashley,

This looks like a stored process of which I have absolutely 0 expertise. However, that said, it looks like you are able to pass macro variables that users can select, namely &VAR1_MIN, &VAR1_MAX, &VAR2_MIN and &VAR2_MAX

Couldn't you default the two min macro variables to a date lower than any filedate or termdate, and default the two max variables to a date greater than any filedate or termdate, and only replace those if entered as criteria by the user?

If so, then your where statement could be reduced to:

where (filedate between "&VAR1_MIN"d and "&VAR1_MAX"d)

and (termdate between "&VAR2_MIN"d and "&VAR2_MAX"d)