get the largest number by group, keep all of the largest if they are same

[Alexxxxxxx]

Hello,

I expect to get the largest DEGREE per 'HRM_L2, TYPE' group. If there are two same largest number of 'DEGREE' then I expect to keep all of them(even they are the missing value). 

by using the following table as an example,

HRM_L2	PERSON_CTRY_CODE	TYPE	DEGREE
AB		1
AB	US	1	6
AB	GB	1	2
CC	US	2
CC	GB	2
DF	CA	4	2
DF	GB	4	2
DF	DE	4	2

I expect to get 

HRM_L2	PERSON_CTRY_CODE	TYPE	DEGREE	note
AB	US	1	6	it is the largest number of 'AB 1' group
CC	US	2		I expect to keep both of them even they are the missing value
CC	GB	2
DF	CA	4	2	I expect to keep all of them if the largest number of DEGREE of this group are the same
DF	GB	4	2
DF	DE	4	2

Could you please give me some suggestion about this?

thanks in advance.

[Patrick · Accepted solution]

Look into Proc Rank.

Or here a data step approach:

proc sort data=sashelp.cars out=work.cars;
  by make type DESCENDING cylinders;
run;

data want;
  set cars;
  by make type;
  retain _cylinders;
  if first.type then _cylinders=cylinders;
  else if _cylinders ne cylinders then delete;
run;

Or SQL:

proc sql;
  create table want as
  select *
  from sashelp.cars
  group by make, type
  having max(cylinders)=cylinders
  ;
quit;

And last but not least:

If you're after code for your sample data then please provide such sample data via a working and tested SAS data step posted into a code window. Help us to help you.