topic Remove duplicate observations in SAS Programming

Remove duplicate observations

lca — Tue, 13 Sep 2011 17:08:21 GMT

Hello!

SAS newbie here 🙂

I have table similar to this:

ID	Version	Value
100	0	10
100	1	11
200	0	20
200	1	21
200	2	22
200	3	21
200	4	20
300	0	40
300	1	41
300	2	43

I want to extract data and make a new table with only unique IDs with the highest version and corresponding value.

Like this:

ID	Version	Value
100	1	11
200	4	20
300	2	43

This is may be easy, but I'm new to SAS 🙂

Any suggestions?

Remove duplicate observations

art297 — Tue, 13 Sep 2011 17:27:21 GMT

There are a number of ways. Here is one using proc sort.

proc sort data=have;

by id descending version;

run;

proc sort data=have out=want nodupkey;

by id;

run;

Remove duplicate observations

art297 — Tue, 13 Sep 2011 17:30:38 GMT

You could also do it using proc sql:

proc sql noprint;

create table want as

select *

from have

group by id

having version eq max(version)

;

quit;

Remove duplicate observations

DLing — Tue, 13 Sep 2011 20:50:22 GMT

The data set also appears to be sorted by ID and Version, in which case, you can use:

proc sort data=have; *--- only needed if not already sorted by ID and Version;

by id version;

run;

data want;

set have;

by id;

if last.id; *--- highest version for each id due to sorting;

run;

Use which ever method you find more natural and intuitive to you. Art's methods all work.

Remove duplicate observations

lca — Wed, 14 Sep 2011 21:36:27 GMT

Thank you, it worked

ID	Version	Value
100	0	10
100	1	11
200	0	20
200	1	21
200	2	22
200	3	21
200	4	20
300	0	40
300	1	41
300	2	43

ID	Version	Value
100	0	10
100	1	11
200	0	20
200	1	21
200	2	22
200	3	21
200	4	20
300	0	40
300	1	41
300	2	43

ID	Version	Value
100	0	10
100	1	11
200	0	20
200	1	21
200	2	22
200	3	21
200	4	20
300	0	40
300	1	41
300	2	43