data req;
input id$ salary;
datalines;
001 2000
002 3000
003 4000
001 3000
001 5000
004 2000
003 1000
002 5000
004 1000
run;
how to get second hightest salary group by id;
*correct program;
proc sql;
create table want as select*,largest(2,salary)
from req
group by id;
quit;
proc sort data=req;
by id descending salary;
run;
data want;
set req;
by id;
retain cnt;
if first.id then cnt = 0;
cnt = cnt + 1;
if cnt = 2;
drop cnt;
run;
LARGEST() and functions in general, operate on a row of data, not a column of data.
@rvsidhu035 wrote:
data req; input id$ salary; datalines; 001 2000 002 3000 003 4000 001 3000 001 5000 004 2000 003 1000 002 5000 004 1000 run; how to get second hightest salary group by id; *correct program; proc sql; create table want as select*,largest(2,salary) from req group by id; quit;
modify my program
@rvsidhu035 wrote:
modify my program
I don't understand what you mean here.
any changes in my program kindly change
SQL doesn't have the concept of ordering of rows so it doesn't work to find things like second largest well.
See the examples from the documentation:
proc sort data=req;
by id descending salary;
run;
data want;
set req;
by id;
retain cnt;
if first.id then cnt = 0;
cnt = cnt + 1;
if cnt = 2;
drop cnt;
run;
Select * from (select emp.*, dense_rank () over (order by sal desc) as rank from emp) where rank=2;
SAS SQL does not support OVER or have the DENSE_RANK function. If you're using SQL pass through, post the question in the applicable forum, ie Oracle or MS SQL since this would not be a SAS question.
@rvsidhu035 wrote:
Select * from (select emp.*, dense_rank () over (order by sal desc) as rank from emp) where rank=2;
data req;
input id$ salary;
datalines;
001 2000
002 3000
003 4000
001 3000
001 5000
004 2000
003 1000
002 5000
004 1000
run;
proc sort data=req out=_req;
by id descending salary;
run;
proc rank data=_req out=results(where=(salaryRank=2)) ties=low descending;
by id;
var salary ;
ranks salaryRank ;
run;
data req;
input id$ salary;
datalines;
001 2000
002 3000
003 4000
001 3000
001 5000
004 2000
003 1000
002 5000
004 1000
;
run;
proc sql;
select *
from (select * from req except (select * from req group by id having salary=max(salary)))
group by id
having salary=max(salary);
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.