Finding the second largest number across columns by grouping

[rvsidhu035]

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;

[Reeza]

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;

---

[rvsidhu035]

modify my program

[Reeza]

---

@rvsidhu035 wrote:

modify my program

---

I don't understand what you mean here.

[rvsidhu035]

any changes in my program kindly change

[Reeza]

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:

http://support.sas.com/documentation/cdl/en/procstat/66703/HTML/default/viewer.htm#procstat_univaria...

[Reeza]

And how are you handling ties?

[dgritt · Accepted solution]

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;

[rvsidhu035]

Select * from (select emp.*, dense_rank () over (order by sal desc) as rank from emp) where rank=2;

[Reeza]

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;

---

[rvsidhu035]

sorry some one in my group chat reply i dont get meaning behind that why i post in

[novinosrin]

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;

[Ksharp]

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;