topic Re: Proc SQL Error when using order by in SAS Programming

Proc SQL Error when using order by

MrBruce — Sat, 26 Nov 2016 01:10:17 GMT

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

Re: Proc SQL Error when using order by

DarthPathos — Sat, 26 Nov 2016 03:07:40 GMT

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

Thanks

Chris

Re: Proc SQL Error when using order by

MrBruce — Sat, 26 Nov 2016 03:20:45 GMT

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

Re: Proc SQL Error when using order by

DarthPathos — Sat, 26 Nov 2016 03:27:26 GMT

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 🙂

Re: Proc SQL Error when using order by

Ksharp — Sat, 26 Nov 2016 03:41:12 GMT

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 .

Re: Proc SQL Error when using order by

Tom — Sat, 26 Nov 2016 05:25:53 GMT

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.