Date:         Fri, 30 Jan 2004 09:11:47 -0500
Reply-To:     "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Subject:      Re: using an OR function in proc sql
Content-Type: text/plain; charset="iso-8859-1"

I'm sorry, brain damage on my part.  I should know better than to stick my
non-SQLhead where it doesn't belong.  Here's a simple example of using ands
and ors in a where clause, undoubtedly improvable by those who can.

data a;
  do key = 1 to 10;
     fld1 = key * 2;
     fld2 = fld1 * 20;
     fld3 = fld2 * 200;
     output;
  end;
run;

data b;
  key = 2; fld = 16;output;
  key = 3; fld = 17;output;
  key = 4; fld = 160;output;
  key = 5; fld = 40000;output;
  key =11; fld = 400;output;
run;

proc sql;
  create table matched as
    select a.key, b.fld, a.fld1, a.fld2, a.fld3
      from a, b
     where a.key = b.key
       and ( b.fld = a.fld1 or
             b.fld = a.fld2 or
             b.fld = a.fld3 )
    ;
quit;

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of
siddiqui
Sent: January 30, 2004 8:45 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: using an OR function in proc sql


hi harry

tried the in operator my log gives

b.pfrm in (a.pfr1 a.pfr2 a.pfr3 )
           -
           79
           76
ERROR 79-322: Expecting a SELECT.

ERROR 76-322: Syntax error, statement will be ignored.

any clue?
thanks
siddiqui




Harry.Droogendyk@CIBC.COM (Droogendyk, Harry) wrote in message
news:<F0161D3F7AC5D411A5BE009027E774D60E61CDC8@gemmrd-scc013eu.gem.cibc.com>
...
> How about the IN operator?
>
>                 proc sql;
>                 create table bwtmatched as
>                 select a.rec,a.pibpfr,b.pfrm,a.pfr1,a.pfr2,a.pfr3
>                 from my a,date b
>                 where a.pibpfr=b.pfm
>                    and
>                     b.pfrm in (a.pfr1 a.pfr2 a.pfr3 )
>                  ;
>
>
>
>                 -----Original Message-----
>                 From:   SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]
On
> Behalf Of siddiqui
>                 Sent:   Thursday, January 29, 2004 2:07 PM
>                 To:     SAS-L@LISTSERV.UGA.EDU
>                 Subject:        using an OR function in proc sql
>
>                 hi,
>
>                 I was wondering if the OR function can be used in proc sql
> and if so
>                 whats the correct way
>
>                 dataset my ;
>                 rec=records
>                 pibpfr
>                 pfr1
>                 pfr2
>                 pfr3
>
>                 dataset date;
>                 pfrm
>
>                 objective
>
>                 using procsql create a data set which contains records
which
> have
>                 pibpfr=pfrm
>                 or
>                 pfr1=pfrm
>                 or
>                 pfr2=pfrm
>                 or
>                 pfr3=pfrm
>
>
>                 demo code:
>
>                 proc sql;
>                 create table bwtmatched as
>                 select a.rec,a.pibpfr,b.pfrm,a.pfr1,a.pfr2,a.pfr3
>                 from my a,date b
>                 where a.pibpfr=b.pfm
>                 {and(or (a.pfr1=b.pfrm or a.pfr2=b.pfim or a.pfr3=b.pfrm )
> };
>
>                 quit;
>                 run;