order by in subquery??

[Jaheuk]

hi all,
why is it not possible to order in subquery ??

proc sql;
create table test as
select *
from (select * from sashelp.class order by sex, age );
QUIT;

GR,
Herman

[LinusH]

I never heard of this one. It seems very weird. Cannot find any doc that hints of this restriction in use of sub-queries. I suggest that open a track to SAS support to get an explanation.

/Linus

[LAP]

In this case your FROM Clause is a query expression
where the (query-expression) is an in-line view

According to the documentation
In-Line Views

The FROM clause can itself contain a query-expression that takes an optional table alias. This kind of nested query-expression is called an in-line view. An in-line view is any query-expression that would be valid in a CREATE VIEW statement. ......

Characteristics of in-line views include the following:
􀀀 An in-line view is not assigned a permanent name, although it can take an alias.
􀀀 An in-line view can be referred to only in the query in which it is defined. It
cannot be referenced in another query.
􀀀 You cannot use an ORDER BY clause in an in-line view.....

For more information see

http://support.sas.com/documentation/cdl/en/proc/61895/PDF/default/proc.pdf

To fix the problem change your query to

proc sql;
create table test as
select *
from (select * from sashelp.class) order by sex, age ;
QUIT;