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?
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;
Thank you, it worked
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.