Date:         Mon, 12 Mar 2012 02:31:47 -0400
Reply-To:     Søren Lassen <s.lassen@POST.TELE.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Søren Lassen <s.lassen@POST.TELE.DK>
Subject:      Re: OT: Chance to Make SAS-L History: Did You Know That...
Comments: To: Tom Abernathy <tom.abernathy@GMAIL.COM>
Content-Type: text/plain; charset=ISO-8859-1

Tom,
The query you show works fine. When I say that there is a bug, it is
because this does not work (it should, and it would in e.g. Oracle):
proc sql;
  create table class_ranks2 as
  select class.*
       , 1+(select count(age) from sashelp.class a where age > class.age)
         as rank
       ,1+(select count(distinct age) from sashelp.class a
                 where age > class.age)
         as dense_rank
  from
    sashelp.class
  ;

What happens here is clearly a bug, or at least a severe deviation from
the SQL specification - the expression "age > class.age" gets resolved as
"a.age > a.age" which leads to all ranks being set to 1.

This is only tested in SAS 9.1, may have been fixed later.

Ther reason that I used the queries as first specified is just to get rid
of the alias on the CLASS table (the "b" in yoour example), thereby making
it easier to encapsulate the rank functionality in macros.

Regards,
Søren

On Fri, 9 Mar 2012 09:23:01 -0500, Tom Abernathy <tom.abernathy@GMAIL.COM>
wrote:

>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