```Date: Thu, 7 Oct 2004 22:22:04 -0400 Reply-To: "Chang Y. Chung" Sender: "SAS(r) Discussion" From: "Chang Y. Chung" 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 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; ```

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