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).

SAS rank.
proc rank data=tbl_in  out=tbl_out ties=mean descending;
by grp;
var amt;
ranks amt_rnk;
run;

Oracle SQL rank.
create table tbl_out
as with tbl as
(
select a.*,
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;

29 comments:

Unknown said...


in rank procedure i used ties=low instead of mean,
in sql instead of avg which function i can use...


Jhon anderson said...

Being an SAS programmer would be the correct career choice.The future of SAS technology is on positive note. It offers huge career prospects for talented professionals.
Thanks,
SAS Training chennai | SAS Courses in chennai

Priya said...

Updating with the latest technology and implementing it is the only way to survive in our niche. Thanks for making me this article. You have done a great job by sharing this content in here. Keep writing article like this.
SAS Training in Chennai | SAS Course in Chennai

Mathew Stephen said...

Testing is the important one for developing the application. Software testing is required to point out the defects and errors for development phrases. So, it offers a good career for talented professionals in software testing field.
Software Testing Training in chennai | Digital Marketing Training in Chennai | SEO Training in Chennai

Jhon Abraham said...

Learn SAS today.It has incredible breadth in IT industry.It's an application suite that can change, oversee and recover information from the assortment of root and perform measurable explanatory on it.
SAS Training in Chennai | SAS Course in Chennai | SAS Institutes in Chennai

Paul Miller said...

This is an awesome post.Really very informative and creative contents.Thanks to sharing these concept is a good way to enhance the knowledge.I like this site very much.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
DOTNET Training in Chennai | DOTNET course in Chennai

Rohit P said...

That was really nice content on data mining , worth reading them ,
nice post , do post more post

Thanks for share

best seo training , learn with our experts digital marketing training in bangalore seo training in bangalore

Arjun kumar said...

I appreciate you and I would like to read your next post.Thanks for sharing this post.
Informatica courses in Chennai | Informatica institutes in Chennai | Informatica Training in Chennai

Nikshitha S said...

Updating with the latest technology and implementing it is the only way to survive in our niche. Thanks for making me this article. You have done a great job by sharing this content in here. Keep writing article like this.
SAS Training in Chennai | SAS Course in Chennai

Priya R said...

Very interesting content which helps me to get the indepth knowledge about the technology. To know more detils about the course visit this website.
Digital marketing course in Chennai | Digital marketing training in Chennai

San Jairaj said...

Wonderful post!!Thank you for sharing your ideas.
Seo Training in Chennai | Seo Course in Chennai

Paul Miller said...

Software development life cycle is the crucial part in software testing. Testing is mainly important to develop the procedure, methods, documentation, etc., it helps for other developers also and it helps to identify the missing elements. Software Testing Training in Chennai offers a wonderful training with best MNC experts.
Thanks,
Testing Training in Chennai | Software Training institutes in Chennai

Amirtha rao said...

Your article about software testing is awesome. It helped me to understand the career prospects in software testing industry.
Regards,
Software testing training in chennai

https://www.bravetechnologies.in said...

I have read your blog its very informative. Keep Updating.erp software solutions chennai|erp in chennai

Paul Miller said...

Thanks for sharing these niche piece of knowledge. Here, I had a solution for my inconclusive problems & it’s really helps me a lot keep updates…
Selenium Training in Chennai|Selenium Training

Mohana M said...

Excellent Sharing. Want to become a Data scientist learn Data Science with R Training | Data Science with Python Training at GangBoard. SAS Training

jhansi joe said...

I would like to add your post in my RSS feed, can you update your blog regularly.
Selenium Training in Chennai|Selenium Training

Campus Events said...

Thanks for your great information! Its interesting and informative.Campus Events|Online Event Registration

siva siva said...

Superb! I found some useful information in your blog, it was awesome to read.Thank you for sharing.
College Events in chennai| Events Registration Online in chennai| National and International Upcoming Events| IEEE Paper Presentation

Evelin Harriet said...

Great information. Thank you for Sharing. (ERP) Enterprise Resource Planning Chennai

Siva Seo said...

I have read your blog its very Interesting. Thanks for sharing.
erp software solutions in chennai | erp software development chennai

teja said...

Thanks for sharing such informative guide on Tally ERP9 Training Institute in Chennai | Tally ERP Training Institute in Velachery.

Shivani Yuvanjalin said...

Thank you for this valuable information. I have got some important suggestions from it. Get your business to the next level in simple steps. ERP Providers in Chennai

sivanesan said...

The best thing is that your blog really informative thanks for your great information!
erp in chennai

Brave Technologies said...

Nice Blog. Thank you for Sharing. We are the best erp software providers in chennai. For more details call +91 9677025199.
erp solutions provider in chennai
erp for automotive industry chennai

Siva Seo said...

Thank you for this valuable information. I have got some important suggestions from it. I'm working in Brave Technologies Private Limited. web design in chennai

Fuzzy International said...

Thank you for your nice post. It is really useful. cashew nuts supplier in india | cashew nuts supplier in dubai

Yasmin Priya said...

Thanks for sharing this informative blog to our vision. Keep update your blog. If anyone interested to know more details about SAS then learn SAS Training in Bangalore will definitely helps you.

vignesjoseph said...

Great post!I am actually getting ready to across this information.If Learn for SAS this one of the site for SAS analytics. I am very happy to this commands.Also great blog here with all of the valuable information you have.Well done, it's a great knowledge.Selenium Training in Chennai