Date:         Wed, 2 Sep 2009 15:02:10 -0700
Reply-To:     xlr82sas <xlr82sas@AOL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         xlr82sas <xlr82sas@AOL.COM>
Organization: http://groups.google.com
Subject:      Re: SQL: How to Get Median and Percentiles
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On Sep 2, 1:55 pm, Haris <karoval...@gmail.com> wrote:
> Is there a way to get output for median or other column percentiles in
> an SQL statement?  I know there are other SAS procedures like PROC
> MEAN or PROC UNIVARIATE that can do this easily; however, for my
> purposes I  need to get it out of an SQL statement.
>
> Any help would be much appreciated.  Thanks.

This is a pet peave of mine.

The median function in SQL operates differently than mean, std, min,
max.... For some reason it is a noop when only one variable is used,
ie
Maybe SAS should issue a warning when using the median with just one
argument.

 proc sql;
  select
    median(age) as medage
  from
    sashelp.class
  group
    by sex
;quit;

SEX    medage
-------------
F          14
F          12
F          11
F          13
F          14
F          15
F          12
F          13
F          15
M          16
M          12
M          14
M          12
M          15
M          14
M          13
M          11
M          15
M          12

Isn't that nice. I wish SAS would pull some actors  from IML/STAT
Studio products and Starship Eterprise products and get them working
on basic language extensions. This would help differentiate SAS from R
and other languages. Many of my programs are twice as long as
necessary because I cannot get order stats out of SQL. I know there is
no sense of order in SQL and the 'order by' disrupted the SQL purists.
Incidentally monotonic() should be documented or removed. Its been out
ther for years.