Date:         Thu, 7 Oct 2004 22:22:04 -0400
Reply-To:     "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Subject:      Re: Discouraged with Version 9.1 of SAS
Comments: To: xlr82sas@AOL.COM

On Thu, 7 Oct 2004 15:08:00 -0700, Roger DeAngelis <xlr82sas@AOL.COM>
wrote:

>Hi All,
>
>  First I tried
>
>proc sql;
>        select median(age) from Sashelp.class;
>quit;
>
>But it returned each age.
>
>I realize there is no sense of order in a relational database and
>the median would be difficult to implement. Looks like the
>median is even less useful than the 'montonic' fuction

...

Hi,

IMHO, the median() function works just as it works in the data step --
calculating the median across variables instead of across observations.

Given that sql deals with (un-ordered) sets of records, the order-based
statistic like median is indeed non-trivial. Some dialects of sql (like ms
t-sql) has keyword "top," so that you can do something like:

   select top 2 Field1
   from Table1
   order by Field1 desc;

The traditional sql way, however, is to use reflective correlated join, as
shown below. HTH.

Cheers,
Chang

proc sql;

  create table one
   (v1 numeric, v2 numeric, v3 numeric)
  ;
  insert into one (v1, v2, v3)
  values (11,12,13)
  values (21,22,23)
  values (31,32,33)
  values (41,42,43)
  values (51,52,53)
  ;
  /* median function */
  select median(v1,v2,v3)
  from   one
  ;
  /*
  --------
      12
      22
      32
      42
      52
  */
  /* finding third largest value(median) of v2 -- sql way */
  select v2
  from   one as o1
  where  v2 = (select o1.v2
               from   one as o2
               where  o1.v2 <= o2.v2
               having count(*)=3)
  ;
  /*
        v2
  --------
        32
  */
  quit;