topic Re: How to remove duplicates in SAS 8.3 EG Query Builder in SAS Enterprise Guide

How to remove duplicates in SAS 8.3 EG Query Builder

WorkUser — Wed, 18 Aug 2021 19:43:12 GMT

Is there a way to remove duplicates in Query Builder? I usually check the “Select distinct rows only” box and it fixes the problem but in this case there are fields that don’t match so it will be included in the dataset. Can I remove duplicates by just a specific field rather than an entire row using the query builder?

Thanks

Re: How to remove duplicates in SAS 8.3 EG Query Builder

ballardw — Wed, 18 Aug 2021 21:14:57 GMT

If there are other variables that you need how do you identify which values of the other variables that you want?

Re: How to remove duplicates in SAS 8.3 EG Query Builder

jimbarbour — Wed, 18 Aug 2021 22:51:16 GMT

@WorkUser wrote:

Can I remove duplicates by just a specific field rather than an entire row using the query builder?

Thanks

Not that I know of. Generally that's not how SQL works.

However, you can use Proc Sort with a NODUPKEY option.

PROC  SORT  DATA=Have  NODUPKEY;
    BY  ID, Date;
RUN;

In the above example, only duplicates on both ID and Date will be eliminated. If other columns in the dataset "Have" do not match, SAS will still drop a row if ID and Date match. However, be aware, that SAS will just take the first one that it encounters. If you have something like:
123 18AUG2021 Purchase

123 18AUG2021 Return

123 18AUG2021 Purchase

Only the first "Purchase" record would be retained.

Jim