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

Alexxxxxxx — Fri, 11 Oct 2019 00:39:37 GMT

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.

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

Patrick — Fri, 11 Oct 2019 01:07:46 GMT

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.

topic get the largest number by group, keep all of the largest if they are same in SAS Programming

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

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