DATA Step, Macro, Functions and more

Third highest salary as per Department

Reply
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
Adam 110 IT
Niti 140 IT
Aman 110 HR
Lewis 130 Finance
Smith 125 Back
;
RUN;

Output:-

Andrew 120 Finance

Adam 110 IT

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.

Thanks in Advance.Please help.

 

Respected Advisor
Posts: 4,927

Re: Third highest salary as per Department

Posted in reply to Nitish1003

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
Adam 110 IT
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,041

Re: Third highest salary as per Department

Posted in reply to Nitish1003
Here is for Q1.

DATA SALARY1;
INPUT EmpName $ Salary Department $;
DATALINES;
Steve 150 Finance
James 120 IT
Andrew 120 Finance
Mark 100 Back
Adam 110 IT
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,041

Re: Third highest salary as per Department

Posted in reply to Nitish1003
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.

Thanks in advance.

PROC Star
Posts: 1,760

Re: Third highest salary as per Department

Posted in reply to Nitish1003

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; 
Respected Advisor
Posts: 4,927

Re: Third highest salary as per Department

Posted in reply to Nitish1003

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
Adam 110 IT
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.

Ask a Question
Discussion stats
  • 9 replies
  • 426 views
  • 3 likes
  • 4 in conversation