topic Re: Third highest salary as per Department in SAS Programming

Third highest salary as per Department

Nitish1003 — Sun, 12 Mar 2017 05:47:01 GMT

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.

Re: Third highest salary as per Department

PGStats — Sun, 12 Mar 2017 06:35:41 GMT

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.

Re: Third highest salary as per Department

Ksharp — Sun, 12 Mar 2017 10:31:05 GMT

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 ?

Re: Third highest salary as per Department

Nitish1003 — Sun, 12 Mar 2017 18:21:21 GMT

Hi Ksharp

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

Re: Third highest salary as per Department

Nitish1003 — Sun, 12 Mar 2017 18:22:37 GMT

Thanks for the help PG Stats

Re: Third highest salary as per Department

ChrisNZ — Sun, 12 Mar 2017 21:20:18 GMT

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;

Re: Third highest salary as per Department

Ksharp — Mon, 13 Mar 2017 02:41:19 GMT

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) .

Re: Third highest salary as per Department

PGStats — Mon, 13 Mar 2017 03:40:33 GMT

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;

Re: Third highest salary as per Department

Nitish1003 — Mon, 13 Mar 2017 05:22:39 GMT

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.

Re: Third highest salary as per Department

Nitish1003 — Mon, 13 Mar 2017 07:42:55 GMT

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

Re: Third highest salary as per Department

Raj03 — Sat, 28 Jul 2018 12:53:10 GMT

Works perfectly and it is actually a very good Logic took time to understand