DATA Step, Macro, Functions and more

Proc SQL Error when using order by

Reply
Occasional Contributor
Posts: 5

Proc SQL Error when using order by

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

 

 

Regular Contributor
Posts: 231

Re: Proc SQL Error when using order by

Hi 

 

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

 

Thanks

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Occasional Contributor
Posts: 5

Re: Proc SQL Error when using order by

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

 

Bruce

Regular Contributor
Posts: 231

Re: Proc SQL Error when using order by

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 :-)

Has my article or post helped? Please mark as Solution or Like the article!
Super User
Posts: 9,676

Re: Proc SQL Error when using order by

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 .

Super User
Super User
Posts: 6,499

Re: Proc SQL Error when using order by

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.

Ask a Question
Discussion stats
  • 5 replies
  • 213 views
  • 2 likes
  • 4 in conversation