Hi,
I encountered the error when I would like to use order by in Proc SQL. Below are three codes and their resuts.
Why does code 2 generate error but code 3 works? Thanks.
Code 1:
proc sql;
create table test2 as
(select *
from test);
quit;
Resut: No Error
Code 2:
proc sql;
create table test2 as
(select *
from test
order by 1,2);
quit;
Resut: Error
Code 3:
proc sql;
create table test2 as
select *
from test
order by 1,2;
quit;
Resut: No Error
Hi
When I did a google search, I got this post which answers your question. Hope it helps.
Thanks
Chris
Hi,
Thanks for the help. The link you provided explains that order by could be not be used in in-line view.
The problem that I encountered is that order by works fine when there are no parentheses, but generates an error when parentheses are used.
Do you have any insights? Thanks. 🙂
Bruce
Your second example has SAS seeing the (...) as a subquery, which can't have ORDER BY. Example 3 is just creating a new table using your criteria, which is perfectly acceptable (and something i do all the time).
For future reference and to speed up someone helping you, I recommend providing code using one of the SASHELP datasets (so the code can be replicated on anyone's SAS system), and also providng a copy / paste of the error message.
Hoping this helps and good luck 🙂
ORDER BY is to order the final result , therefore it should be used at the end of code. Couldn't be used in Sub-Query .
You are getting an error in the second example because you included an ORDER BY clause in your sub-query. The first one does not generate an error since the sub-query doesn't have the ORDER BY clause. As the third example shows you do not need to use a sub-query in a CREATE TABLE statement, so don't.
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.
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.