topic Re: remove duplicates but keep all fields (using proc sql) in SAS Procedures

remove duplicates but keep all fields (using proc sql)

podarum — Thu, 05 Sep 2013 14:55:52 GMT

Hi, is there a way to remove duplicates but keep all fields.. Example below (but keep in mind there are 30 fields in total, and I want to capture all and the table is a huge data warehouse table) :

HAVE:

Acct_No Name Date

0001 Mike Jun-13

0002 Mike Jun-13

0003 Fred Apr-13

0003 Fred May-13

0003 Fred Jun-13

0004 Will Sep-13

WANT:

Acct_No Name Date

0001 Mike Jun-13

0002 Mike Jun-13

0003 Fred Apr-13

0004 Will Sep-13

Re: remove duplicates but keep all fields (using proc sql)

esjackso — Thu, 05 Sep 2013 15:05:01 GMT

Does the date matter? IE your example chose the first one ... was that by design?

Re: remove duplicates but keep all fields (using proc sql)

jpstats — Thu, 05 Sep 2013 15:05:42 GMT

Just use -

proc sort

data = mydataset nodupkey;

by acct_no name;

run;

Or, if you have to use SQL -

proc sql noprint;

create table key_keep as

select distinct acct_no, name, date

from mydataset

group by acct_no, name

having date = min(date);

create table mydataset2 as

select a.*

from mydataset a

INNER JOIN

key_keep b

a.acct_no = b.acct_no and

a.name = b.name and

a.date = b.date;

quit;

I think that should do the trick.

Re: remove duplicates but keep all fields (using proc sql)

podarum — Thu, 05 Sep 2013 15:09:04 GMT

no it doesn't matter, as long as I get only one by Acct_No

and jpstats, the data is from a server, and it's large that I have to use proc sql (rsubmit)

Re: remove duplicates but keep all fields (using proc sql)

esjackso — Thu, 05 Sep 2013 15:10:14 GMT

That doesnt actually get what they ask for a couple of reasons:

- doesnt use SQL

- it really is only a no dup on acct_no since the dates are actually unique

Re: remove duplicates but keep all fields (using proc sql)

jpstats — Thu, 05 Sep 2013 15:11:32 GMT

Right, good catch.

Re: remove duplicates but keep all fields (using proc sql)

esjackso — Thu, 05 Sep 2013 15:11:43 GMT

Are trying to update the table in place or just get a sas dataset to work with?

Re: remove duplicates but keep all fields (using proc sql)

PGStats — Thu, 05 Sep 2013 15:13:14 GMT

With SQL you will have somehow to tell the procedure which value of your fields to keep. In your example, it seems that you want to keep the minimum Date. If your choice of value to keep depends on the order of the data in the table (e.g. the first Date), then SQL is not a good tool to perform the operation and proc SORT or a datastep would do a better job.

Re: remove duplicates but keep all fields (using proc sql)

podarum — Thu, 05 Sep 2013 15:14:16 GMT

a dataset to work with

Re: remove duplicates but keep all fields (using proc sql)

esjackso — Thu, 05 Sep 2013 15:24:21 GMT

This seems to work with your example assuming that the date is numeric:

proc sql;

create table want as

select acct_no, name, min(date) as date

from have

group by acct_no, name

;

quit;

Re: remove duplicates but keep all fields (using proc sql)

jpstats — Thu, 05 Sep 2013 15:29:59 GMT

I may have misunderstood, but I think he wants the resulting set to have all the fields...

Re: remove duplicates but keep all fields (using proc sql)

cooker — Thu, 05 Sep 2013 15:33:08 GMT

You may try the following:

[insert your passthru code here]

proc sql;

create table newtable as

select a.acct_no, a.name, a.date

from dataset a

where a.date = (select min(a1.date)

from dataset a1

where a1.name = a.name

and a1.acct_no = a.acct_no)

quit;

You'll want to list all of your fields of course or use the a.* method. There are other methods which are available to Oracle DB but this should work on most platforms.

Good luck!

Re: remove duplicates but keep all fields (using proc sql)

podarum — Thu, 05 Sep 2013 15:33:27 GMT

that is correct, and since theer are about 30 fields, I don't want to type each one.. a dup may occur though in another field, my example only shows date, but it could happen for any other of the 30 fields... I may have to comprimise.

Re: remove duplicates but keep all fields (using proc sql)

esjackso — Thu, 05 Sep 2013 15:41:35 GMT

Even taking the min of just the date may not get rid of all the duplicates if the min is in the table multiple time for acctno and name and other fields changing... if the values dont really matter then proc sort with out= option is the best choice as mentioned in his post.

Re: remove duplicates but keep all fields (using proc sql)

cooker — Thu, 05 Sep 2013 15:47:51 GMT

Yes, if you could have differences in all the columns; you'd have to replicate the subquery for each column. This may be something which knocks the snot out of your DB server and may timeout.

Re: remove duplicates but keep all fields (using proc sql)

jpstats — Thu, 05 Sep 2013 15:59:36 GMT

Copy & paste the following:

data mydataset;

input acct_no name $ date date9.;

datalines;

0001 Mike 01JUN2013

0002 Mike 01JUN2013

0003 Fred 01APR2013

0003 Fred 01MAY2013

0003 Fred 01JUN2013

0004 Will 01SEP2013

run;

proc sql noprint;

create table key_keep as

select distinct acct_no, name, date

from mydataset

group by acct_no, name

having date = min(date);

create table mydataset2 as

select a.*

from mydataset a

INNER JOIN

key_keep b

a.acct_no = b.acct_no and

a.name = b.name and

a.date = b.date;

quit;

The output (mydataset2) should match your specification.

Re: remove duplicates but keep all fields (using proc sql)

Haikuo — Fri, 06 Sep 2013 01:13:27 GMT

Well, since nobody has had mentioned, and it seems to work (Proc SQL only, and regardless of the quantity of variables), however, it will have to involve an undocumented feature, so not recommend for production usage.

proc sql;

create table no_dup as

select * from have

group by acct_no, name

having monotonic()=min(monotonic());

quit;

Haikuo

Re: remove duplicates but keep all fields (using proc sql)

esjackso — Fri, 06 Sep 2013 12:16:17 GMT

Very interesting ... I will have to file this trick away!

Re: remove duplicates but keep all fields (using proc sql)

esjackso — Fri, 06 Sep 2013 12:45:37 GMT

So I tried the trick out and must be doing something wrong.

I was expecting the same results from a proc sort nodupkey process but didnt get that. What am I missing? The made up data is attached

LOG:

15 proc sql;

16 create table no_dup as

17 select *

18 from duptest

19 group by name, date

20 having monotonic()=min(monotonic());

NOTE: The query requires remerging summary statistics back with the

original data.

NOTE: Table WORK.NO_DUP created, with 1 rows and 5 columns.

21 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

22 proc sort data=duptest out=out nodupkeys; by name date; run;

NOTE: There were 12 observations read from the data set WORK.DUPTEST.

NOTE: 1 observations with duplicate key values were deleted.

NOTE: The data set WORK.OUT has 11 observations and 5 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Re: remove duplicates but keep all fields (using proc sql)

Tom — Fri, 06 Sep 2013 13:20:25 GMT

It is probably better to use the MONOTONIC() function in a subquery and assign it to a variable rather than try to call the function in two different places in the same SQL code.

create table no_dup (drop=_n_) as

select *

from (select *,monotonic() as _n_ from duptest)

group by name,date

having _n_ = min(_n_)

order by name,date

;