BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasboy007
Calcite | Level 5

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;


1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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

3 REPLIES 3
ballardw
Super User

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
Calcite | Level 5

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

ERROR: Duplicate values not allowed on index state

jakarman
Barite | Level 11

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 --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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