topic Re: 3rd highest sal in dense rank in SAS Procedures

3rd highest sal in dense rank

BrahmanandaRao — Sat, 02 Nov 2019 14:42:57 GMT

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;

Re: 3rd highest sal in dense rank

novinosrin — Sat, 02 Nov 2019 15:00:21 GMT



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;

Re: 3rd highest sal in dense rank

BrahmanandaRao — Sat, 02 Nov 2019 15:09:00 GMT

Hi,

Novinosrin

how to fix the error as per my code

please suggest me

Re: 3rd highest sal in dense rank

novinosrin — Sat, 02 Nov 2019 15:19:59 GMT

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!

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

Re: 3rd highest sal in dense rank

BrahmanandaRao — Sat, 02 Nov 2019 15:28:47 GMT

Hi,

novinosrin

Yes i am doing homework by oracle emp table

thank you for valuable advice

Re: 3rd highest sal in dense rank

FreelanceReinh — Sat, 02 Nov 2019 16:05:25 GMT

Hi @BrahmanandaRao,

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;

Re: 3rd highest sal in dense rank

BrahmanandaRao — Sat, 02 Nov 2019 17:09:52 GMT

Freelance Reinhard

Thank you very much for your valuable reply

Re: 3rd highest sal in dense rank

Kurt_Bremser — Sun, 03 Nov 2019 08:55:48 GMT

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.