DATA Step, Macro, Functions and more

pull the hishest number

Reply
Contributor
Posts: 33

pull the hishest number

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

Super User
Super User
Posts: 7,942

Re: pull the hishest number

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.

Super User
Posts: 7,768

Re: pull the hishest number

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,499

Re: pull the hishest number

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;

Ask a Question
Discussion stats
  • 3 replies
  • 431 views
  • 0 likes
  • 4 in conversation