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!
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.
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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.