Date:         Mon, 29 Jan 2007 15:41:03 -0500
Reply-To:     Mike Rhoads <RHOADSM1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Rhoads <RHOADSM1@WESTAT.COM>
Subject:      Re: Writing to SQL Server
In-Reply-To:  <200701291729.l0TG4QH3017067@mailgw.cc.uga.edu>
Content-Type: text/plain; charset="windows-1256"

Steve,

Yep.

What SAS actually does behind the scenes in this type of example is the following:

1.  Read from the specified data set,
2.  Write records to a "hidden" temporary data set,
And, assuming all goes well:
3.  Delete the original data set, and
4.  Rename the temporary data set back to the "original" data set.

This is why, when doing this type of processing with SAS data sets, you have to recreate indexes, respecify compression if desired, etc.  You are creating a whole new data set that just happens to have the same name as the one you started out with.

In contrast, RDBMSes (not just SQL Server) aren't set up this way.  They expect you to insert, delete, and modify records within a table, without creating a new version of the table.

So, you have a few alternatives:

1.  Explicitly do what SAS is implicitly doing.  Start by creating a new table in SQL Server with the same columns as your current table, and use that new table on your DATA statement.  At the end of your DATA step, if all goes well, get rid of the original table (using PROC DATASETS or PROC SQL) and change the name of your newly-created table back to the original name.

There are some potential pitfalls to this approach.  One of them is that, even if your account has rights to add, delete, and/or modify records in a SQL Server table, you may not have the rights to create or drop entire tables.

2.  As Mark Terjeson suggested, use PROC SQL with an UPDATE statement rather than a DATA step.  This updates the records in place, rather than creating a new table.

3.  You can also implement the update-in-place strategy in DATA step code, via the MODIFY statement.

Note, however, that updating in place cannot add new variables or otherwise change the structure of your table.  So, for your example, it would work if your original table already has spj_temp as a column.  Otherwise, you will have to either create a new table or add a new column to the original table.

Mike Rhoads
Westat
RhoadsM1@Westat.com

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Steve James
Sent: Monday, January 29, 2007 12:29 PM
To: SAS-L@LISTSERV.UGA.EDU
Cc: Steve James
Subject: Writing to SQL Server


I’m trying to test my ability to write to a SQL Server table, however I am
unable.  It appears to read the data OK, but not write. It doesn’t seem to
matter if I use ODBC or OLE-DB, both fail with the same message (see below).
 I'm not real familiar with either ODBC or OLEDB; I know just enough syntax
to put on the LIBNAME statement to make it work but not much more.


7    %let mylib = qw ;
8
9    libname &mylib odbc
10
complete=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
schema=dbo ;

NOTE: Libref QW was successfully assigned as follows:
      Engine:        ODBC
      Physical Name:
11
12   data qw.yhat0405 ;
13     set qw.yhat0405 ;
14     spj_temp = 1 ;
15     run ;

ERROR: The ODBC table yhat0405 has been opened for OUTPUT. This table
already exists, or there is a name conflict with an
       existing object. This table will not be replaced. This engine does
not support the REPLACE option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds

Thanks,

Steve

Steve James
Centers for Disease Control and Prevention
sjames@cdc.gov