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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: