Remove duplicate by var ID but keep the highest value of their var age

[Didi_b]

Hello,

I am trying to remove duplicate value by var ID but I also need to the highest value of the var kid_age for the obs that have duplicate. Is it possible? Could someone help me please?

I need to have only 1 Obs for each ID but also have the highest value for the var kid_age for the ID that listed more than once in my data.

This is the SAS programme I used to remove duplicate:

Proc sort data=Work.base
out=clean   
dupout=dups   
nodupkey;
by ID;
run;

data example :

ID name age kid_age
XD546 Alex 18 1
GT786 Yvan 35 10
PE358 Sami 25 5
LK523 Yan 40 18
LK523 Yan 40 15
UY841 Doris 28 14
UY841 Doris 28 8
PQ153 Suzi 38 16
PQ153 Suzi 38 8
PQ153 Suzi 38 3

[Reeza · Accepted solution]

Double sort or use SQL to control the aggregations more. It really depends on whether you can assume that Name and Age will be constant along with ID. 

See this slightly modified example data process (Suza versus Suzi).

SQL offers you a little more control/options.

data example;
input ID $ name $ age kid_age;
cards;
XD546 Alex 18 1
GT786 Yvan 35 10
PE358 Sami 25 5
LK523 Yan 40 18
LK523 Yan 40 15
UY841 Doris 28 14
UY841 Doris 28 8
PQ153 Suzi 38 16
PQ153 Suza 38 8
PQ153 Suzi 38 3
;;;;
run;

proc sort data=example;
by id name age descending kid_age ;
run;


Proc sort data=example
out=clean   
dupout=dups   
nodupkey;
by ID;
run;

proc print data=example;run;

proc print data=clean;run;

proc sql;
create table clean_want as
select id, max(name) as name, max(age) as age, max(kid_age) as kid_age
from example
group by id;
quit;

proc print data=clean_want;
run;

[Michelleazevedo]

Proc sort data=Work.base;
by ID descending kid_age;
run;

Proc sort data=Work.base
out=clean   
dupout=dups   
nodupkey;
by ID;
run;

if you want just duplicated values:

Proc sort data=Work.base
out=dups   
nouniquekey;
by ID;
run;

with dupout you keep just deleted values on dups.