topic Re: Removing duplicates in SAS Programming

Removing duplicates

rosejolly — Wed, 03 Nov 2021 04:53:57 GMT

Hi All,

I want to merge 2 dataset using proc sql. Before merging I want to remove duplicates in one dataset and then merge . Can this be done under single proc statement in proc sql.

Re: Removing duplicates

SASKiwi — Wed, 03 Nov 2021 05:16:16 GMT

What do you mean by duplicates? Rows where all columns' values are the same or where certain key columns have the same values? SQL isn't very good at removing duplicate rows / key columns and there are better methods to do this.

Re: Removing duplicates

rosejolly — Wed, 03 Nov 2021 10:27:05 GMT

Duplicates here I meant is certain key columns have the same values. Thanks for the reply

Re: Removing duplicates

Kurt_Bremser — Wed, 03 Nov 2021 11:34:51 GMT

The blueprint is

proc sql;
create table want as
  select
    t1.*,
    t2.var,
    t2.varx
  from have1 t1 left join (
    select distinct id, var, varx
    from have2
  ) t2
  on t1.id = t2.id
;
quit;

To really be of help here, we need to see examples for your data and what you expect as a resulting table.

Post your incoming datasets as data steps with datalines, so we do not have to make guesses about your data.

Re: Removing duplicates

rosejolly — Wed, 03 Nov 2021 11:49:03 GMT

Thanks a lot, I think this will be helping out

Re: Removing duplicates

Kurt_Bremser — Wed, 03 Nov 2021 12:43:13 GMT

Keep in mind that, as always, the task defines the tool. Depending on the layout, size and contents of your data, other means than SQL might be more efficient, or it might even be that SQL can't do what you want (SQL only knows DISTINCT, but PROC SORT makes a difference between NODUPREC and NODUPKEY).