Proc SQL Error when using order by

[MrBruce]

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

[DarthPathos]

Hi 

When I did a google search, I got this post which answers your question.  Hope it helps.

Thanks

Chris

[MrBruce]

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

[DarthPathos]

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 🙂

[Ksharp]

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 .

[Tom]

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.