10-08-2015 09:46 AM - edited 10-08-2015 09:46 AM
I was wondering if there was a direct way in SQL to print out "n" number of observations by group.
data have; input service $ PaidAmount : dollar.; format PaidAmount dollar.; cards; Bicycling $1000 Bicycling $2000
For example, for the above data, I would want to print out the top 2 paid amounts, by service
proc sql outobs = 2 ;
select service, paidamount format=dollar24.2
group by service
order by paidamount desc ;
Obviously, the above code only prints out the top 2 paid amounts for bicycling since it's limited to outobs = 2. I'm looking more to get a table that is like this:
Service Paid Amount
Thanks in advance!
10-08-2015 10:10 AM
TBH its not really the type of thing you want to do with SQL. I mean you can, create the groups, then assign an incrementor, or do a subloop for max < outer loop max. But its far easier to do in datastep. Why do you need to use SQL? Better to use the right tool for the right job.
10-08-2015 10:13 AM - edited 10-08-2015 10:14 AM
Like @RW9 wrote this is something which is done easier using a SAS datastep.
In databases like Oracle there would be Analytics funtions allowing you to do such things - but that's nothing which has been implemented into the SAS SQL flavour.
10-08-2015 10:38 AM
I think a data step view is a good choice for this task
data have; input service :$10. PaidAmount : dollar. @@; format PaidAmount dollar.; cards; Bicycling $1000 Bicycling $2000 Bicycling $3000 Rowing $600 Rowing $900 Rowing $400 Rowing $500 ;;;; run; proc print; run; data _2perV / view=_2perV; set have; by service; if first.service then _c=0; _c+1; if _c le 2 then output; drop _c; run; proc sql; select * from _2perV; quit;