Hi Dave,

Corrected code in which &SQLtable is table name in database:

LIBNAME MSSQLsrv ODBC DSN="SQL server" USER=username PWD=password; * MS SQL lib ODBC engine; PROC SQL; CONNECT TO ODBC (DSN="SQL server" USER=username PWD=password); * Pass-Through Facility; EXEC (DELETE FROM &SQLtable) BY ODBC; * remove existing data; EXEC (ALTER TABLE &SQLtable ADD &Dummy FLOAT) BY ODBC; * add a Dummy variable; EXEC (ALTER TABLE &SQLtable DROP COLUMN &MSSQLvars) BY ODBC; * remove old structure; EXEC (ALTER TABLE &SQLtable ADD &SASVars) BY ODBC; * define new structure; EXEC (ALTER TABLE &SQLtable DROP COLUMN &Dummy) BY ODBC; * remove Dummy variable; DISCONNECT FROM ODBC; INSERT INTO &DataBase SELECT * FROM SAS_dataset; * export data via ODBC; QUIT;

Regards - Jim. -- Jim Groeneveld, Netherlands Statistician/SAS consultant http://jim.groeneveld.eu.tf

On Fri, 6 Aug 2010 06:18:34 -0400, Jim Groeneveld <jim.1stat@YAHOO.COM> wrote:

>Hi Dave, > >First of all I have to point to the drawbacks of PROC APPEND, even with the >FORCE option: >1. it does not append new variables in the DATA= dataset to the BASE= dataset; >2. it restricts existing character variables lengths in the BASE= dataset to >those in that dataset, even if the lengths in the DATA= dataset may be larger. > >As a result appending datasets with different structures leads to loss of >data. Because of that I wrote a macro %_Append_ that losslessly concatenates >any pair of datasets, while keeping as much as possible attributes: variable >labels, (in)formats. It can be obtained as: >http://jim.groeneveld.eu.tf/software/SASmacro/_Append_.zip >I am currently preparing a paper about this for PhUSE 2010. > >For the same congress I am also preparing a paper about writing data from >SAS to an MS SQL database table, just a specific table for which I have >write permission (not for other tables). So it is not possible to CREATE a >database table, it is only possible to modify an existing table. > >For that purpose I use a combination of SAS SQL code and Pass-Through code. >What I do is emptying the MS SQL table completely, even removing its >structure and filling it from scratch. The code basically is: > >LIBNAME MSSQLsrv ODBC DSN=”SQL server” USER=username PWD=password; * MS SQL >lib ODBC engine; >PROC SQL; > CONNECT TO ODBC (DSN="SQL server" USER=username PWD=password); * >Pass-Through Facility; > EXEC (DELETE FROM &DataBase) BY ODBC; * remove >existing data; > EXEC (ALTER TABLE &DataBase ADD &Dummy FLOAT) BY ODBC; * add a >Dummy variable; > EXEC (ALTER TABLE &DataBase DROP COLUMN &MSSQLvars) BY ODBC; * remove >old structure; > EXEC (ALTER TABLE &DataBase ADD &SASVars) BY ODBC; * define >new structure; > EXEC (ALTER TABLE &DataBase DROP COLUMN &Dummy) BY ODBC; * remove >Dummy variable; > DISCONNECT FROM ODBC; > INSERT INTO &DataBase SELECT * FROM SAS_dataset; * export >data via ODBC; >QUIT; > >in which: >&Dummy is a dummy variable: an MS SQL table needs at least one variable; >&MSSQLvars is a list of previously determined variables in the table; >&SASVars is a list (with attributes) of new variables to define; >&DataBase is the library.dataset SAS name of the database table; > >Yet there is much more preparation needed: >a. the insertion must take place in the order that the variables (columns) >have been defined, it is pure data, no variables names and the data don't >know which variables they belong to; >b. as no automatic type conversions between SAS and MS SQL takes place (like >with CREATE), one has to provide for those oneself: >- any currency (DOLLAR) format to MONEY format, >- any DATE or TIME format to DATETIME format; >c. the existing table's row structure has to be obtained in order to be able >to remove it (&MSSQLvars), via either SAS SQL CREATE or a data step. > >All that is to be described in much more detail in my paper (October 2010). >That way I am able to send a 'copy' of any SAS dataset to that table without >bothering for (existing) contents and structure. At the server side that >table can be processed further. > >I don't know whether it is feasible for you to follow this strategy, you >should have a copy of the existing data in the database table available in >SAS to combine with the new data in SAS, or you should obtain such a copy >firstly CREATing a SAS dataset if that would not be too much data. In any >case the PASS-Through faciliy is an option, provided you know what you are >doing. > >I hope these suggestions may prove to be helpful. > >Regards - Jim. >-- >Jim Groeneveld, Netherlands >Statistician/SAS consultant >http://jim.groeneveld.eu.tf > > > >On Thu, 5 Aug 2010 08:31:15 -0400, Dave Brewer <david.brewer@UC.EDU> wrote: > >>Hi All, >> >>I append data from a SAS data set to my M/S SQL table using an ODBC >>connection and PROC APPEND. The SQL table contains a key based on multiple >>columns. >> >>Everything was working OK until recently when my new SAS data set >>contained data already found on the SQL table and the PROC APPEND blew up. >> >>I corrected the problem by deleting the duplicates by reading in my SQL >>table and merged this table against my SAS file and deleted the duplicates >>and then reran my PROC APPEND. >> >>My question: Is it possible to use sql passthru commands to insert my SAS >>records into the SQL tables and delete the duplicates? >> >>I know I can send my SAS data set to SQL and use the following: >> >>INSERT INTO MYMASTERSQL_TABLE >> SELECT * >>FROM MYSASTABLE >> WHERE NOT EXISTS >> ( SELECT * >> FROM MYMASTERSQL_TABLE >> WHERE MYMASTERSQL_TABLE_KEY1 = MYSASTABLE >> etc for all my keys ) >> >>Then I would have to drop the SAS table as I don't need it anymore. >> >>Is it possible to do what I want by using a local SAS dataset and a M/S >>SQL table? How would I construct the SAS Passthru code? >> >>Thanks for your help. >>Dave