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;
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.
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.