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?
create table class_ranks2 as
, 1+(select count(age) from sashelp.class where age > b.age)
,1+(select count(distinct age) from sashelp.class where age > b.age)
On Fri, 9 Mar 2012 05:49:15 -0500, S=?ISO-8859-1?Q?=C3=B8ren?= Lassen
>You can compute ranks with PROC SQL like this:
> create table class_ranks as select
> 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
>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
>If you are using Oracle or SQL server or similar, you can of course just
>use the built-in ranking functions.