Celko's median

This is Joe Celko's solution for calculating a median in SQL.

* From SQL For Smarties, Second Edition, by Joe Celko.  ;

* If there is no statistical median, the mean of the   ; 
* value just below and the value just above is used.   ;

data parts;
   input @1  pno    $2.
         @4  pname  $5.
         @10 color  $5.
         @16 weight 2.
         @19 city   $6.;
cards;
p1 Nut   Red   12 London
p2 Bolt  Green 17 Paris
p3 Cam   Blue  12 Paris
p4 Screw Red   14 London
p5 Cam   Blue  12 Paris
p6 Cog   Red   19 London
;;;; *****; run;

proc sql;

   select avg(distinct weight)
   from   (select f1.weight
           from   parts as f1,
                  parts as f2
           group  by f1.pno, f1.weight
           having sum(case when f2.weight = f1.weight then 1
                           else 0
                      end)
                  >=
                  abs(sum(case when f2.weight < f1.weight then 1
                               when f2.weight > f1.weight then -1
                               else 0
                          end)));

*****; quit;