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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.