topic Re: question about selectively removing duplicates in SAS Programming

question about selectively removing duplicates

lmyers2 — Sat, 20 Jan 2018 21:03:11 GMT

Hello,

I'm using SAS 9.4 edition. I'm trying to selectively remove duplicate observations but I'm not sure how to tell SAS to keep certain rows. I'd like to keep the row with more data in the state field, for example. My code is below and it's not working. Thanks for the help!

CASE_ID State

1 MA

proc sort data=casemergedata out=newdata nodupkey; by CASE_ID; run;
proc freq data=newdata; tables CASE_CONTROL; run;

Re: question about selectively removing duplicates

ChrisNZ — Sat, 20 Jan 2018 21:24:20 GMT

Are you only looking at one variable?

In that case just also sort by descending STATE.

Re: question about selectively removing duplicates

PeterClemmensen — Sat, 20 Jan 2018 21:29:52 GMT

data have;
input CASE_ID State$;
infile datalines missover;
datalines;
1 MA   
1  
; 

proc sort data=have out=want nodupkey;
   by descending CASE_ID;
run;

Re: question about selectively removing duplicates

art297 — Sat, 20 Jan 2018 22:54:31 GMT

The way I like to do things like that is to use two sorts. First one, to order the records so that the record with the maximum value of state comes first within each id. i.e.:

proc sort data=casemergedata out=newdata;
  by CASE_ID descending state;
run;

Then a second sort to get rid of the duplicate caseids. i.e.:

proc sort data=newdata nodupkey;
  by CASE_ID;
run;

Art, CEO, AnalystFinder.com

Re: question about selectively removing duplicates

ChrisNZ — Sat, 20 Jan 2018 23:06:35 GMT

You can add the presorted option to the second sort to speed things up.

Re: question about selectively removing duplicates

Astounding — Sun, 21 Jan 2018 01:44:46 GMT

Here's a version that works when you have many variables, not just 2 or 3:

proc sort data=have;

by case_id state;

run;

data want;

update have (obs=0) have;

by case_id;

run;

It also works when you have many observations per CASE_ID.

Re: question about selectively removing duplicates

Patrick — Sun, 21 Jan 2018 01:55:30 GMT

@ChrisNZ

I really liked your presorted proposal. Unfortunately this option doesn't appear to have an effect if combined with nodupkey.

28         proc sort data=sashelp.class out=work.class(sortedby=age);
29           by age name;
30         run;
         
33         proc sort data=work.class out=test1 presorted nodupkey;
34           by age;
35         run;

NOTE: Input data set is not in sorted order.
NOTE: ...
         
37         proc sort data=work.class out=test1 presorted;
38           by age;
39         run;
NOTE: Sort order of input data set has been verified.
NOTE: Input data set is already sorted; it has been copied to the output data set.
NOTE: ...

Re: question about selectively removing duplicates

PGStats — Sun, 21 Jan 2018 05:25:08 GMT

@Astounding, you may end up with unwanted results with UPDATE

data have;
input case_id state1 $ state2 $;
datalines;
1 A .
1 . .
1 . B
;

proc sort data=have;
by case_id state1 state2;
run;

data want;
update have (obs=0) have;
by case_id;
run;

proc print; run;

                         Obs    case_id    state1    state2

                          1        1         A         B

Re: question about selectively removing duplicates

Astounding — Sun, 21 Jan 2018 14:35:04 GMT

@PGStats, you're right about the possibility of complications. But it's difficult to tell what is really necessary here. The original post is likely to be a simplification of the actual scenario so I wanted to make sure the UPDATE program was at least considered. The example that you posted might actually be a desirable result ... it's up to the original poster to decide.

Note that I had the data set sorted by STATE so that non-missing-values the non-missing STATE observation would prevail if there is a conflict.

Re: question about selectively removing duplicates

ChrisNZ — Sun, 21 Jan 2018 20:24:10 GMT

@Patrick

There is no reason presorted should not work with nodupkey.

Reporting this as a bug now.

