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.
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.
Some tips:
Don't use monotonic(), its not production.
Don't code in uppercase, its like shouting.
See here for a better solution:
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...
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.
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.
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.
Thanks a lot to all of You.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.