Please help me to find the top three salary from emp table

[toparveenkumar]

I have a emp data set and want to find out the top three salary from emp table by following code.

Proc sql;
SELECT monotonic() as RANK, ename, sal
FROM (SELECT ename, sal
FROM emp
ORDER BY sal DESC)
WHERE  calculated RANK <= 3;
quit;

Then Error is coming 

ERROR 79-322: Expecting a ).

Please help to find "top three salary from emp table" I will be very thankful to you.

[RW9]

Some tips:

Don't use monotonic(), its not production.

Don't code in uppercase, its like shouting.

See here for a better solution:

https://communities.sas.com/t5/SAS-Programming/Calculate-the-highest-two-records-of-Metrc2-for-each-...

As for your code I can't check, post the exact log of the run.  Nothing jumps out at me, but without something to check against...

[toparveenkumar]

Sir It is the sample table

Data emp;
infile datalines truncover;
input id ename$ dept$ sal;
datalines;
1 A account 100
2 B HR 200
3 c Dev 400
4 D account 600
5 E account 700
6 F HR 500
7 G HR 300
8 H Dev 800
9 I Dev 250
;

Proc sql;
select *
from emp
order by sal desc;
quit;

Proc sql;
SELECT monotonic() as rank, ename, sal
from(select ename, sal
from emp
order by sal desc)
where calculated rank<= 3;
quit;

It is coming in log

Proc sql;
431 Select * from (select *,monotonic()as x from emp order by sal descending) ;
----- -
79 22
-
200
ERROR 79-322: Expecting a ).

ERROR 22-322: Syntax error, expecting one of the following: ;, ','.

ERROR 200-322: The symbol is not recognized and will be ignored.

432 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

I just want to solve it using Pro sql.

[RW9]

Yes, so Order by is not valid in subqueries.  Has been covered before:

https://communities.sas.com/t5/SAS-Procedures/order-by-in-subquery/td-p/15483

As I noted before, use the procedure mentioned in that post.  Also, using Base SAS in SAS (it is the programming language you are using) will make your code simpler, easier to maintain and faster.  Use SQL only when it adds something to the whole.

[RichardDeVen · Accepted solution]

The SQL statement reset OUTOBS=n; sets the OUTOBS option that restricts the number of rows selected for output.  The option applies to all subsequent queries in the SQL step, and can be reset to all rows using reset OUTOBS=MAX;

proc sql;
  reset outobs=3;

  create table top3priced_cars as
  select * from sashelp.cars
  order by MSRP descending;
quit;

Caution: The option does not affect sub-queries. This query will show N=428.

  create table top3priced_cars as
  select make, model, MSRP, count(*) as N from 
  ( select * from sashelp.cars ); 

Happy coding.

[toparveenkumar]

Thanks a lot to all of You.