warning not correct output

[Anandkvn]

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

[ed_sas_member]

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 · Accepted solution]

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]

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]

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;