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;

15 comments:

Anonymous said...


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


Unknown 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

Unknown 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

Unknown 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

Unknown 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

Unknown said...

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

Unknown 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

Brave Technologies said...

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

Unknown 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

Unknown said...

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

Unknown 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

Unknown said...

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

siva nesan 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.