topic Re: Proc sql finding Max value in SAS Programming

Proc sql finding Max value

vandhan — Thu, 28 Jan 2016 12:24:32 GMT

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.

Re: Proc sql finding Max value

Kurt_Bremser — Thu, 28 Jan 2016 12:25:30 GMT

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

Re: Proc sql finding Max value

Ksharp — Fri, 29 Jan 2016 02:46:07 GMT

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;