Help using Base SAS procedures

order by in subquery??

Reply
Frequent Contributor
Posts: 89

order by in subquery??

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
Super User
Posts: 5,429

Re: order by in subquery??

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
Data never sleeps
Frequent Contributor
Frequent Contributor
Posts: 77

Re: order by in subquery??

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;
Ask a Question
Discussion stats
  • 2 replies
  • 1252 views
  • 0 likes
  • 3 in conversation