Keeping only the duplicates

Question

I'm trying to keep only the duplicate results for one column in a table. This is what I have.

proc sql; 
    create table DUPLICATES as 
    select Address, count(*) as count 
    from TEST_TABLE
    group by Address
    having COUNT gt 1 
    ;
quit;

Is there any easier way to do this or an alternative I didn't think of? It seems goofy that I then have to re-join it with the original table to get my answer.

Answer 1

proc sort data=TEST_TABLE;
    by Address;
run;

data DUPLICATES;
    set TEST_TABLE;
    by Address;
    if not (first.Address and last.Address) then output;
run;