Date:         Fri, 9 Nov 2007 08:04:18 -0800
Reply-To:     Hari <excel_hari@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Hari <excel_hari@YAHOO.COM>
Organization: http://groups.google.com
Subject:      Re: Order of specification matters with BETWEEN AND conditional
              operator
Comments: To: sas-l@uga.edu
In-Reply-To:  <1194447291.041433.327430@o38g2000hse.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

On Nov 7, 7:54 pm, Spjcdc <spj...@gmail.com> wrote:
> I don't think it's because of the between statement, I think that the
> WHERE statement is not evaluated as you think.
>
> Someone else can give you a more detailed explanation as to why, but I
> like to force statements to resolve the way I want them.  You have 3
> conditions to test:
>
> where cond1 and cond2 or cond3
>
> My suspicion is that it's actually being evaluated as
>
> where  (cond1 and cond2) or cond3
>
> What you want is
>
> Where cond1 and (cond2 or cond3)
>
> My guess is that if you put parens in the appropriate places, it will
> work properly:
>
>  where (calculated CUSTIDlen between 1 and 4)
>  and ( offid is missing or offid in ("23" "24" "25" "26" "27"
> "28")  );
>
> Perhaps you could rewrite it to
>
> where (calculated CUSTIDlen between 1 and 4)  and offid in (" " "23"
> "24" "25" "26" "27" "28")
> ;
>
> and avoid the explicit OR statement entirely.
> Steve
>
> On Nov 7, 9:18 am, Hari <excel_h...@yahoo.com> wrote:
>
>
>
> > Hi,
>
> > I have a very simple code like the following wherein Im trying to
> > output sample records which have the lenght of CUSTID ge 1 and le 4. I
> > used the following construct
>
> > Proc SQL outobs = 100;
> > Select *, Length(CUSTID) as CUSTIDlen
> >         from OffRaw.May07
> > where calculated CUSTIDlen between 1 and 4
> > and offid is missing or offid in ("23" "24" "25" "26"
> >                         "27" "28");
>
> > but strangely the result consists of even records which have CUSTIDlen
> > eq 9 etc (Note, the second condition for offID works correctly)
>
> > On the other hand, if I write the code as
>
> > Select *, Length(CUSTID) as CUSTIDlen
> >         from OffRaw.May07
> > where offid is missing or offid in ("23" "24" "25" "26"
> >                         "27" "28")
> > and calculated CUSTIDlen between 1 and 4
> > ;
>
> > then it works correctly (that is records with length of custID between
> > 1 and 4 are the only one outputted in addition to the condition on
> > OffId).
>
> > I want to understand as to why changing the order of BETWEEN AND
> > operator to the end works but not in the other scenario?
>
> > hp- Hide quoted text -
>
> - Show quoted text -

Steven and Mary Thanks a lot for the answer.

In hindsight im wondering as to how I could afford to commit this
stupid mistake!

Thanks again,
hp