Newsgroups: bit.listserv.sas-l
Date:         Thu, 10 Sep 1992 20:54:00 PDT
Reply-To:     HOFER@USCMVSA.BITNET
From:         HOFER@USCMVSA.BITNET
Subject:      summary: Question on combining two slightly overlapping...

To summarize for the group regarding the question on combining two
overlapping data sets:

The question:

>I have two longitudinal files that I wish to combine into a
>single longitudinal file. For this, I was using a data step:
>DATA COMBINED; SET BASE1 BASE2;
>  However, there is some overlap between the two data files.
>I know that I can combine the two files using a MERGE command
>and a BY statement--Since the two sets contain exactly the same
>number and name of variables, the second data set will overwrite
>the first (i.e., BY ID;).
>  The DATA...; MERGE...: BY...; would work fine as long as I knew
>that the data was precisely the same for duplicate ID's.
>The question is regarding how I would obtain a data set which
>contained the duplicate records only? If I use the DATA... SET
>step, how could I search out, compare, and delete the duplicate
>ID's.

Many thanks for those who responded. I appreciate the variety of methods
for accomplishing this task.

The responses:

From:         Melvin Klassen <KLASSEN@UVVM.BITNET>

  DATA _NULL_;
    MERGE BASE1 (IN=FILE1) BASE2 (IN=FILE2);
    BY ID;
    IF FILE1 = FILE2 THEN /* Both values can NEVER be zero. */
                          /* So, if they are equal,         */
         DO;              /* then both are "1" !            */
         PUT 'Duplicate record exists for ' ID=;
         OUTPUT;
         END;
    RETURN;
  RUN;
========================================================================
From: "Roger Lustig" <roger@ASTRO.PRINCETON.EDU>

This involves using a BY statement in a DATA step, and the accompanying
FIRST. and LAST. variables.

DATA COMBINED; SET BASE1 BASE2;
run;
proc sort data=combined; by id v1 v2 v3 v4;
run;
data doubles singles; set combined; by id v1 v2 v3 v4;
if first.v4 and last.v4 then output singles;
else output doubles;
run;

OK?  This will produce two datasets, one containing records that
are unique on the set of sort vars, and one with the non-unique ones.
You can read up on BY processing in the manual.

Roger
========================================================================
From: "Nancy M. Bonney" <0003459876@MCIMAIL.COM>

If you need to look at all the records with duplicate
IDs, take a look at PROC COMPARE. If you want to just
get rid of duplicate observations, there are some
options in PROC SORT that may do what you want. One
option deletes observations with duplicate BY variables;
another option deletes observations with duplicate BY
variables only if all the variables are duplicates.
========================================================================
From: gibes@SWIRL.MONSANTO.COM(Kernon Gibes)

Surely your question has been answered by now, but I haven't seen a
summary posted so just in case it hasn't...

If you're using 6.06+, you might try SQL.  The code:

proc sql;
 select * from base1
 union
 select * from base2
quit;

will "append" base1 and base2 automatically getting rid of duplicate
records.
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU