Date:         Thu, 27 Sep 2001 15:54:33 -0400
Reply-To:     Droogendyk Bill <bill_droogendyk@DOFASCO.CA>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Droogendyk Bill <bill_droogendyk@DOFASCO.CA>
Subject:      Re: SQL Complex Query with Minus
Content-Type: text/plain

 the following is recorded re: set operators in the Oracle7 Server SQL
Language Reference Manual

UNION: All distinct rows selected by either query - same as SAS "UNION"
UNION ALL: All distinct rows selected by either query, including all
duplicates - same as SAS "UNION ALL"
INTERSECT: All distinct rows selected by both queries - same as SAS
"INTERSECT"
MINUS: All distinct rows selected by the first query but not the second -
same as SAS "EXCEPT"


W.(Bill) Droogendyk
Quality Systems
Dofasco Inc. Hamilton ON Canada
905 548 7200 x3359

SAS: Anything else is just a toy!


> -----Original Message-----
> From: Dianne Rhodes [SMTP:RHODESD1@WESTAT.COM]
> Sent: 27 September, 2001 3:05 PM
> To:   SAS-L@LISTSERV.UGA.EDU
> Subject:      SQL Complex Query with Minus
>
> I am working with some SQL Plus Oracle queries and translating them to run
> in PROC SQL in SAS.
> This next batch has subqueries of this structure:
>
> create table hins_i01 as
> select id from
> (select id from table1, table2
>      where condition1 and table1.id=table2.id
>   intersect
>   select id from table3, table4
>    where condition2 and table3.id2=table4.id2
>   minus
>   (select id from table1, table2
>    where condition3 and table1.id2=table2.id2)
>   )
>  ;
> The INTERSECT operator translates just fine.  However,
> MINUS would appear to be a unique operator to the Oracle world.
> My understanding is that results of this query should return ids in the
> first subquery and not in the second subquery.  Can I use the EXCEPT
> operator in SAS to achieve the same results? Or am I missing something?
>
> These are exception edits so I am getting the results I want:  no rows
> returned.
>
> Dianne Louise Rhodes
> Sr. Systems Analyst
> Westat