Re: question about selectively removing duplicates

ChrisNZ — Mon, 29 Jan 2018 22:56:25 GMT

@Patrick @art297 Update: This is not a bug, it is a planned shortcoming.

Option presorted is only there to set flags (that's a very expensive flag when you copy a whole table to set it!).

If any processing is required (such as removing duplicates) this option cannot be used.

What a wasted opportunity!

It is all described here is its flawed glory.

All the more reason to avoid such wastage by creating a new data step option.

I have asked for the log NOTE and the presorted documentation to be corrected.

Re: question about selectively removing duplicates

art297 — Mon, 29 Jan 2018 23:08:00 GMT

@ChrisNZ: Thanks for looking into it! I suppose the explanation made sense to somebody (at SAS) but I, for one, don't understand what benefit they gained by excluding PRESORTED when NODUPKEY is used.

Art, CEO, AnalystFinder.com

Re: question about selectively removing duplicates

ChrisNZ — Tue, 30 Jan 2018 01:07:32 GMT

@art297 This option only helps to set metadata flags. So it's not really about excluding other options.

If you run

proc sort data=SASHELP.CLASS out=CLASS presorted nodupkey danish;
  by NAME;
run;

then option PRESORTED will trigger the validation of the NODUPKEY and DANISH options, and add the corresponding metadata.

In this case PROC CONTENTS shows the updated values for flags SORTED, NODUPKEY and COLLATE.

That's all PRESORTED does: set the metadata. This option excludes any processing. What a wasted opportunity.

Re: question about selectively removing duplicates

art297 — Tue, 30 Jan 2018 01:24:35 GMT

@ChrisNZ: Not totally true. Take a look at the log after running the following .. particularly the log for the last two sorts.

The presorted option cuts the sort down by 2/3rds of the time.

data have;
  set sashelp.class;
  do i=1 to 100000;
    output;
  end;
run;

proc sort data=have;
  by sex;
run;

data need;
  set have;
run;
 
proc sort data=need out=want1;
  by sex;
run;

proc sort data=need out=want2 presorted;
  by sex;
run;

Art, CEO, AnalystFinder.com

Re: question about selectively removing duplicates

ChrisNZ — Tue, 30 Jan 2018 02:05:55 GMT

Yes PRESORTED avoids sorting, and just copies the table instead, thereby saving time.

But that's only true if no other processing, such as removing duplicates, is required.

That's where the wasted opportunity is: why not allow that removal? A simple sequential read would then suffice, instead of sorting.

Note that PRESORTED is even more efficient if you don't create another table.

proc sort data=HAVE presorted; by NAME; run;

data _null_; set HAVE; by NAME; run;

Both these steps take about the same time, as no data is copied and the data set is just read sequentially.

I am not saying this is a useless option, quite the contrary.

I am saying it could easily have been more useful.

As in the case of this thread's question.

Re: question about selectively removing duplicates

art297 — Tue, 30 Jan 2018 02:24:32 GMT

Then we totally agree!

Art, CEO, AnalystFinder.com

Re: question about selectively removing duplicates

ChrisNZ — Wed, 31 Jan 2018 21:55:34 GMT

@art297 Worth noting as it is the first time I have this feedback after reporting countless issues:

The developer will:

Look at changing the wording of the Notes generated with the various options in PROC SORT
Work with Publications to ensure that the documentation is updated to clarify everything related to the options used and the effect they have on each other

So it seems this one will not be shelved! Yay! 🙂

Re: question about selectively removing duplicates

art297 — Wed, 31 Jan 2018 22:21:03 GMT

@ChrisNZ: What's the saying?: if you don't succeed the first time, try, try again?

Hopefully, in trying to explain it, they'll realize that the option should simply perform as a typical user would expect!

Art, CEO, AnalystFinder.com

Re: question about selectively removing duplicates

ChrisNZ — Wed, 31 Jan 2018 23:17:51 GMT

That's over-optimistic I reckon. An updated message and documentation is all we'll get.