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
If there are other variables that you need how do you identify which values of the other variables that you want?
@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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: