Date: Fri, 6 Aug 2010 06:42:37 -0400
Reply-To: Jim Groeneveld <jim.1stat@YAHOO.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jim Groeneveld <jim.1stat@YAHOO.COM>
Subject: Re: PASSTHRU SQL Question
Content-Type: text/plain; charset=ISO-8859-1
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;
CONNECT TO ODBC (DSN="SQL server" USER=username PWD=password); *
EXEC (DELETE FROM &SQLtable) BY ODBC; * remove
EXEC (ALTER TABLE &SQLtable ADD &Dummy FLOAT) BY ODBC; * add a
EXEC (ALTER TABLE &SQLtable DROP COLUMN &MSSQLvars) BY ODBC; * remove
EXEC (ALTER TABLE &SQLtable ADD &SASVars) BY ODBC; * define
EXEC (ALTER TABLE &SQLtable DROP COLUMN &Dummy) BY ODBC; * remove
DISCONNECT FROM ODBC;
INSERT INTO &DataBase SELECT * FROM SAS_dataset; * export
data via ODBC;
Regards - Jim.
Jim Groeneveld, Netherlands
On Fri, 6 Aug 2010 06:18:34 -0400, Jim Groeneveld <jim.1stat@YAHOO.COM> wrote:
>First of all I have to point to the drawbacks of PROC APPEND, even with the
>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:
>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;
> CONNECT TO ODBC (DSN="SQL server" USER=username PWD=password); *
> EXEC (DELETE FROM &DataBase) BY ODBC; * remove
> EXEC (ALTER TABLE &DataBase ADD &Dummy FLOAT) BY ODBC; * add a
> EXEC (ALTER TABLE &DataBase DROP COLUMN &MSSQLvars) BY ODBC; * remove
> EXEC (ALTER TABLE &DataBase ADD &SASVars) BY ODBC; * define
> EXEC (ALTER TABLE &DataBase DROP COLUMN &Dummy) BY ODBC; * remove
> DISCONNECT FROM ODBC;
> INSERT INTO &DataBase SELECT * FROM SAS_dataset; * export
>data via ODBC;
>&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
>I hope these suggestions may prove to be helpful.
>Regards - Jim.
>Jim Groeneveld, Netherlands
>On Thu, 5 Aug 2010 08:31:15 -0400, Dave Brewer <david.brewer@UC.EDU> wrote:
>>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
>>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 *
>> 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.