Proc sql finding Max value

[vandhan]

data have;
input id Salary;
cards;
101 1000
102 2000
103 1000
104 3000
105 3000
106 4000
;

proc sql;
create table want as
select id, max( salary ) as wantvar
from have
where salary < ( select max( salary ) from have where salary < ( select max ( salary ) from have));
quit;

above code is giving 3 records... it should get only one that is 102 2000.

Please help.

[Kurt_Bremser]

You already posted this in general SAS Programming. Please refrain from double posting.

[Ksharp]

You want select the second large Salary OBS ?

data have;
input id Salary;
cards;
101 1000
102 2000
103 1000
104 3000
105 3000
106 4000
;
proc sql;
select id, salary ,(select count(distinct salary) from have where salary lt a.salary) as n
from have as a
where calculated n=1;
quit;