Date:         Wed, 8 Mar 2000 15:04:19 -0700
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: Celko's median using SQL
Comments: To: xbguan@YAHOO.COM
Content-Type: text/plain; charset=us-ascii

Without seeing the code and data, it would be hard to say why your query failed.

I'm not greatly surprised that it took a long time, though:  it's doing an unoptimized
Cartesian join, and it's doing a lot of comparisons.  Did you have a lot of observations?
One reason that a median function isn't more widely available in SAS is that it's
computationally expensive.

Not sure what you mean about AVG and SUM; MEAN and SUM are available as
SAS functions.



--
JackHamilton@FirstHealth.com
Development Manager, Technical Group
METRICS Department, First Health
West Sacramento, California USA


>>> <xbguan@YAHOO.COM> 03/08/2000 12:31 pm >>>
I tried the similar query on my own table. It took several hours to run
and return 0 rows. Any idea?

Also, is it easy to create a function similar to AVG, SUM, etc.?

Thanks a lot!
Xiaobin

In article <8a389f$q4j$1@nnrp1.deja.com>,
  joe_celko@my-deja.com wrote:
>
> >> From SQL For Smarties: Advanced SQL Programming, Second Edition, by
> Joe Celko, Morgan Kaufman Publishers, 2000 ...
>    SELECT AVG(DISTINCT weight)
>      FROM (SELECT ...
> <<
>
> I actually think that SELECT AVG(weight)... is a better choice because
> it will do a weighted median.  For example, if the central values are
> (2,2,3,3,3) the SELECT AVG(DISTINCT) version will give you (5/2) =
2.5,
> while the SELECT AVG() version will give you 13/5 = 2.6, which is a
> better measure of central tendency.
>
> --CELKO--
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>


Sent via Deja.com http://www.deja.com/
Before you buy.