topic Re: Finding the second largest number across columns by grouping in SAS Programming

Finding the second largest number across columns by grouping

rvsidhu035 — Tue, 03 Apr 2018 15:49:09 GMT

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;

Re: Finding the second largest number across columns by grouping

Reeza — Tue, 03 Apr 2018 15:55:11 GMT

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;

Re: Finding the second largest number across columns by grouping

rvsidhu035 — Tue, 03 Apr 2018 15:57:32 GMT

modify my program

Re: Finding the second largest number across columns by grouping

Reeza — Tue, 03 Apr 2018 15:58:46 GMT

@rvsidhu035 wrote:

modify my program

I don't understand what you mean here.

Re: Finding the second largest number across columns by grouping

rvsidhu035 — Tue, 03 Apr 2018 16:00:01 GMT

any changes in my program kindly change

Re: Finding the second largest number across columns by grouping

dgritt — Tue, 03 Apr 2018 16:00:55 GMT

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;

Re: Finding the second largest number across columns by grouping

Reeza — Tue, 03 Apr 2018 16:01:54 GMT

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

Re: Finding the second largest number across columns by grouping

Reeza — Tue, 03 Apr 2018 16:02:17 GMT

And how are you handling ties?

Re: Finding the second largest number across columns by grouping

rvsidhu035 — Tue, 03 Apr 2018 16:05:13 GMT

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

Re: Finding the second largest number across columns by grouping

Reeza — Tue, 03 Apr 2018 16:07:17 GMT

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;

Re: Finding the second largest number across columns by grouping

rvsidhu035 — Tue, 03 Apr 2018 16:09:08 GMT

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

Re: Finding the second largest number across columns by grouping

novinosrin — Tue, 03 Apr 2018 16:25:16 GMT

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;

Re: Finding the second largest number across columns by grouping

Ksharp — Wed, 04 Apr 2018 13:36:30 GMT

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;