topic Re: append data to sql data in SAS Programming

append data to sql data

mariopellegrini — Wed, 21 Jun 2023 05:41:03 GMT

Good morning. A proc append in a SQL data library takes a very long time to append data. However, the same data on a basic sas library takes a few seconds. I report the 2 example logs.

I'm asking for suggestions on which method is better to hang data on a Sql Server data library from sas:

proc append base = LIB_SAS.TAB1 data = backup.WXGC21K force nowarn;

run;

NOTE: Appending BACKUP.WXGC21K a EDWH_ODS.TAB1.

NOTE: FORCE is specified, so dropping/truncating will occur.

NOTE: There were 16945246 observations read from the data set BACKUP.WXGC21K.

NOTE: 16945246 observations added.

NOTE: The data set EDWH_ODS.TAB1 has 16945246 observations and 4 variables.

NOTE: PROCEDURE APPEND ha utilizzato (tempo totale di elaborazione):

real time 14.97 seconds

-------------------------------------------------------------------------------------------------

proc append base = LIB_SQL.TAB1 data = work.WU98D7R force nowarn;

run;

NOTE: Appending WORK.WU98D7R a LIB_SQL.TAB1.

NOTE: FORCE is specified, so dropping/truncating will occur.

NOTE: There were 16945246 observations read from the data set WORK.WU98D7R.

NOTE: 16945246 observations added.

NOTE: The data set LIB_SQL.TAB1 has . observations and 4 variables.

NOTE: PROCEDURE APPEND ha utilizzato (tempo totale di elaborazione):

real time 2:56:25.66

user cpu time 2:21.54

system cpu time 4:19.78

memory 593.34k

OS Memory 18784.00k

Timestamp 20/06/2023 05:34:11 p.

Step Count 243 Switch Count 0

Re: append data to sql data

HenryKobus — Wed, 21 Jun 2023 06:33:09 GMT

I do not know much about the environment you are working in. I assume that proc append just take a "few" observations and gives it to SQLServer who appends them. After doing so maybe the SQLServer starts re-creating an index or so, waits for completion and after that accepts the next package.

I would try to get the data to be appended in the SQLServers database and let the SQLServer doing the append. Something like this :

proc upload data = backup.WXGC21K out = LIB_SQL.WXGC21K;run;

proc sql; 
	connect to  ODBC (dsn="MySQLServerConnectString" schema=dbo);
		execute (INSERT INTO TAB1  
					SELECT *
					FROM dbo.WXGC21K) by ODBC;
	disconnect from ODBC;
quit;

proc sql; 
	connect to ODBC (dsn="MySQLServerConnectString" schema=dbo);
		execute (Drop Table WXGC21K) by ODBC;
	disconnect from ODBC;
quit;;

or if you have licenced SAS/Interface for Access to SQLServer you may try this:

proc upload data = backup.WXGC21K out = LIB_SQL.WXGC21K;run;

proc sql; 
	connect to  sqlsvr (user="Domain\UserID" password="MyPassword" datasrc="MySQLServerConnectString" schema=dbo);;
		execute (INSERT INTO TAB1  
					SELECT *
					FROM WXGC21K) by sqlsvr;
	disconnect from sqlsvr;
quit;

proc sql; 
	connect to sqlsvr (user="Domain\UserID" password="MyPassword" datasrc="MySQLServerConnectString" schema=dbo);
		execute (Drop Table WXGC21K) by sqlsvr;
	disconnect from sqlsvr;
quit;;

Re: append data to sql data

mariopellegrini — Wed, 21 Jun 2023 08:25:55 GMT

Thank you. But I can't connect from proc sql.

LIBNAME EDWHDSST SQLSVR Datasrc=EDWHDSST SCHEMA=DBO AUTHDOMAIN="SQLSRVAuth" ;

NOTE: Libref EDWHDSST was successfully assigned as follows:
Engine: SQLSVR
Physical Name: EDWHDSST

But this proc sql:

proc sql;
connect to SQLSVR as mydb (Datasrc="EDWHDSST" SCHEMA="DBO" AUTHDOMAIN="SQLSRVAuth" );
execute (SELECT * FROM TABSQL) by sqlsvr;
disconnect from sqlsvr;

returns the following error:

27 proc sql;
28 connect to SQLSVR as mydb (Datasrc="EDWHDSST" SCHEMA="DBO" AUTHDOMAIN="SQLSRVAuth" );
NOTE: Credential obtained from SAS metadata server.
ERROR: Invalid option name SCHEMA.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

