topic Re: Renaming indexes in PROC SQL in SAS Programming

Renaming indexes in PROC SQL

tomcmacdonald — Wed, 27 Jun 2018 21:29:59 GMT

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;

Re: Renaming indexes in PROC SQL

ChrisBrooks — Wed, 27 Jun 2018 21:58:31 GMT

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

Re: Renaming indexes in PROC SQL

Ksharp — Thu, 28 Jun 2018 02:15:58 GMT

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

  create table bar like foo;
quit;

Re: Renaming indexes in PROC SQL

tomcmacdonald — Thu, 28 Jun 2018 13:49:08 GMT

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.

Re: Renaming indexes in PROC SQL

tomcmacdonald — Thu, 28 Jun 2018 13:51:23 GMT

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.

Re: Renaming indexes in PROC SQL

ballardw — Fri, 29 Jun 2018 17:32:26 GMT

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

Re: Renaming indexes in PROC SQL

ChrisBrooks — Fri, 29 Jun 2018 18:12:30 GMT

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.