simple index in Proc SQL

[sasboy007]

Hi all,

I have the below, but SAS log file is stating an error exists with my index statement.  Is this the best way of adding an index to create table statement?  Should I have the index statement in a different location?

PROC SQL;

CREATE TABLE TEST AS

SELECT * FROM

CONNECTION TO ORACLE(

SELECT STATE, SALES

FROM SALES

WHERE STATE IN ('IL','OH');

create unique index indx1 on sales(state);

DISCONNECT FROM ORACLE;

QUIT;

[ballardw]

From the documentation:

index-name names the index that you are creating. If you are creating an index on one column only, then index-name must be the same as column.

Looks like the index name has to be STATE not INDX1

[sasboy007]

Same thing.  Getting the following error on index, although the query runs correctly.

ERROR: Duplicate values not allowed on index state

[jakarman · Accepted solution]

You are defining an index on an external (rdbms oracle) table . Normally a DBA's type of work.
The SQL pass through code can be checked using the sastrace option.

Creating an index with unique is telling there should be no duplicates. I expect duplicated on state. This it the error you are getting.

....