Rank multiple columns

vietlinh12hoa — Fri, 04 Jun 2021 08:49:38 GMT

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?

Re: Rank multiple columns

vietlinh12hoa — Fri, 04 Jun 2021 08:50:09 GMT

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;

topic Rank multiple columns in SAS Programming

Rank multiple columns

Re: Rank multiple columns