Finally I made my own way and it really worked. Here is the solution to this problem.
Country <- c("India","US", "India","US","India","India","India","US","US","UK","US","India")
State <- c ("IM","UM","IM","UA","IM","IH","IH","UM","UM","UKM","UM","IH")
Sales <- c(1245,3212,345,3412,2423,3787,997,12121,12232,12113,45432,12121)
Revenue <- c(124523,121253,23565,565765,56321,121434,3431212,4532312,122345,232542,12345232,2312212)
SalesReport <- data.frame(Country,State,Sales,Revenue)
I want to rank my data set over Revenue partition by Country and State. So output will be like.
SELECT * RANK() OVER (PARTITION BY Country, State Order by Revenue DESC) as Rank FROM SalesReport GROUP BY Country, State
proc rank data=SalesReport out=SalesReportRanking
by Country State;
1. After creating dataset, I am creating a KEY which is nothing but concatenate of all columns which are needed for partition by.
2. First I make the dataset ordered by these columns.
3. And now making a KEY “ID”
4. Now ranking the dataset by ID. -x for Descending order.
5. Arranging the dataset for better look.
6. Nullifying the ID (No one should know your trick :P) and view it.
SalesReport <- SalesReport[order(Country,State),]
row.names(SalesReport) <- NULL
SalesReport$ID <- paste(SalesReport[,1],SalesReport[,2])
SalesReportRanking <- transform(SalesReport, Rank = ave (Revenue, ID, FUN = function(x)
rank (-x, ties.method ="min")))
SalesReportRanking <- SalesReportRanking[order(SalesReportRanking$ID,SalesReportRanking$Rank),]
SalesReportRanking$ID <- NULL
The above code can be used for a single column as well. Just replace ID with your column name.
Compare your result with SAS output. Here we go. It matches if not please comment below. I hope it helped you a little bit. Thanks for reading.
Another important queries for this solution:
1. How to use Rank() in SQL Server
2. How to use Rank() in R
3. How to emulate SQL “partition by” in R?
4. How to partition when ranking on a particular column?