Tuesday, June 05, 2012
SAS proc rank and its Oracle SQL equivalent
The following SAS rank and Oracle SQL produce the same results. In SAS, when there are ties, the average rank is used. To do the same in Oracle, we calculate the unique row numbers and then its average based on group id and variable (amt).
proc rank data=tbl_in out=tbl_out ties=mean descending;
Oracle SQL rank.
create table tbl_out
as with tbl as
row_number() over(partition by grp order by amt desc) amt_rnk0 from tbl_in a
) select a.*, (avg( amt_rnk0 ) over(partition by grp, amt )) amt_rnk
from tbl a;