DATA Step, Macro, Functions and more

Finding the second largest number across columns by grouping

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

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

Posted in reply to rvsidhu035

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;

View solution in original post


All Replies
Super User
Posts: 24,026

Re: Finding the second largest number across columns by grouping

Posted in reply to rvsidhu035

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

Posted in reply to rvsidhu035

@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

Posted in reply to rvsidhu035

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

Posted in reply to rvsidhu035

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

Posted in reply to rvsidhu035

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

Posted in reply to 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;
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

Posted in reply to 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;

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 293 views
  • 2 likes
  • 5 in conversation