Desktop productivity for business analysts and programmers

Remove duplicates keeping obs

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Remove duplicates keeping obs

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_ACOD_B
11002200
110023010
1100215400

Want:

COD_ACOD_B
11002200

     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


Accepted Solutions
Solution
‎03-05-2014 09:41 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Remove duplicates keeping obs

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;

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,284

Re: Remove duplicates keeping obs

Have you tried:

proc sql;

  create table want as

    select distinct cod_a,min(cod_b) as cod_b

      from have

  ;

quit;

Occasional Contributor
Posts: 11

Re: Remove duplicates keeping obs

Thanks it works!!!

Solution
‎03-05-2014 09:41 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Remove duplicates keeping obs

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;

Occasional Contributor
Posts: 11

Re: Remove duplicates keeping obs

Thanks! It works perfectly!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 327 views
  • 3 likes
  • 3 in conversation