LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (January 2004, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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;


Back to: Top of message | Previous page | Main SAS-L page