Is it really a bug or just an ambiguous reference? You could also assign the table alias to the outer reference. Why do you need nested (nested) sub queries?

proc sql; create table class_ranks2 as select b.* , 1+(select count(age) from sashelp.class where age > b.age) as rank ,1+(select count(distinct age) from sashelp.class where age > b.age) as dense_rank from sashelp.class b ;

On Fri, 9 Mar 2012 05:49:15 -0500, S=?ISO-8859-1?Q?=C3=B8ren?= Lassen <s.lassen@POST.TELE.DK> wrote:

>You can compute ranks with PROC SQL like this: >proc sql; > create table class_ranks as select > class.*, > 1+(select count(*) from (select age from sashelp.class) a > where age>class.age) as rank, > 1+(select count(*) from (select distinct age from sashelp.class) a > where age>class.age) as dense_rank > from > sashelp.class > ; > >The RANK expression counts the number of records with age higher than the >age from the current record; the DENSE_RANK expression calculates the >number of distinct age values greater than the age from the current record. > >The reason I put "(select age from sashelp.class) a" in the RANK expression >(and a similar inline query in the DENSE_RANK expression) is a bug in >SAS SQL: If you just write "sashelp.class a", the SAS SQL interpreter >understands "class.age" as what should be "a.age" or just "age" in the >subquery. > >If you are using Oracle or SQL server or similar, you can of course just >use the built-in ranking functions. > >Regards, >Søren