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;
Getting back to the original ask from @sreevatsan1991 , here are some resources to help.
And if you're using SAS Enterprise Guide, you'll find sample programs for Top N reports (including support for grouping) in the .\Sample\Code folder within the install directory.
Chris
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.
Hi @sreevatsan1991 I don't understand either.
What's the HAVE and the WANT.
Plz provide the above two "comprehensive samples" explaining your requirement
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.
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?
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.
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..
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.
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.
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;
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;
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
Your expected result doesn't seem to depict that. Can you clearly modify your expected result plz?
Thanks a lot for your help. I really appreciate it. Now I had changed the expected result above.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.