BookmarkSubscribeRSS Feed
johnjinkim
Obsidian | Level 7

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!

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

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__
Jade | Level 19

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 712 views
  • 0 likes
  • 4 in conversation