or using username and password:

proc sql;
connect to SQLSVR as mydb (Datasrc="DBO" user=user password=xxxx);
execute (SELECT * FROM TABSQL) by sqlsvr;
disconnect from sqlsvr;
quit;

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver
specified

Re: append data to sql data

HenryKobus — Wed, 21 Jun 2023 08:19:15 GMT

The schema is needed in LIBNAME statement. For pass through the tablespace follows the FROM statement and then a period + table name:

proc sql;
connect to SQLSVR as mydb (Datasrc="EDWHDSST" AUTHDOMAIN="SQLSRVAuth" );
execute (SELECT * FROM dbo.TABSQL) by sqlsvr;
disconnect from sqlsvr;

Re: append data to sql data

mariopellegrini — Wed, 21 Jun 2023 11:04:59 GMT

Okay. Thank you.

What could I do instead of the proc upload?

It seems that the possibility is not installed because I get the following error:

ERROR: Procedure UPLOAD not found.

Re: append data to sql data

Kurt_Bremser — Wed, 21 Jun 2023 12:26:06 GMT

3 hours of real time vs. 7 minutes of CPU time points to bandwidth issues between the SAS and DB server.

And 7 minutes vs. 15 seconds also means that the whole setup of the connection is inefficient.

Get in touch with your SAS and DB admins, and study the documentation for your specific database connection (ODBC).

Also see if you have ACCESS to SQL server licensed, it provides a more direct path (SAS - DB client - server instead of SAS - ODBC OS component - ODBC driver - server).

Re: append data to sql data

PaigeMiller — Wed, 21 Jun 2023 12:19:24 GMT

@mariopellegrini wrote:

Good morning. A proc append in a SQL data library takes a very long time to append data. However, the same data on a basic sas library takes a few seconds. I report the 2 example logs.

I'm asking for suggestions on which method is better to hang data on a Sql Server data library from sas:

Better in what sense? If the criterion for "better" is the amount of time it takes, you have already found the answer.

Re: append data to sql data

mariopellegrini — Wed, 21 Jun 2023 12:26:12 GMT

Yes, but I need to bring data to SqlServer

Re: append data to sql data

mariopellegrini — Wed, 21 Jun 2023 12:47:36 GMT

Thank you.

But assuming PROC UPLOAD worked, could it greatly improve data transfer times from sas to sql? ie i mean doing a dry proc upload of a table from sas to sql

Another question: can there not be a "SQL Pass-Through" code that makes data transfer efficient?

Re: append data to sql data

Kurt_Bremser — Wed, 21 Jun 2023 12:43:19 GMT

PROC UPLOAD is part of SAS/CONNECT, which is used to communicate between two SAS processes. It is not used to connect to a database server.

Re: append data to sql data

HenryKobus — Wed, 21 Jun 2023 12:51:11 GMT

You may use a datastep or proc datasets / proc copy instead:

data LIB_SQL.WXGC21K;
    set backup.WXGC21K ;
run;

Re: append data to sql data

SASKiwi — Thu, 22 Jun 2023 02:42:04 GMT

In my experience, when appending SAS data to SQL Server tables you need to experiment with the INSERTBUFF data connection option. Changing this option can make a huge difference in table load times as the default value is not optimal. Try something like this, then experiment by increasing or reducing the value to see the impact:

LIBNAME EDWHDSST SQLSVR Datasrc=EDWHDSST SCHEMA=DBO AUTHDOMAIN="SQLSRVAuth"  insertbuff = 10000;

Also to connect to SQL Server in PROC SQL you can use your LIBNAME:

proc sql;
  connect using EDWHDSST;
  create table MyTable as
  select * from connection to EDWHDSST
  (SELECT * FROM TABSQL) ;
  disconnect from EDWHDSST;
quit;

Re: append data to sql data

mariopellegrini — Thu, 22 Jun 2023 08:05:24 GMT

Thank you very much! Thanks to this option the elaboration went to 3'39"!

Is 10,000 usually what works best? Do you still advise me to try other values? Changing by how much?

Re: append data to sql data

SASKiwi — Thu, 22 Jun 2023 20:03:42 GMT

@mariopellegrini - I don't think there is an ideal number for everyone. I usually try a few different values, like 1,000, 2,000, 5,000, 10,000, 15,000, 20,000 to see which gives the best performance. We currently use 10,000 for most of our loads and that works well. There is no point using a larger number than the number of rows you are appending though.