order by in subquery??

Jaheuk — Mon, 06 Apr 2009 10:10:04 GMT

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

Re: order by in subquery??

LinusH — Tue, 07 Apr 2009 12:24:24 GMT

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

Re: order by in subquery??

LAP — Tue, 07 Apr 2009 23:59:16 GMT

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;

topic order by in subquery?? in SAS Procedures

order by in subquery??

Re: order by in subquery??

Re: order by in subquery??