Prev: Deleting files older then a certain age from a folder
Next: Running PL/SQL through PROC DBLOAD
From: kriscrow on 19 Mar 2010 15:23
I have the following sql code that groups records by yr, month and a
few other classification variables and orders by descending sum of
variable "amount".

What I'd like to do is keep the top 3 records(equivalent to top 3 amts
after ordering) in each category as individual records and group the
remaining records into a single record(sum amounts for non top 3). I
know I can accomplish this easily enough with a proc means but is it
possible to do using SQL?

proc sql;
create table metrics2 as
select year(date_end) as yr, month(date_end) as mth, name, type,
type2, city,
sum(amt) as tot_amt
from end_data
where year(date_end)>=2009 and name ne ' '
group by 1,2,3,4,5,6
order by 1,2,3,4,5,tot_amt desc
;
quit;
From: Patrick on 19 Mar 2010 20:36
I guess what you could do is creating an additional variable for the
'group by' statement.

This variable should contain a distinct value for the top 3 rows and
an identical value for all other rows.

Search the forum with "top sql" - there are already quite a few
threads around identifying "top" rows.

I would expect that you'll end up with a sub-select and a case
statement.

HTH
Patrick
 | 
Pages: 1
Prev: Deleting files older then a certain age from a folder
Next: Running PL/SQL through PROC DBLOAD