Create ranks using SQL

[Naveen1]

Question : I have a dataset with department names, employee names, salary. I have to find out the 5th largest salary of employee along with its department by using Proc SQL (I know the procedure by using Data step but I want to know by using Proc SQL). Please help.

Dept_Name	Emp_name	Salary
Economics	Naveen	1070
statistics	Praveen	1157
Biology	Manju	1286
marketing	Chethan	1396
Economics	vinay	1110
statistics	sanjay	1318
Biology	harshi	1376
marketing	manu V	1179
Biology	manu S	1002
statistics	manu P	1442

[PaigeMiller]

Is this a homework problem?

I would advise to do this in a data step or PROC RANK. It's much simpler.

[Naveen1]

I want to know in Proc SQl.

I know the procedure in Data step by using rank and (_n_).

[Loko]

Post yout trial here and you will be helped for sure. Have you tried solve it by yourself?

[Naveen1]

Iam not that much good in Proc SQL. When I searched online I could't get exact solution for 5th largest. I got solution for 2nd largest, 3rd largest. So please help me how to get 5th Largest. Thanks in advance.

[PaigeMiller]

Can you provide a link to the solution for 3rd largest? Why wouldn't that generalize to the 5th largest? Show us your code!

And while it is admirable to try to increase your SQL skills, this really isn't something that you should attempt to do in SQL, since SQL is simply a very poor choice in SAS to do this calculation.

[Naveen1]

The above problem is interviewed question. So Iam seeking help in this portal. Thanks.

[PaigeMiller]

---

@Naveen1 wrote:

The above problem is interviewed question. So Iam seeking help in this portal. Thanks.

---

The answer to the interview question is:

"It would be easier to do this in a DATA step or using PROC RANK. Doing this in PROC SQL is a very poor choice."

[gamotte]

1/ Sort by Salary (with a SELECT ... ORDER BY if you have to use SQL only)

2/ Use the monotonic function to get the fifth record.

[Naveen1]

Can I get elabprated syntax, as I informed earlier I dont have much knowledge on  Proc SQL.

[LinusH]

You have still not motivated why you want/need to do it in SQL.

If it's for learning purposes, don't do it, since given the answer already given, there are better ways to do this. Don't learn a less efficient way.

If you want to learn SQL, there must be tons of other exercises to go about.

[Ksharp]

Why you want to do this. it is really clumsy for SQL to get the largest n obs.
The following return the 5th largest weight for each sex.



proc sql;
select sex,name,weight,(select count(*) as n  from (select distinct sex,name,weight from sashelp.class) as b
          where b.sex=a.sex and b.weight<=a.weight) as n
 from sashelp.class as a
   where calculated n=5;
quit;

[stat_sas · Accepted solution]

Hi,

This is another way:

proc sql outobs=5;
create table want as
select * from have
order by salary desc;
select * from want
having salary=min(salary);
quit;