## Third highest salary as per Department

Occasional Contributor
Posts: 16

# Third highest salary as per Department

Hi Team

Please help me with this scenario.I want to solve it by Proc Sql and not base SAS as i was able to solve it by first.variable and Proc rank so i need it to solve using Proc Sql.

Dataset:-

DATA SALARY1;
INPUT EmpName \$ Salary Department \$;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
;
RUN;

Output:-

Andrew 120 Finance

Q1.If there is no third observartion for particular department i don`t need that department salary to show up.

Q2.If there is no third observation for particular department i need the last salary for that department.

Posts: 5,523

## Re: Third highest salary as per Department

Here is a solution for Q1, including a tie breaker

``````DATA Salary2;
INPUT EmpName \$ Salary Department \$;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
PG 140 IT   <-- tied with Niti
;

proc sql;
/* Break ties */
create table S as
select *, salary + 0.0001*rand("uniform") as s
from Salary2;

create table thirdSalary as
select EmpName, Salary, Department
from
(select *
from
(select *
from S
group by Department
having s < max(s))
group by Department
having s < max(s))
group by department
having s = max(s);

drop table S;
quit;``````

If you don't need to break ties then replace dataset S with Salary2 and variable s with Salary.

Q2: There is no such thing as the last observation in SQL. You would need an observation number to solve this problem in SQL.

PG
Occasional Contributor
Posts: 16

## Re: Third highest salary as per Department

Thanks for the help PG Stats

Super User
Posts: 10,770

## Re: Third highest salary as per Department

```Here is for Q1.

DATA SALARY1;
INPUT EmpName \$ Salary Department \$;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
;
RUN;
proc sql;
select *,(select count(distinct salary) from salary1
where department=a.department and salary ge a.salary) as n
from salary1 as a
where calculated n=3;
quit;

If you want Q2, you need make an index variable like n+1, or you only want salary's max or min ?

```
Occasional Contributor
Posts: 16

## Re: Third highest salary as per Department

Hi Ksharp

Thank's for the help can you please describe the logic a bit it will be helpful

Super User
Posts: 10,770

## Re: Third highest salary as per Department

```Get each obs 's rank within each department .
In other words, the max has rank(n) 1,the second has rank 2 ........
and pick up the obs has the rank 3 (the third largest) .

```
Occasional Contributor
Posts: 16

## Re: Third highest salary as per Department

Hi KSharp

I already did it with help of Proc rank, but I wanted to understand the logic of your first solution. If possible can you please explain me the logic running behind the nested Query.

PROC Star
Posts: 2,340

## Re: Third highest salary as per Department

If you want to explore all the ways SAS can skew a cat, the best way to solve this question imho is to use proc summary and then a data a step to implement Q1 or Q2 or any other scenario. Compact and (somewhat) legible syntax, and no prior sorting required.

``````proc summary nway;
class DEPARTMENT;
var SALARY;
output out=STATS
max(SALARY)=SALARY
idgroup(max(SALARY)out[3](EMPNAME)=EMPNAME);
run; ``````
Posts: 5,523

## Re: Third highest salary as per Department

Another version of @Ksharp's idea, maybe simpler because it doesn't involve a correlated subquery:

``````DATA Salary2;
INPUT EmpName \$ Salary Department \$;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
PG 140 IT   <-- tied with Niti
;

proc sql;
/* Break ties */
create table S as
select *, salary + 0.0001*rand("uniform") as s
from Salary2;

create table thirdSalary as
select a.EmpName, a.Salary, a.Department
from
S as a left join
S as b on a.Department=b.Department
group by a.EmpName, a.Salary, a.Department
having sum(b.s>a.s) = 2;

drop tabel S;
quit;``````
PG
Occasional Contributor
Posts: 16

## Re: Third highest salary as per Department

Thanks every one for the help. I have got my answer and i have also gone through the details of using subquery.

Discussion stats
• 9 replies
• 1072 views
• 3 likes
• 4 in conversation