topic Re: proc sql in SAS Programming

Create ranks using SQL

Naveen1 — Mon, 25 Sep 2017 12:50:17 GMT

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

Re: proc sql

PaigeMiller — Mon, 25 Sep 2017 10:57:41 GMT

Is this a homework problem?

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

Re: proc sql

Naveen1 — Mon, 25 Sep 2017 11:04:17 GMT

I want to know in Proc SQl.

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

Re: proc sql

Loko — Mon, 25 Sep 2017 11:49:54 GMT

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

Re: proc sql

Naveen1 — Mon, 25 Sep 2017 12:44:49 GMT

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.

Re: proc sql

PaigeMiller — Mon, 25 Sep 2017 12:48:45 GMT

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.

Re: proc sql

Naveen1 — Mon, 25 Sep 2017 12:50:43 GMT

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

Re: proc sql

gamotte — Mon, 25 Sep 2017 12:51:16 GMT

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.

Re: proc sql

Naveen1 — Mon, 25 Sep 2017 12:53:04 GMT

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

Re: proc sql

LinusH — Mon, 25 Sep 2017 12:53:46 GMT

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.

Re: proc sql

PaigeMiller — Mon, 25 Sep 2017 13:00:47 GMT

@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."

Re: Create ranks using SQL

Ksharp — Mon, 25 Sep 2017 13:07:02 GMT

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;

Re: Create ranks using SQL

stat_sas — Mon, 25 Sep 2017 13:41:56 GMT

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;