I have input
Product ID | Group | Revenue | Quantity |
0 | C | 60 | 10 |
1 | C | 50 | 30 |
2 | C | 90 | 20 |
3 | B | 150 | 30 |
4 | B | 80 | 20 |
5 | B | 80 | 10 |
6 | A | 100 | 15 |
7 | A | 200 | 15 |
8 | A | 150 | 30 |
I would like to have independent rank by Group for revenue and quantity (lower rank means better), the expected output will be:
Product ID | Group | Revenue | Quantity | rank revenue | rank quantity |
0 | C | 60 | 10 | 2 | 3 |
1 | C | 50 | 30 | 3 | 1 |
2 | C | 90 | 20 | 1 | 2 |
3 | B | 150 | 30 | 1 | 1 |
4 | B | 80 | 20 | 2 | 2 |
5 | B | 80 | 10 | 2 | 3 |
6 | A | 100 | 15 | 3 | 2 |
7 | A | 200 | 15 | 1 | 2 |
8 | A | 150 | 30 | 2 | 1 |
What I have in my mind is to rank independently each column like for revenue:
proc sql;
create table sort_revenue as
select * from mytable group by Group order by revenue desc
;quit;
data rank_revenue;
set sort_revenue;
by group;
if first.group then rank_revenue=1;
else rank_revenue+1;
run;
and do the same for rank quantity.
I wonder any more efficient way to do both columns at the same time?
Found it (from https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p1xzpoijq32wbsn1gr6g5cx3emsx.htm)
proc rank data=mytable out=myrank ties=low descending;
by Group notsorted;
var Revenue Quantity;
ranks Revenue_rank Quantity_rank;
run;
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.