4 ways to find maximum value in a group with SAS


SAS Analytics : Practical SAS, Statistics & Analysis Course

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;
Coursera Data Science

SAS Tutorials : 100 Free SAS Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

3 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. How to find out second maximum using proc sort only... Plz tell..Thank u..

    ReplyDelete

Next → ← Prev