LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2004, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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



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;

Back to: Top of message | Previous page | Main SAS-L page