BookmarkSubscribeRSS Feed
MrBruce
Calcite | Level 5

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

 

 

5 REPLIES 5
DarthPathos
Lapis Lazuli | Level 10

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!
MrBruce
Calcite | Level 5

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
Lapis Lazuli | Level 10

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!
Ksharp
Super User
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
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3951 views
  • 3 likes
  • 4 in conversation