BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rvsidhu035
Quartz | Level 8

 

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
dgritt
Obsidian | Level 7

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

12 REPLIES 12
Reeza
Super User

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;

 

Reeza
Super User

@rvsidhu035 wrote:

modify my program


I don't understand what you mean here.

rvsidhu035
Quartz | Level 8

any changes in my program kindly change

Reeza
Super User

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
Super User
And how are you handling ties?
dgritt
Obsidian | Level 7

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
Quartz | Level 8

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

Reeza
Super User

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
Quartz | Level 8
sorry some one in my group chat reply i dont get meaning behind that why i post in
novinosrin
Tourmaline | Level 20
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
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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