Re: SQL Median Function Should be Critical Alert Tech Support

Yes Sig, that is exactly right. The present implementation of the median
function in PROC SQL is exactly like the data step median function; it only
operates on a list of values within a *single* row/observation at one time.
It does *NOT* compute a median value across all selected rows for a given
column, whether or not GROUP BY criteria is specified.

s/KAM


----- Original Message -----
From: "Sigurd Hermansen" <HERMANS1@WESTAT.com>
To: "Kevin Myers" <KMyers1@CLEARWIRE.NET>; <sas-l@LISTSERV.UGA.EDU>
Sent: Friday, August 18, 2006 12:01
Subject: RE: SQL Median Function Should be Critical Alert Tech Support Issue


> Kevin:
> Are you saying that the new median function will in PROC SQL simply
compute the median for a dataset and not produce medians by group or for the
yield of a query (as subset by WHERE clauses or join conditions)? In that
case I definitely agree that the SQL median function should be consistent
with other summary functions.
> Sig
>
> ________________________________
>
> From: owner-sas-l@listserv.uga.edu on behalf of Kevin Myers
> Sent: Fri 8/18/2006 9:59 AM
> To: SAS Users
> Subject: SQL Median Function Should be Critical Alert Tech Support Issue
>
>
>
> Hello folks,
>
> Recently SI apparently added the median function to the list of functions
that can be used in PROC SQL. Unfortunately, it appears that they simply
allowed the data step median function to be used in PROC SQL, rather than
creating a true SQL summary function. In my opinion this needs to be
changed *IMMEDIATELY* before anyone really starts using the median function
within PROC SQL in its present state. This needs to be treated as an ALERT
tech support issue by SI. There are two reasons for this:
>
> 1. The median function really isn't very useful within PROC SQL in its
present implementation. Almost anyone who wants to use a median function
within PROC SQL is going to want and expect median to be a true SQL summary
function.
>
> 2. Perhaps even more importantly, median IS already a true summary
function in every other SQL capable database where it is implemented that I
know of. By allowing median as a non-summary function, SI is probably
violating the latest ANSI SQL standards (I have *not* checked) and in any
case is certainly making the SAS SQL dialect even less compatible with other
SQL capable systems.
>
> Again, this is a CRITICAL issue, because it needs to be corrected *BEFORE*
any folks out there start using median in its present incarnation, and
create a compatibilty issue that SI can't fix because it would break too
much existing code.
>
> If any of you disagree, then please let me know. Otherwise, I would
appreciate it if any of you that have the time would chime in and add your
support to this thread, so I can send this to SI as more than just my own
rant.
>
> Thanks,
> s/KAM
>