BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anandkvn
Lapis Lazuli | Level 10

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;



1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20


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;

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20


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
Lapis Lazuli | Level 10

Capture.PNG

Hi,

Novinosrin

 

how to fix the error  as per my code 

please suggest me

novinosrin
Tourmaline | Level 20

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
Lapis Lazuli | Level 10

Hi,

 

novinosrin

 

Yes i am doing  homework by oracle emp table  

thank you for valuable advice

 

 

Kurt_Bremser
Super User

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
Jade | Level 19

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
Lapis Lazuli | Level 10

Hi 

Freelance Reinhard

 

Thank you very much for your valuable reply 

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4331 views
  • 2 likes
  • 4 in conversation