Help using Base SAS procedures

simple index in Proc SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

simple index in Proc SQL

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;



Accepted Solutions
Solution
‎10-01-2014 02:35 PM
Trusted Advisor
Posts: 3,212

Re: simple index in Proc SQL

Posted in reply to sasboy007

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.

....
   

---->-- ja karman --<-----

View solution in original post


All Replies
Super User
Posts: 11,343

Re: simple index in Proc SQL

Posted in reply to sasboy007

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

Occasional Contributor
Posts: 16

Re: simple index in Proc SQL

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

ERROR: Duplicate values not allowed on index state

Solution
‎10-01-2014 02:35 PM
Trusted Advisor
Posts: 3,212

Re: simple index in Proc SQL

Posted in reply to sasboy007

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.

....
   

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 247 views
  • 0 likes
  • 3 in conversation