02-27-2015 01:20 AM
In my case the table which i am using is on SQL Server. Using libname statement i access that table. Whenever i make any modification to that table, SAS throws an
Error:- the ODBC table has been output. This table already exists,there in name conflicts with existing objects.
This table will not be replaced. This engine doesn't support REPLACE Option
How to resolve it.
Thanks in advance
02-27-2015 07:55 AM
This is a contradiction with a RDBMS - SAS. SAS is designed to replace full tables when a record is changing. An RDBMS is designed with OLTP only updating one record for every record change.
You cannot solve that different desigg as a result of different goals. You should behave with your code accordingly to those designs.
Replacing a dataset while updating that data (datstep) is allowed in a SAS datastep but should be avoided at all with SQL. Use other tablenames.
If you are updating one record with SAS than use updated in place (modify that one) and use locking at record level as the RDBMS interface does need that.
02-27-2015 08:44 AM
Jaap Karman has provided there reason behind this, I would want to ask why you are changing a database table from SAS? Doesn't sound like a good idea to me, use the database functionality to update tables, that way you can hit all the hooks from the, e.g. audit trail updates etc. If you want to work with the data in SAS, access it an store it in SAS, then do what you want with it. Try to avoid overlapping the two.
03-02-2015 03:36 AM
It's not unusual to have SAS to refresh tables in an external RDBMS. It could be a architectural decision to keep staing tables there. Refresh strategies could also apply to some data mart structures.
But most DBA's don't like replacing tables, so a truncate/insert strategy would have a better chance to get you popular with those guys.
03-02-2015 01:24 AM
Thanks for the reply
I am having a table named sample in sql which is having a field name Date. From which i wanted to extract month number using sas language. The following is the code which i am using
libname top odbc datasrc=xx user=xxx password=xxx;
alter table top.sample
add mon num ;
SAS is throwing a
ERROR:- The header/variable is not supported by ODBC function
ERROR:- View sample cannot be altered
03-02-2015 03:17 AM
If the table already exists, you must use either proc SQL or proc append. Try a "little" table first (because of insertbuff option and bulkload option SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition) and use something like this:
* Please rectify your Libnames etc. first!;
Libname YOUR_LIBNAME ODBC Schema=DBO DSN=YOUR_SQL_STAGING_AREA;
Insert Into YOUR_LIBNAME.YOUR_SQL_TABLE Select * From YOUR_SAS_TABLE;