If you want to do advanced SQL processing, you should buy and read Joe Celko's book SQL For Smarties, available in hardcopy and on Kindle. Your task is advanced because it requires imposing an order on table rows.
Here's an example from his book of how you would calculate a median:
* 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;
The answer returned is 13.
Tasks requiring sequencing are often less efficient in a descriptive language like SQL than they are in a procedure language like the data step, and frequently harder to understand. (Median is a function in SAS SQL now, but I'm not certain that it always has been.)
... View more
