How to remove duplicates in SAS 8.3 EG Query Builder

[WorkUser]

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

[ballardw]

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

[jimbarbour]

---

@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