Date:         Sat, 22 Mar 2003 19:02:25 -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: PROC SQL UNIQUE vs. DISTINCT
Comments: To: "Erik013@AOL.COM" <Erik013@AOL.COM>
Content-Type: text/plain; charset="iso-8859-1"

The original SAS documentation for SAS SQL, as I recall, specified DISTINCT
as the operator in a SAS SELECT clause that eliminates duplicate rows (where
each column in the dataset has exactly the same value) in the yield of a
query. In the list of differences between SAS SQL and the ANSI standard in
effect at the time, the SAS SQL manual included SELECT UNIQUE as an
unsupported option. Since RDBMS's may implement row uniqueness constraints
using indexes, SAS offers UNIQUE as an option on SQL indexes, as Ray
explains.

I tend to go with the documented options, even if undocumented ones appear
to work the same way. The term 'DISTINCT' makes perfect sense, and it helps
prevent confusion with index constraints.

Sig

-----Original Message-----
From: Erik013@AOL.COM [mailto:Erik013@AOL.COM]
Sent: Friday, March 21, 2003 12:33 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: PROC SQL UNIQUE vs. DISTINCT


Thank you to both Diane and Ray for their commentary, including Ray's RTFM
remark.

I did RTFM and did find what Ray stated but the code segments were pulled
directly from a system program and I had never seen "UNIQUE" on a SELECT
statement with no mention of an index creation.  I did some tests on some
data and it appeared that it had the same use as DISTINCT, but curiosity got
the best of me and I wanted to know if there is a difference.

Erik S. Larsen
Independent Consultant
PO Box 505
Charleston, SC 29401

A SAS Alliance Consulting Partner Silver Member
A SAS Certified Professional V8

(843) 367-2862 (SC office)
(703) 568-3083 (VA office)
(801) 681-3281 (fax)