09-25-2017 06:35 AM - last edited on 09-25-2017 08:50 AM by LinusH
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.
09-25-2017 08:44 AM
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.
09-25-2017 08:48 AM
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.
09-25-2017 08:59 AM - edited 09-25-2017 09:00 AM
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."
09-25-2017 08:53 AM
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.
09-25-2017 09:07 AM
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;
Need further help from the community? Please ask a new question.