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
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.