topic Re: How to print out "N" observations for different groups using SQL in SAS Programming

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

johnjinkim — Thu, 08 Oct 2015 13:46:29 GMT

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!

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

RW9 — Thu, 08 Oct 2015 14:10:04 GMT

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.

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

Patrick — Thu, 08 Oct 2015 14:14:44 GMT

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

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

data_null__ — Thu, 08 Oct 2015 14:38:29 GMT

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;