Suppose you have sales data. You wish to find maximum sales generated by a product ID.
Method I .
Method II.
Method III.
Method IV.
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;
In the last method by using proc sql,want to know which is the table b on which we are applying an inner join
ReplyDeleteIt's the same table 'test'. We are applying self join here i.e. a table is joined to itself.
DeleteWhy are we applying self join here when a simple query can give the same results?
Deleteproc sql;
select id, product, max(sale) as max_sale from test
group by ID;
quit;
How to find out second maximum using proc sort only... Plz tell..Thank u..
ReplyDeleteproc sort data= test;
Deleteby 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.