Date: Fri, 6 Aug 2010 06:18:34 -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
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.