Renaming indexes in PROC SQL

[tomcmacdonald]

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;

[ChrisBrooks]

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         

[tomcmacdonald]

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. 

[Ksharp]

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

  create table bar like foo;
quit;

[tomcmacdonald]

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.

[ballardw]

---

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

[ChrisBrooks]

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.