topic Re: Please help me to find the top three salary from emp table in SAS Programming

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

toparveenkumar — Tue, 05 Mar 2019 10:59:15 GMT

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.

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

RW9 — Tue, 05 Mar 2019 11:09:40 GMT

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-date/m-p/497108/highlight/true#M131644

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

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

toparveenkumar — Tue, 05 Mar 2019 12:05:40 GMT

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.

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

RW9 — Tue, 05 Mar 2019 12:29:36 GMT

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.

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

RichardDeVen — Tue, 05 Mar 2019 15:08:51 GMT

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.

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

toparveenkumar — Wed, 06 Mar 2019 06:47:41 GMT

Thanks a lot to all of You.