Solved
Contributor
Posts: 56

# Finding the second largest number across columns by grouping

``````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;``````

Accepted Solutions
Solution
‎04-03-2018 12:05 PM
Contributor
Posts: 31

## Re: Finding the second largest number across columns by grouping

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;

All Replies
Super User
Posts: 24,026

## Re: Finding the second largest number across columns by grouping

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;``````

Contributor
Posts: 56

## Re: Finding the second largest number across columns by grouping

modify my program

Super User
Posts: 24,026

## Re: Finding the second largest number across columns by grouping

@rvsidhu035 wrote:

modify my program

I don't understand what you mean here.

Contributor
Posts: 56

## Re: Finding the second largest number across columns by grouping

any changes in my program kindly change

Super User
Posts: 24,026

## Re: Finding the second largest number across columns by grouping

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...

Super User
Posts: 24,026

## Re: Finding the second largest number across columns by grouping

And how are you handling ties?
Solution
‎04-03-2018 12:05 PM
Contributor
Posts: 31

## Re: Finding the second largest number across columns by grouping

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;

Contributor
Posts: 56

## Re: Finding the second largest number across columns by grouping

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

Super User
Posts: 24,026

## Re: Finding the second largest number across columns by grouping

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;

Contributor
Posts: 56

## Re: Finding the second largest number across columns by grouping

sorry some one in my group chat reply i dont get meaning behind that why i post in
Super User
Posts: 2,068

## Re: Finding the second largest number across columns by grouping

``````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;``````
Super User
Posts: 10,850

## Re: Finding the second largest number across columns by grouping

``````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;``````
☑ This topic is solved.