4 ways to find maximum value in a group with SAS

Suppose you have sales data. You wish to find maximum sales generated by a product ID.

data test;
input ID product $  Sale;
1 A 4.5
1 B 1
1 C 4.25
1 D 3
1 E  4.75
2 F 4.9
2 G  4.1
2 H 3
2 I 4
2 J  4.05

Method I . 
proc sort data = test;
by ID descending sale;

proc sort data = test nodupkey;
by ID;

Method II. 

proc sort data = test;
by ID descending sale;

data test1;
set test;
by ID;
If first.ID then output test1;

Method III. 
proc sort data = test;
by ID;

proc rank data=test out= test2(where=(sale_rank=1))  ties=low descending;
by ID;
var sale;
ranks sale_rank;

Method IV. 
proc sql noprint;
create table t1 as
select * from test a inner join
(select ID, max(sale) as maxsale
from test
group by ID) b
on a.ID = b.ID and a.sale = b.maxsale;

SAS Tutorials : 100 Free SAS Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*
Related Posts:
4 Responses to "4 ways to find maximum value in a group with SAS"
  1. In the last method by using proc sql,want to know which is the table b on which we are applying an inner join

    1. It's the same table 'test'. We are applying self join here i.e. a table is joined to itself.

    2. Why are we applying self join here when a simple query can give the same results?

      proc sql;
      select id, product, max(sale) as max_sale from test
      group by ID;

  2. How to find out second maximum using proc sort only... Plz tell..Thank u..


Next → ← Prev