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;
cards;
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
;
run;

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

proc sort data = test nodupkey;
by ID;
run;

Method II. 

proc sort data = test;
by ID descending sale;
run;

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

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

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

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;
quit;

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:
6 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

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

      Delete
    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;
      quit;

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

    ReplyDelete
    Replies
    1. proc sort data= test;
      by id descending sale;
      run;

      data test1;
      set test;
      by id;
      if first.id=1 then count=1;
      else count+1;
      if count=2 then output test1;
      run;

      I hope this will work.

      Delete
  3. This comment has been removed by the author.

    ReplyDelete

Next → ← Prev