Siddiqui and Harry:

When I saw Harry's reply, I wondered about the IN operator blank-delimited list instead of the usual comma-delimited list. My initial tests showed that the IN operator in SAS SQL does accept blank-delimited lists of literals (constants). Nonetheless, it does not accept blank-delimited lists of variable names:

data test; x='1'; y=x; output test; x='2'; x=y; output test; run; proc sql; select * from test where y in ( x '1' '2' '3') ; quit;

Remove the x in the list and the test program works as expected. It will, on the other hand, accept a single column of values ('relvar' is column) from the yield of a SELECT statement:

select * from test where y in (select x from test) ; This seemingly strange behavior actually makes sense. The SAS SQL IN operator accepts a list of literals as a constant, following the convention of SAS Data step operators. It also accepts the yield of a single column variable as a list (in this case arrayed vertically). It does not accept a list of column variable names because the 'relvar' type of the list of variables is tabular.

This workaround works:

select * from test as t1 where y in ((select x from test as t2 where t1.ID=t2.ID) union (select y from test as t2 where t1.ID=t2.ID) ) ; I don't see much practical use for it, but it illustrates that the SAS SQL IN operator will accept the yield of a query of column relvar type.

Sig -----Original Message----- From: siddiqui [mailto:ms7942@ALBANY.EDU] Sent: Friday, 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;