DATA Step, Macro, Functions and more

Renaming indexes in PROC SQL

Reply
Frequent Contributor
Posts: 99

Renaming indexes in PROC SQL

I'm adding an primary key integrity constraint when creating a table.  Something like this

 

proc sql;
  create table foo (
    id char(10) primary key
  );
quit;

This automatically adds an index with the name of "id".  My problem is I want to create another table in the same library with same primary key column name.  Doing something like this throws an error after running the previous code:

 

proc sql;
  create table bar (
    id char(5) primary key
  );
quit;

Would it be possible to rename the index from within PROC SQL?  Something like this:

 

proc sql;
  create table foo (
    id char(10) primary key
  );

  alter table foo
  modify index id rename to foo_id_idx;

quit;
Valued Guide
Posts: 592

Re: Renaming indexes in PROC SQL

Posted in reply to tomcmacdonald

What error are you getting because when I run your code I don't get an error at all - here's my log

 

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         create table foo (
 75         id char(10) primary key);
 NOTE: Table WORK.FOO created, with 0 rows and 1 columns.
 76         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.08 seconds
       cpu time            0.00 seconds
       
 78         proc sql;
 79         create table bar (
 80         id char(5) primary key);
 NOTE: Table WORK.BAR created, with 0 rows and 1 columns.
 81         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 77         
 
 82         
 83         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 96         
Frequent Contributor
Posts: 99

Re: Renaming indexes in PROC SQL

Posted in reply to ChrisBrooks

I'm getting this error:

 

ERROR: An index named id with the same definition but different characteristics exists for file SOMELIB.FOO.DATA.

All datasets are residing in a library and not in WORK if that makes a difference. 

Super User
Posts: 10,770

Re: Renaming indexes in PROC SQL

Posted in reply to tomcmacdonald
proc sql;
  create table foo (
    id char(10) primary key
  );

  create table bar like foo;
quit;
Frequent Contributor
Posts: 99

Re: Renaming indexes in PROC SQL

My real life table bar will have other constraints so it isn't a direct copy. Also the id column is a different length in bar.
Super User
Posts: 13,523

Re: Renaming indexes in PROC SQL

Posted in reply to tomcmacdonald

@tomcmacdonald wrote:
My real life table bar will have other constraints so it isn't a direct copy. Also the id column is a different length in bar.

Sound like the cause of the error like

ERROR: An index named id with the same definition but different characteristics exists for file SOMELIB.FOO.DATA

Length of the variable is a characteristic.

 

And makes sense for keys. Since one set potentially could have xxxxxxxxxx as a value but one is limited to xxxx what should happen?

Valued Guide
Posts: 592

Re: Renaming indexes in PROC SQL

Posted in reply to tomcmacdonald

If the two id columns represent two different things e.g. customers and products then from a design point of view I would have thought it. better to rename them to something like cust_id and prod_id to remove the danger of future confusion over which is being referenced.

Ask a Question
Discussion stats
  • 6 replies
  • 102 views
  • 0 likes
  • 4 in conversation