How to update the table in sas which is from SQL Server?

[samirt]

Hi all,

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

[jakarman]

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.

[RW9]

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.

[LinusH]

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.

[samirt]

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;

proc sql;

     alter table top.sample

     add mon num ;

    update top.sample

     set mon=month(date);

quit;

SAS is throwing a

ERROR:- The header/variable is not supported by ODBC function

ERROR:- View sample cannot be altered

[user24feb]

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;

Proc SQL;
  Insert Into YOUR_LIBNAME.YOUR_SQL_TABLE Select * From YOUR_SAS_TABLE;
Quit;