3rd highest sal in dense rank

[Anandkvn]

Hi  ,

Good Evening to all

Here I have a emp table so i want find 3rd highest sal using Dense_Rank function

proc sql;
create table emp 	
  (empno num(6),
   ename char(15), 
   job char(15), 
   mgr num(10),
   hiredate char(15) ,
   sal num(12),
   comm num(6),
   deptno num(10));
quit;


proc sql;
insert into emp values(7369 'SMITH'  'CLERK'           7902  '17-DEC-80'        800         .          20);
insert into emp values(7499 'ALLEN'  'SALESMAN'        7698  '20-FEB-81'       1600        300         30);
insert into emp values(7521 'WARD'   'SALESMAN'        7698  '22-FEB-81'       1250        500         30);
insert into emp values(7566 'JONES'  'MANAGER'         7839  '02-APR-81'       2975         .          20);
insert into emp values(7654 'MARTIN' 'SALESMAN'        7698  '28-SEP-81'       1250       1400         30);
insert into emp values(7698 'BLAKE'  'MANAGER'         7839  '01-MAY-81'       2850         .          30);
insert into emp values(7782 'CLARK'  'MANAGER'         7839  '09-JUN-81'       2450         .          10);
insert into emp values(7788 'SCOTT'  'ANALYST'         7566  '19-APR-87'       3000         .          20);
insert into emp values(7839 'KING'   'PRESIDENT'        .    '17-NOV-81'       5000         .          10);
insert into emp values(7844 'TURNER' 'SALESMAN'        7698  '08-SEP-81'       1500         0          30);
insert into emp values(7876 'ADAMS'  'CLERK'           7788  '23-MAY-87'       1100         .          20);
insert into emp values(7900 'JAMES'  'CLERK'           7698  '03-DEC-81'        950         .          30);
insert into emp values(7902 'FORD'   'ANALYST'         7566  '03-DEC-81'       3000         .          20);
insert into emp values(7934 'MILLER' 'CLERK'           7782  '23-JAN-82'       1300         .          10);
quit;


/*how to get 3rd highest salary using Dense_Rank function*/
proc sql;
create table _3rd_highest_dense_rank as
select e.*, dense_rank(), over(order by ,sal desc)
as dense_rank from emp e)
where dense_rank=3;
quit;

[novinosrin · Accepted solution]

proc sql;
create table emp 	
  (empno num(6),
   ename char(15), 
   job char(15), 
   mgr num(10),
   hiredate char(15) ,
   sal num(12),
   comm num(6),
   deptno num(10));
quit;


proc sql;
insert into emp values(7369 'SMITH'  'CLERK'           7902  '17-DEC-80'        800         .          20);
insert into emp values(7499 'ALLEN'  'SALESMAN'        7698  '20-FEB-81'       1600        300         30);
insert into emp values(7521 'WARD'   'SALESMAN'        7698  '22-FEB-81'       1250        500         30);
insert into emp values(7566 'JONES'  'MANAGER'         7839  '02-APR-81'       2975         .          20);
insert into emp values(7654 'MARTIN' 'SALESMAN'        7698  '28-SEP-81'       1250       1400         30);
insert into emp values(7698 'BLAKE'  'MANAGER'         7839  '01-MAY-81'       2850         .          30);
insert into emp values(7782 'CLARK'  'MANAGER'         7839  '09-JUN-81'       2450         .          10);
insert into emp values(7788 'SCOTT'  'ANALYST'         7566  '19-APR-87'       3000         .          20);
insert into emp values(7839 'KING'   'PRESIDENT'        .    '17-NOV-81'       5000         .          10);
insert into emp values(7844 'TURNER' 'SALESMAN'        7698  '08-SEP-81'       1500         0          30);
insert into emp values(7876 'ADAMS'  'CLERK'           7788  '23-MAY-87'       1100         .          20);
insert into emp values(7900 'JAMES'  'CLERK'           7698  '03-DEC-81'        950         .          30);
insert into emp values(7902 'FORD'   'ANALYST'         7566  '03-DEC-81'       3000         .          20);
insert into emp values(7934 'MILLER' 'CLERK'           7782  '23-JAN-82'       1300         .          10);
quit;

proc rank data=emp out=want(keep=ranked_salary empno sal
where=(ranked_salary=3))
descending ties=dense;

   var sal ;
   ranks ranked_salary;
run;

proc print data=want;
run;

[Anandkvn]

Hi,

Novinosrin

how to fix the error  as per my code 

please suggest me

[novinosrin]

Your code looks like a Database specific SQL of some proprietary database like MS SQL Server, Oracle etc. Therefore you wouldn't expect that to work in a Proc SQL(SAS specific SQL which is proprietary to SAS)  environment.

If you want to use Database specific SQL from/in a SAS environment, I'm afraid you would need to connect to your database using explicit pass through called Proc SQL pass through within which you may be able to use database specific SQL to work to your needs.

I recently had amazing help from these gentlemen @Patrick  , @Ksharp , @Kurt_Bremser  , @SASKiwi in a long thread begging their patience to listen to my emotional rant. I would recommend, do some research online, get your SAS/Database administers to help/collaborate and if any of the above gentlemen et al chimes in, you may be in for a party. Best Regards!

PS

Personal case update, despite their help, I am afraid I couldn't win over bureaucracy. All the best!

[Anandkvn]

Hi,

novinosrin

Yes i am doing  homework by oracle emp table  

thank you for valuable advice

[Kurt_Bremser]

Maxim 1: Read the documentation.

In this case, to see which SQL functions are available in SAS, and how they need to be called. You also have two opening and three closing brackets in your code, so that won't work either.

[FreelanceReinh]

Hi @Anandkvn, 

Of course, PROC RANK is designed for solving such problems. To stay within the limits of PROC SQL while replicating the result from PROC RANK, you could use a subquery:

proc sql;
create table want as
select empno, sal, 3 as ranked_salary
from emp a
where (select count(distinct sal) from emp b where b.sal>a.sal)=2;
quit;

[Anandkvn]

Hi 

Freelance Reinhard

Thank you very much for your valuable reply