topic Re: Remove duplicates keeping obs in SAS Enterprise Guide

Remove duplicates keeping obs

anonymous_user — Wed, 05 Mar 2014 14:16:43 GMT

Hi everyone

I have tried to remove duplicates from my data set using sql distinct, max, etc but unfortunately it did not work.

Have:

COD_A	COD_B
11002	200
11002	3010
11002	15400

Want:

COD_A	COD_B
11002	200

It doesn’t matter with cod B will be chose in table want, what I really need is to update from tree observations, to just one but keeping cod_b in the result.

I have tried many SQL solutions, like group by, distinct etc...

Thanks in advance

Regards

Re: Remove duplicates keeping obs

art297 — Wed, 05 Mar 2014 14:38:55 GMT

Have you tried:

proc sql;

create table want as

select distinct cod_a,min(cod_b) as cod_b

from have

;

quit;

Re: Remove duplicates keeping obs

RW9 — Wed, 05 Mar 2014 14:41:32 GMT

By datastep:

data have;

attrib cod_a cod_b format=$10.;

cod_a="11002"; codb="200"; output;

cod_a="11002"; codb="3010"; output;

cod_a="11002"; codb="15400"; output;

run;

proc sort data=have out=want;

by cod_a cod_b;

run;

data want;

set want;

by cod_a;

if first.cod_a then output;

run;

Re: Remove duplicates keeping obs

anonymous_user — Thu, 06 Mar 2014 00:00:41 GMT

Thanks! It works perfectly!

Re: Remove duplicates keeping obs

anonymous_user — Thu, 06 Mar 2014 00:01:26 GMT

Thanks it works!!!