topic Re: simple index in Proc SQL in SAS Procedures

simple index in Proc SQL

sasboy007 — Wed, 01 Oct 2014 17:07:24 GMT

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;

Re: simple index in Proc SQL

ballardw — Wed, 01 Oct 2014 17:19:16 GMT

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

Re: simple index in Proc SQL

sasboy007 — Wed, 01 Oct 2014 17:45:11 GMT

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

ERROR: Duplicate values not allowed on index state

Re: simple index in Proc SQL

jakarman — Wed, 01 Oct 2014 18:35:15 GMT

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.

....