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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.