topic How to get TOP 10 highest transacation using proc sql in New SAS User

How to get TOP 10 highest transacation using proc sql

animesh123 — Tue, 31 Jan 2023 13:42:39 GMT

I have this small code .Could anyone please help me how to limit TOP 10 highest transaction.

proc sql;
select make,origin,invoice
from sashelp.cars
group by make,origin
order by make, invoice desc;
run;
quit;

Re: How to get TOP 10 highest transacation using proc sql

PaigeMiller — Tue, 31 Jan 2023 16:06:21 GMT

Question is asked often:

https://communities.sas.com/t5/New-SAS-User/Select-top-N-from-each-group-in-proc-sql/m-p/852214

Generally, I think you would be better off using PROC RANK that SQL as PROC RANK allows you to handle ties in different ways. And just plain old PROC SORT is much easier to code than using SQL.

Re: How to get TOP 10 highest transacation using proc sql

Ksharp — Wed, 01 Feb 2023 11:40:29 GMT

proc sql;
create table want as
select a.make,a.origin,a.invoice
from sashelp.cars as a left join sashelp.cars as b
 on a.make=b.make and a.origin=b.origin and a.invoice<=b.invoice
group by a.make,a.origin,a.invoice
having count(distinct b.invoice) in (1:10)
order by make, invoice desc;
quit;