Quartz | Level 8

## 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;``````
1 ACCEPTED SOLUTION

Accepted Solutions
Obsidian | Level 7

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

12 REPLIES 12
Super User

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

Quartz | Level 8

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

modify my program

Super User

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

@rvsidhu035 wrote:

modify my program

I don't understand what you mean here.

Quartz | Level 8

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

any changes in my program kindly change

Super User

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

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

And how are you handling ties?
Obsidian | Level 7

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

Quartz | Level 8

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

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

Quartz | Level 8

## 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
Tourmaline | Level 20

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

## 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;``````
Discussion stats
• 12 replies
• 5476 views
• 2 likes
• 5 in conversation