Date: Fri, 9 Mar 2012 09:23:01 -0500
Reply-To: Tom Abernathy <tom.abernathy@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Tom Abernathy <tom.abernathy@GMAIL.COM>
Subject: Re: OT: Chance to Make SAS-L History: Did You Know That...
Content-Type: text/plain; charset=ISO-8859-1
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.