Newsgroups: bit.listserv.sas-l
Date:         Fri, 28 Aug 1992 11:16:21 EDT
Reply-To:     Andy Norton <76350.1604@COMPUSERVE.COM>
From:         Andy Norton <76350.1604@COMPUSERVE.COM>
Subject:      RE: print dup records (SQL re-merging "gotcha")
Comments: To: SAS-L <SAS-L@AWIIMC12.IMC.UNIVIE.AC.AT>

----------------------------------------------------------------------
CONTENT:   Response
SUMMARY:   Printing duplicate records (response to Kernon Gibes)
           Oddities of SQL re-merging
REL/PLTF:  6.07.01/CMS, 6.04/PC-DOS
E-ADDR:    76350.1604@compuserve.com
NAME:      Andy Norton
ADDRESS:   Trilogy Consulting, 5228 Lovers Lane, Kalamazoo MI 49002
PHONE:     (616) 344-2191
----------------------------------------------------------------------

==>  While I was composing this, the correction to the original request
was posted (duplicate record not duplicate key).  This note is in
response to Kernon Gibes' response to the original query.

Not to beat a dead horse, but it's amazing how many issues arise from a
simple question.  The SAS SQL "re-merging" facility is pretty
slippery.

Kernon Gibes responded to a request for a method to print duplicate
observations (and I did as well, independently).  Kernon's answer was
  proc sql;
    select * from org
    group by idno
    having count(idno) > 1
    ;
  quit;
Which is almost identical to my response, except I used COUNT(*) to
count records, rather than COUNT(IDNO) which counts nonmissing values
of IDNO.  As long as there are no missing values of IDNO both will give
the same answer.

Now the interesting comment (from Kernon):
> will mimic Ray's solution except that if the records are PURE
> duplicates, each IDNO will be printed only once.  If the records are
> only duplicates with respect to IDNO but other variables differ, the
> above SQL code will list ALL the records for each IDNO having
> duplicate values.

This is not exactly true, although I can understand how the
misconception arose.  The correct answer is that

IF  the SELECT and HAVING clauses only reference columns listed in the
     GROUP BY clause
THEN there will be no re-merging
     and each IDNO will be printed only once.
ELSE there will be re-merging
     and in this application every record will be printed.

The remerging facility is a SAS extension to standard SQL.  The latter
only permits summary statistics and GROUP BY variables to be used in
the SELECT and HAVING clauses.  SAS allows detail-level variables to be
included as well.  SAS SQL first performs the standard SQL
summarization, and then does an inner join with the result of the FROM
clause (on the GROUP BY variables).

This makes the code more concise, but I'm beginning to think that the
SAS SQL remerging facility may not be such a hot idea.  At least the
documentation ought to warn of the following issue when SELECT * is
used:
    1.  When the SELECT clause only includes variables referenced in
        the GROUP BY clause, the output has one record per group.
    2.  When the SELECT clause includes variables not referenced in
        the GROUP BY clause, the output may have several records per
        group.
    3.  Because SELECT * is dynamically derived from the input data
        set, changes to the data set variables may change the situation
        from #1 to #2.  This can mess up existing programs.

Usually in SAS, adding additional variables to a dataset is transparent
to existing programs, so the problems can come as a surprise.  I have
notes of several different ways that remerges have rudely surprised
me, some of which I've barely touched on here.

Possible morals include
    1.  Read the log and watch for unexpected "remerging" messages.
    2.  Be cautious of SELECT *
    3.  Be cautious of adding variables to a dataset which is
        currently all-key.
    4.  Be cautious of SAS SQL statements which could invoke the
        re-merge facility.
    5.  Use SELECT DISTINCT to force unduplicated output (when
        desired).

Personally, I haven't decided which "morals" I should elevate to the
status of "conventions" yet.  Number 1 is a good starting point.

            Andy
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU
Subject: (no subject given)