turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Third highest salary as per Department

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 12:47 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 01:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 02:22 PM

Thanks for the help PG Stats

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 06:31 AM

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 ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 02:21 PM

Hi Ksharp

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 10:41 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-13-2017 01:22 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 05:20 PM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-12-2017 11:40 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-13-2017 03:42 AM

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