As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Inserting Rows in a Specified Order

From sasCommunity
Jump to: navigation, search

When SQL is used to create a new table containing the results of a query, an ORDER BY clause can be specified, and is honored. For example:

proc sql;

    create table newclass as
    select * from sashelp.class where sex='M' order by age;

However, if the new table is first created with no rows, then populated using the INSERT statement, the ORDER BY clause is rejected. For example, running

create table newclass like sashelp.class;
insert into newclass
select * from sashelp.class where sex='M' order by age;

generates, in the log

     select * from sashelp.class where sex='M' order by age;
                                               ----- --
                                               22    202
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=,
              <>, =, >, >=, AND, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, INTERSECT,
              LE, LET, LT, LTT, NE, NET, NOT, OR, OUTER, UNION, ^, ^=, |, ||, ~, ~=.
ERROR 202-322: The option or parameter is not recognized and will be ignored.

Embedding the SELECT clause with the ORDER BY clause in an inline view does not help. The code becomes

insert into newclass select * from 
 (select * from sashelp.class where sex='M' order by age);

and the same error condition results. However, a named and stored view can solve the problem. Code:

create view ordered as
select * from sashelp.class where sex='M' order by age;
insert into newclass select * from ordered;

Clean up and exit:

drop view ordered;
quit;