keeping tied values in a by group

[PrinceAde]

Hi everyone, I need help with this data.

data m_data;
input group name $ val;
datalines;
1 pet 10
1 pet 15
2 jane 20
2 john 21
3 james 30
3 james 30
;

proc sort data=m_data;
by group name descending val;
run;

data tied_values;
set m_data;
by group name descending val;

if first.name;
run;

proc print data=tied_values;
run;

I'm trying to keep the first.name, in addition I want to keep values that are tied in the by group such that my final dataset will give this;

1 pet 15
2 jane 20
2 john 21
3 james 30
3 james 30

Using the first.name alone will produce this;

1 pet 15
2 jane 20
2 john 21
3 james 30

Please what concept can I use to get this done.

Thanks.

[PeterClemmensen · Accepted solution]

I think this is what you want

data m_data;
input group name $ val;
datalines;
1 pet 10
1 pet 15
2 jane 20
2 john 21
3 james 30
3 james 30
;

proc sql;
   create table want as
   select *
   from m_data
   group by group, name
   having max(val) = val
   order by group
   ;
quit;

[PrinceAde]

This works. If I may ask, what's the logic behind listing the group variable twice in the order by statement?

Can this same code/logic be implemented in a data step as well?

Thank you @JosvanderVelden 

[PeterClemmensen]

A simple typo 🙂

[PrinceAde]

Okay thank you very much.

[JosvanderVelden]

The row/observation "3 james 30" is present twice in the input/source dataset so sort with first logic will not allow a result as you wish.
What is the logic behind keeping these 2 exactly the same observations/rows?

[PrinceAde]

I'm trying to select max value(val) per group per name with the condition to keep tie values(val)