I am trying to find the most efficient way of getting a 1.8GB SAS Dataset file into a MS SQL 2014 table.
Our scenario is as follows.
We have a SAS Grid 9.3 on Linux Nodes.
We have a MS SQL 2014 Database
We only have ODBC connectivity which we use to connect to SQL Server because the SAS DataDirect Driver 7.0.1 that ships with SAS9.3 does not support MSSQL 2014. We will need to upgrade to SAS9.4 to use the DataDirect Driver.
I discovered that I cannot use BULKLOAD option in a Proc Append because BULKLOAD is not available for Linux / Unix platforms using the ODBC MS SQL Native Driver and unixODBC Driver Manager v2.3.2.
As far as I can tell the Proc Append without the BULKLOAD=yes option is extremely inefficient. Can I use a Bulk Insert statement that may work? Has anyone tried it successfully on ODBC?
I am also trying to see if there is perhaps a libname Statement example which I can use that will use options like INSERT_SQL=YES INSERTBUFF=10000 that may speed up the process.
Is there anyone else in the whole wide world who has had success with a similar attempt?
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.