topic Re: Data transfer from SAS EG to SQL Server in SAS Programming

Data transfer from SAS EG to SQL Server

RENATA1 — Thu, 04 Mar 2021 15:38:21 GMT

Greeting! Could you please assist me with improving my code in order to transfer data from SAS EG (version 7.15) to Microsoft SQL Server in most efficient and fastest way? How can I improve my code or what can I add to the libname statement to be able to transfer data faster – large datasets with over 200 million rows?

LIBNAME out_srv SQLSVR DATAsrc=&datasrc. USER="&userid." PASSWORD="&spwd." SCHEMA="&schema." DBCOMMIT=10000 INSERTBUFF=10000;

PROC SQL NOPRINT;

CONNECT TO SQLSVR(DATAsrc="&datasrc." USER="&userid." PASSWORD="&spwd.");

CREATE TABLE out_srv.table1 AS

SELECT * FROM WORK.TEMPFILE;

QUIT;

RUN;

Re: Data transfer from SAS EG to SQL Server

Shmuel — Thu, 04 Mar 2021 15:50:34 GMT

Usually the communication line transfer is fast but the bottle neck is the I/O - read from hard disk and write to hard disk. You may look for options how to assign more buffers at sas side and if possible on sql server side too.

Re: Data transfer from SAS EG to SQL Server

Sajid01 — Thu, 04 Mar 2021 18:02:11 GMT

Agreeing completely with what Shmuel has said, I would suggest take computing to where the data resides(In database processing)
Either use SQL pass through or perform the processing to the extent possible in SQL server..
Here are some papers on the subject
https://www.mwsug.org/proceedings/2014/SA/MWSUG-2014-SA03.pdf

https://www.mssqltips.com/sqlservertip/2703/using-sas-access-and-proc-sql-to-retrieve-sql-server-data/

http://support.sas.com/resources/papers/proceedings10/300-2010.pdf

Re: Data transfer from SAS EG to SQL Server

RENATA1 — Sat, 06 Mar 2021 02:17:14 GMT

Thank you both very much for your input and suggestions! This process is new to me, I was hoping there is a suggestive number when it comes to insertbuff and/or dbcommit; but it sounds like getting that right number is only by ‘experimenting’ 😊

Is the process of creating an empty table first and then inserting records faster than what I am doing in my code?

Re: Data transfer from SAS EG to SQL Server

Shmuel — Sat, 06 Mar 2021 06:42:21 GMT

I have no experience with SQL server, anyhow creating an empty table is usually very fast.

Most of the time is the I/O time of copying data from one HD to another.

Is it possible to replace the HD to a faster model? or use some other protocols for connection, enabling parallel transfer ? I'm not expert of those methods.