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;
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
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.
proc sql;
create table foo (
id char(10) primary key
);
create table bar like foo;
quit;
@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?
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.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.