Need to Filter Top 10 records from multiple groups after Grouping By and Sorting in descending order

[sreevatsan1991]

Example Data set Attached.

I have the sum of revenue grouped By 6 groups QTR, Order_No Product_id, Company,State,Vertical.

So how do I filter top 10 rows for each group given the data set is sorted by Revenue Descending order .

Result would be Top 10 revenues for 6 groups = 60 records

How do i change the below query or write new query to achieve this. (Datastep also welcome)

Query:

PROC SQL;

   CREATE TABLE Top10 AS

   SELECT DISTINCT t1.QTR,

          t1.ORDER_NO,

          t1.Product_id,

          t1.Company,

          t1.STATE,

          t1.vertical,

          (SUM(t1.revenue)) FORMAT=DOLLAR15. AS revenue

      FROM source t1

      GROUP BY t1.QTR,

               t1.ORDER_NO,

               t1. Product_id,

               t1. Company,

               t1.STATE,

               t1.vertical

      ORDER BY revenue DESC;

QUIT;

[PaigeMiller]

So let's see if I understand...

Result would be Top 10 revenues for 6 groups = 60 records

But, the SQL is computing sum of revenue acrosss each combination of the all of the 6 group variables, which is a lot more than 6 groups; and not by each of the 6 group variables individually.

So, I guess I don't understand, your code doesn't seem to match the requested result. Please explain further. If you could provide a small example, that would help.

[novinosrin]

Hi @sreevatsan1991   I don't understand either.

What's the HAVE and the WANT.

Plz provide the above two "comprehensive samples"  explaining your requirement

[PaigeMiller]

I think a small example where you find the top three in each group would work for me, if we can find the top 3, we can find the top 10.

[novinosrin]

Sir, are you having an easy afternoon?

Ok, do you mean top 3 or 10 of what? I am not getting the logic of summing for each group and then asking for top 10. 

Should i assume, sum each by group and output the top 3 or 10 highest sums by group? 

[PaigeMiller]

I don't know the answer, because I don't understand the original problem.

But I do know, once I understand the original problem by means of a small example where I find the top 3, then I can also find the top 10.

[sreevatsan1991]

Hi ,

Sorry I am kind of having difficulty in making an example data. But I will try to explain in a simple way.

Top 3 or 10 it doesn't matter as its all the same.

I am trying to minimize the groups to understand.

Lets us consider the below data

Order	Customer	Revenue
1	A	20
1	A	10
2	B	15
3	A	20
4	C	12
5	A	25
6	B	50

Now Sum the revenue grouping by Order and Customer and sort by revenue decending

You will get

Order	Customer	SUM_of_Revenue
6	B	50
1	A	30
5	A	25
3	A	20
2	B	15
4	C	12

But I don't want this complete result, For example I want only Top revenue for each customer and Top revenue for each order

Expected result:

Order	Customer	SUM_of_Revenue
6	B	50
1	A	30
4	C	12
5	A	25

Result Has Top 3 Customers and Top 3 orders based on revenue.

Like so, I want Top 10 revenue in each groups.. 

[ballardw]

What does your SOURCE data table look like? We have a hard time telling what a top anything might be without the source data.

I have the sum of revenue grouped By 6 groups QTR, Product_id, Company,State,Vertical.

I count 5 groups in that statement, not 6.

[sreevatsan1991]

I am sorry,  I missed it while typing. Another group is order_no Please see the attached sample file in the attachment which is similar to original data.

[novinosrin]

data have;
input Order	Customer $	Revenue;
cards;
1	A	20
1	A	10
2	B	15
3	A	20
4	C	12
5	A	25
6	B	50
;
proc means data= have noprint nway;
class order customer;
var revenue;
output out=temp sum=;
run;


proc rank data=temp out=results(where=(rev<=3)) ties=low descending;
var revenue;
ranks rev;
run;

[novinosrin]

data have;
input Order	Customer $	Revenue;
cards;
1	A	20
1	A	10
2	B	15
3	A	20
4	C	12
5	A	25
6	B	50
;
proc sql outobs=3 nowarn;
create table want as
select Order,	Customer, sum(revenue) as sum
from have
group by order, customer
order by sum desc;
quit;

[sreevatsan1991]

It returned only 3 results.

My expected result :

Top 3 orders based on revenue

Top 3 Customers based on revenue 

Top 3 for each group

[novinosrin]

Your expected result doesn't seem to depict that. Can you clearly modify your expected result plz?

[sreevatsan1991]

Thanks a lot for your help. I really appreciate it.  Now I had changed the expected result  above.

[PaigeMiller]

Which of the replies in this thread now has the modified (and latest) version of the outputs? Please tell us the message number in this thread. Thank you.