BookmarkSubscribeRSS Feed
user24
Obsidian | Level 7

I have a table as below

 

id      term    crh    sub   point

25    2014    5        mat   5

25    2014    5        mat   8

12    2011    7        sci     3

12    2011    7        mat   3

 

I want to pull if id term crh and sub same, but points are different. pull the highest points. So output as belo

 

id      term    crh    sub   point

 

25    2014    5        mat   8

12    2011    7        sci     3

12    2011    7        mat   3

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Simple proc sort nodupkey:

proc sort data=have out=want nodupkey;
  by id term crh sub descending point;
run;

Note the descending before point so highest is first.

Kurt_Bremser
Super User
proc sort data=have;
by id term crh sub point;
run;

data want;
set have;
by id term crh sub;
if last.crh;
run;

or

proc sql;
create table want as
select id, term, crh, sub, max(point) as point
from have
group by id, term, crh, sub
;
quit;
Astounding
PROC Star

This may be fastest, but requires a lot of memory if your data set is large:

 

proc summary data=have nway;

class id term crh sub;

var point;

output out=want (drop=_freq_ _type_) max=;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1157 views
  • 0 likes
  • 4 in conversation