How to print out "N" observations for different groups using SQL

[johnjinkim]

Hi all,

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 
Bicycling $3000
Rowing    $600
Rowing    $900
Rowing    $400
Rowing    $500 
;
run;

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

from have

group by service

order by paidamount desc ;

quit ;

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

Bicycling    $3000

Bicycling    $2000

Rowing       $900

Rowing       $600

Thanks in advance!

[RW9]

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.

[Patrick]

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.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174

[data_null__]

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;