I can't seem to get this to work.   Neither using just PROC SQL:   proc sql; create table foo ( id char(10) primary key ); create table bar ( id char(10) primary key, constraint foo_bar_fkey foreign key(id) references foo on delete restrict on update restrict ); quit; Or with PROC SQL and PROC DATASETS using the overlapping foreign and primary key section as a guide:   http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000403555.htm#a002573254   proc sql; create table foo ( id char(10) ); create table bar ( id char(10) ); quit; proc datasets lib=work; modify foo; ic create primary key(id); modify bar; ic create foreign key(id) references foo on delete restrict on update restrict; modify bar; ic create primary key(id); run; Getting the same error message when trying to create the primary key for bar:   ERROR: An index named id with the same definition but different characteristics exists for file WORK.BAR.DATA.   ... View more

I can't make this code work   proc sql; create table foo ( id char(11) ); create table bar ( id char(11) ); quit; proc datasets lib=work; modify foo; ic create primary key(id); modify bar; ic create primary key(id); ic create fkey = foreign key(id) references foo; run; I'm getting the following errors:   ERROR: An index named id with the same definition but different characteristics exists for file WORK.BAR.DATA. ... View more

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; ... View more

Is there a way to print to to the log? ... View more

  PROC DS2 ; package regexp / overwrite=yes; method match( varchar(1024) pattern, varchar(1024) string ) returns double; return prxmatch(pattern, string); end; run; QUIT; PROC FEDSQL; CREATE TABLE example ( id VARCHAR(11) ); QUIT; PROC FEDSQL; INSERT INTO example SELECT CASE LENGTH(id) WHEN 11 THEN SUBSTRING(id FROM 1 FOR 6) || SUBSTRING(id FROM 8 FOR 4) WHEN 12 THEN SUBSTRING(id FROM 1 FOR 6) || SUBSTRING(id FROM 8 FOR 5) END AS id FROM MYDATABASE.MYSCHEMA.MYTABLE WHERE regexp.match('/[0-9]+/', id) = 1 AND regexp.match('/[A-Z]+/', id) = 0; QUIT;   Throws this error:   ERROR: Access Violation occurred during PREPARE! What do I have to do to avoid this error?    ... View more

I can't get past this seemingly trivial exercise.  I would like to take advantage of all that PROC FEDSQL has to offer but simply cannot get consistent use of a PROC DS2 package.     PROC DS2; package regexp / overwrite=yes; method match( varchar(1024) pattern, varchar(1024) string ) returns integer; return prxmatch(pattern, string); end; run; QUIT;     Seems to yield the cryptic and not very useful error message of     ERROR: Access Violation occurred during PREPARE!     When trying to use it here for instance   PROC FEDSQL; INSERT INTO tableB SELECT id FROM tableA WHERE regexp.match('/.*/', id) = 1 AND regexp.match('/^[5-9]/', id) = 0; QUIT; Like are these PROC DS2 packages simply broken when used with PROC FEDSQL and I should just wait until 9.5 to come out when SAS has hopefully fixed all the bugs?  Is this thing just inherently buggy?       ... View more