Date:         Wed, 11 Aug 2004 11:14:05 -0700
Reply-To:     "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject:      Re: keep duplicated record
Comments: To: Glenn Heagerty <gheagerty@EARTHLINK.NET>

Glenn - I hadn't seen this feature yet, thanks.

A comment though: doesn't the new v9 dupout= option keep the duplicate
records that were to be deleted? It contains the n-1 removed duplicates from
each by group, not the full duplicate by groups.  This way the Out= and the
Dupout= datasets can be concatenated to recreate the original source data.

regards

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Glenn
Heagerty
Sent: Wednesday, August 11, 2004 10:36 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: keep duplicated record

Hi Eric,

Here's what I use when I want the duplicate records(SAS v9):

proc sort data=lib.all out=work.deduped dupout=work.dups nodupkey equals;
    by class1-class3;
run;

It preserve the order of the records within a by-group before removing
duplicates.

It's not a SQL solution like you asked for, though I HIH.

Glenn


eric wrote:
> Hi,all:
> I would like to keep duplicated record from my dataset
>
> I have some variables such as  class1 class2 class3 class4 var1 var2
> var3
>>From past posting of newsgroup, I found the following:
>
> proc sql;
>     create table WORK01 as
>        select key
>           from LIB.ALL
>            group by KEY
>                 having count(KEY)>=2
>                   order by KEY;
>    quit;
>
> How can I define the key for my proc sql?? I got something as
> following which is not working. Can someone give me a simple example?
>
>
> proc sql;
>
> create table work01 as
>
>   select class1,class2, class3, class4 ,var1, var2, var3
>   from LIB.ALL
>            group by class1,class2, class3,
>                 having count(class1,class2, class3)>=2
>                   order by class1,class2, class3 ;
>    quit;
>
>
> Thanks.
>
> Eric
>