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

Reply
Contributor
Posts: 29

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

[ Edited ]

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!

Super User
Super User
Posts: 7,686

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

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.

 

Respected Advisor
Posts: 4,130

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

[ Edited ]

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

 

 

Respected Advisor
Posts: 3,786

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

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;
Ask a Question
Discussion stats
  • 3 replies
  • 275 views
  • 0 likes
  • 4 in conversation