BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anandkvn
Lapis Lazuli | Level 10
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;

 

 


135 proc sql;
136 create table want as select*,largest(2,salary)
137 from req
138 group by id;
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither
the SELECT clause nor the optional HAVING clause of the associated
table-expression referenced a summary function.
NOTE: Invalid argument 1 to function LARGEST. Missing values may be generated.
NOTE: Table WORK.WANT created, with 9 rows and 3 columns.

139 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.15 seconds
cpu time 0.04 seconds

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

Another approach could be:

proc sort data=req;
	by id;
run;

proc transpose data=req out=req_tr prefix=salary;
	var salary;
	by id;
run;

data want;
	set req_tr;
	max2 = largest(2,of salary:);
	keep id max2;
run;

View solution in original post

4 REPLIES 4
ed_sas_member
Meteorite | Level 14

Hi @Anandkvn 

Here is an approach to do this:

proc sql;
	create table want as
	select id, max(salary) as max2
	from (select * from req group by id having salary < max(salary))
	group by id;
quit;

I am not sure whether you can use the largest() function in proc sql.

ed_sas_member
Meteorite | Level 14

Another approach could be:

proc sort data=req;
	by id;
run;

proc transpose data=req out=req_tr prefix=salary;
	var salary;
	by id;
run;

data want;
	set req_tr;
	max2 = largest(2,of salary:);
	keep id max2;
run;
Anandkvn
Lapis Lazuli | Level 10
data want;
	set req_tr;
	max2 = largest(2,of salary:);
	keep id max2;
run;

NOTE: Argument 1 to function LARGEST(2,2000) at line 178 column 9 is invalid.
id=001 salary=2000 max2=. _ERROR_=1 _N_=1
NOTE: Argument 1 to function LARGEST(2,3000) at line 178 column 9 is invalid.
id=002 salary=3000 max2=. _ERROR_=1 _N_=2
NOTE: Argument 1 to function LARGEST(2,4000) at line 178 column 9 is invalid.
id=003 salary=4000 max2=. _ERROR_=1 _N_=3
NOTE: Argument 1 to function LARGEST(2,3000) at line 178 column 9 is invalid.
id=001 salary=3000 max2=. _ERROR_=1 _N_=4
NOTE: Argument 1 to function LARGEST(2,5000) at line 178 column 9 is invalid.
id=001 salary=5000 max2=. _ERROR_=1 _N_=5
NOTE: Argument 1 to function LARGEST(2,2000) at line 178 column 9 is invalid.
id=004 salary=2000 max2=. _ERROR_=1 _N_=6
NOTE: Argument 1 to function LARGEST(2,1000) at line 178 column 9 is invalid.
id=003 salary=1000 max2=. _ERROR_=1 _N_=7
NOTE: Argument 1 to function LARGEST(2,5000) at line 178 column 9 is invalid.
id=002 salary=5000 max2=. _ERROR_=1 _N_=8
NOTE: Argument 1 to function LARGEST(2,1000) at line 178 column 9 is invalid.
id=004 salary=1000 max2=. _ERROR_=1 _N_=9
NOTE: Mathematical operations could not be performed at the following places. The results
of the operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
9 at 178:9
NOTE: There were 9 observations read from the data set WORK.REQ.
NOTE: The data set WORK.WANT has 9 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

 

Ksharp
Super User

You only want SQL ?

 

data req;
input id$ salary;
datalines;
001 2000
002 3000
003 4000
001 3000
001 5000
004 2000
003 1000
002 5000
004 1000
;

proc sql;
select distinct a.*
 from req as a,req as b
  where a.id=b.id and a.salary<b.salary 
   group by a.id,a.salary 
    having count(*)=1;
quit;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 712 views
  • 0 likes
  • 3 in conversation