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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.