Date: Fri, 30 Jan 2004 13:46:47 -0500
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: using an OR function in proc sql
Content-Type: text/plain
Harry:
I would have thought as well that SAS SQL would support lists of variable
names.
Apparently not... This simplest form of IN query generates a syntax error
unless x has a relvar type of scalar (cell):
data test;
ID='1';
x='1';
y=x;
output test;
ID='2';
x='2';
x=y;
output test;
run;
proc sql;
select * from test as t1
where '1' in (x)
;
quit;
SAS SQL appears to operate on the transpose of a list of constants, a
columnar list of constants from the yield of a subquery, or a column
variable that has a relvar type of cell (resolves to a scalar value).
Unfortunately a programmer would have to understand the concept of relvar
type to make sense of the SAS SQL implementation of the IN operator. I'd
like to see a simpler explanation of the domain of the arguments of IN, and
upgrades in future versions that make SAS SQL more compatible with other
dialects of SQL.
Sig
-----Original Message-----
From: Droogendyk, Harry [mailto:Harry.Droogendyk@CIBC.COM]
Sent: Friday, January 30, 2004 1:05 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: using an OR function in proc sql
Sig:
I believe there may be an inconsistency in SAS's implemention of SQL vs the
rest of the world ( one of many perhaps ). According to Celko's Smarties
tome ( pg 199 in 2nd edition, published in 2000 ), the following syntax is
legal. I've used it in Oracle queries.
select * from table
where 'literal' in ( fld1, fld2, fld3, fld4 ).
SAS complains:
521 data a;
522 i = 'abc';
523 j = 'def';
524 k = 'ghi';
525 run;
NOTE: The data set WORK.A has 1 observations and 3 variables.
NOTE: DATA statement used:
real time 0.10 seconds
526
527 proc sql;
528 select * from a
NOTE: SCL source line.
529 where 'abc' in ( i, j, k );
-
79
76
ERROR 79-322: Expecting a SELECT.
ERROR 76-322: Syntax error, statement will be ignored.
530 quit;
NOTE: The SAS System stopped processing this step because of errors.
-----Original Message-----
From: Sigurd Hermansen [mailto:HERMANS1@WESTAT.COM]
Sent: January 30, 2004 11:46 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: using an OR function in proc sql
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;
