topic Re: keeping tied values in a by group in SAS Programming

keeping tied values in a by group

PrinceAde — Mon, 02 Oct 2023 08:00:41 GMT

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.

Re: keeping tied values in a by group

PeterClemmensen — Mon, 02 Oct 2023 09:26:42 GMT

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;

Re: keeping tied values in a by group

JosvanderVelden — Mon, 02 Oct 2023 08:20:03 GMT

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?

Re: keeping tied values in a by group

PrinceAde — Mon, 02 Oct 2023 09:08:15 GMT

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

Re: keeping tied values in a by group

PrinceAde — Mon, 02 Oct 2023 09:18:03 GMT

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

Re: keeping tied values in a by group

PeterClemmensen — Mon, 02 Oct 2023 09:26:25 GMT

A simple typo 🙂

Re: keeping tied values in a by group

PrinceAde — Mon, 02 Oct 2023 09:29:34 GMT

Okay thank you very much.