BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sreevatsan1991
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

Getting back to the original ask from @sreevatsan1991 , here are some resources to help.

 

https://video.sas.com/detail/video/4768437994001/sas-enterprise-guide-tip:-creating-a-grouped-top-n-...

 

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

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!

View solution in original post

21 REPLIES 21
PaigeMiller
Diamond | Level 26

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.

 

--
Paige Miller
novinosrin
Tourmaline | Level 20

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
Diamond | Level 26

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.

--
Paige Miller
novinosrin
Tourmaline | Level 20

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
Diamond | Level 26

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.

--
Paige Miller
sreevatsan1991
Obsidian | Level 7

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

 

OrderCustomerRevenue
1A20
1A10
2B15
3A20
4C12
5A25
6B50

 

 

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

 

You will get

 

OrderCustomerSUM_of_Revenue
6B50
1A30
5A25
3A20
2B15
4C12

 

 

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:

OrderCustomerSUM_of_Revenue
6B50
1A30
4C12
5A25

 

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

 

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

 

 

 

 

 

 

 

ballardw
Super User

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
Obsidian | Level 7

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
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Obsidian | Level 7

 

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
Tourmaline | Level 20

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

sreevatsan1991
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 5538 views
  • 2 likes
  • 7 in conversation