Date: Fri, 6 Aug 2010 07:08:34 -0400
Reply-To: Dave Brewer <david.brewer@UC.EDU>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Dave Brewer <david.brewer@UC.EDU>
Subject: Re: PASSTHRU SQL Question
Content-Type: text/plain; charset=ISO-8859-1
Hi Jim,
Thanks much for your suggestions.
I am not concerned about the drawbacks of PROC APPEND; I won't be creating
new variables or changing lengths as these are set by my data dictionary,
but I am interested in speeding up my time to appending my new data from
SAS to an existing SQL table. My data will contain the same attributes on
both sides.
I am going to d/l your file and see if it helps.
Thanks again for sharing your knowledge and helping me with my problem.
Dave
On Fri, 6 Aug 2010 06:42:37 -0400, Jim Groeneveld <jim.1stat@YAHOO.COM>
wrote:
>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
| 
