Help using Base SAS procedures

Creating a table on SQL server from a SAS table

Reply
Contributor
Posts: 20

Creating a table on SQL server from a SAS table

Every month I must recreate in SAS a table that has close to 5 million records, along with several other tables, some similarly large, some much smaller. I then must replace the same tables on our SQL server.

Using the following code currently takes approximately 3 hours for the 5-million record table to be created, with similar code for the other tables bringing the entire update process to close to 8 hours:

libname sql odbc dsn='sqldsn';

libname bw '\\servername\librarypath';

proc sql;

     drop table sql.sqltable;   (this only takes seconds)

proc sql;

     create table sql.sqltable as     (this takes 3 hours)

     select *

     from bw.sastable;

quit;

In looking for something that might speed up the monthly process, I came across the explicit pass-through syntax. I was able to drop the table successfully, but I get a syntax error in the create table code.  This it the new code I've been attempting to use:

libname bw '\\servername\librarypath';

proc sql;

     connect to odbc (dsn='sqldsn');

     execute (drop table sqltable) by odbc;

     execute (create table sqltable as select * from bw.sastable) by odbc;

     disconnect from odbc;

quit;

The error I get is:

 

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near

the keyword 'as'.

I have researched this online and haven't found anything that helps. Thus, I'm not sure it's even possible to execute a create table statement in this manner. If it is possible, perhaps it's just that, because I am using the pass-through syntax and processing the code on the SQL server, SQL is unable to recognize the SAS library and table from which the SQL table should be created. Does anyone have suggestions on making this work? Or am I stuck with the original implicit create table method and 8-hour monthly process?

Thank you,

Tamara

PROC Star
Posts: 1,085

Re: Creating a table on SQL server from a SAS table

Check out "Bulk Loading for ODBC" in the SAS documentation.

When you use the first pattern for copying a SAS dataset to a table in a SQL database, SAS implements it via a series of SQL "insert" statements. This, as you have noted, is very slow!

I don't believe that your pass-through attempt will work, because when your database receives "from bw.sastable", it won't know what that is, as it's a SAS object.

Transferring data from SAS to a DBMS is a common problem, and to resolve it SAS has added special options to SAS/Access that will write the data to a flat file, and then call the DBMS bulk loader facility to load it to the DBMS. This should speed things up by 10 to 100 times.

Tom

Contributor
Posts: 20

Re: Creating a table on SQL server from a SAS table

Thank you, Tom!

I did not realize it was processed as "insert" statements. Yikes!

I will try the bulk loader facility. Even a 10x increase in speed would be a huge improvement. I read in the SAS documentation that it is only available for Windows platforms. Do you know if there is an equivalent for UNIX? We will be moving away from our Windows platform and onto UNIX by the end of the year, so I will be back in this same situation at that time.

Tamara

PROC Star
Posts: 1,085

Re: Creating a table on SQL server from a SAS table

Yes, with the "insert" statement method, sometimes I think I can actually see the records moving over the wire from the SAS server to the database server. ;-)

In terms of UNIX:

- What is your DBMS?

- Is your SAS server moving from Windows to UNIX?

- If your DBMS isn't MS SQL Server, is it currently on Windows, and if so, will it be moving to UNIX?

Tom

Contributor
Posts: 20

Re: Creating a table on SQL server from a SAS table

Tom,

I hope I'm about to answer these questions correctly!

In terms of UNIX:

- What is your DBMS?     Our SQL DBMS? It is MS SQL Server.

- Is your SAS server moving from Windows to UNIX?  We currently use PC SAS, with the data repository residing on a Windows virtual share. But we will be discontinuing our PC SAS and switching to Enterprise SAS on the UNIX - a big switch for some of my coworkers who have only ever used PC SAS.

- If your DBMS isn't MS SQL Server, is it currently on Windows, and if so, will it be moving to UNIX?  Only SAS is moving to UNIX. The SQL DBMS will remain on Windows.

Tamara

Respected Advisor
Posts: 3,886

Re: Creating a table on SQL server from a SAS table

Hi Tamara

According to doc bulkloading is not available for UNIX so it's may be not worth to use it now.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002105630.htm

It's normally beneficial to implement a database table as optimized as possible for your purpose (eg. with partitions and the like). It's therefore also kind of best practice to not drop and recreate the table afterwards but to truncated it instead (http://support.sas.com/kb/4/560.html).

Also really worth to check are some of the settings especially "dbcommit" and "insertbuff". The default value of "insertbuff" is "1". Having the maximum possible value here will speed up things quite a lot.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm

And then when it comes to reading the data make sure you check out all the information about it to set also these options as optimised as possible so allowing for multithreading

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002273726.htm

Frequent Contributor
Posts: 124

Re: Creating a table on SQL server from a SAS table

Tamara, what Tom said about SQL Server not recognizing the sas dataset is correct.  However, your error is happening before SQL Server even gets that far.  The only way to create a SQL Server table on the fly is by using SELECT INTO.  For example, consider this

SELECT TOP 10 *

  INTO #test

  FROM sysobjects

This will create a temp table on the fly, copying the structure from the sysobjects table.  Now, even if you changed your create statement to something like the above, you would still encounter the problem of SQL Server not finding your SAS data.  One option would be to use SAS to put a flat file somewhere in a directory that is accessible to SQL Server, let's call it 'c:\sql_server_share\yourfile.txt'.  Now, you will have to create a procedure on your SQL Server that will be something like this...

CREATE PROCEDURE spImportSasFile AS

IF OBJECT_ID('dbo.NewTable','u') IS NOT NULL

DROP TABLE dbo.NewTable;

SELECT *

  INTO dbo.NewTable

  FROM OPENROWSET

(

  'MSDASQL',

     'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\sql_server_share;',

     'SELECT * FROM yourfile.txt'

    );

Once you have created the procedure, your ODBC call will be something like this...

execute (exec spImportSasFile) by odbc;

Now, this is wholly dependent upon either your permissions to create this procedure on your SQL Server, or the DBA's willingness to help.  One other thing, in order to allow the reading in of external data sources, you will have to set some configuration options.  If you can execute the below, it should work for you...

EXEC sp_configure 'show advanced options',1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'Ad Hoc Distributed Queries',1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'remote query timeout (s)',28800

GO

RECONFIGURE WITH OVERRIDE

GO

There is a reason why the first two options are set to not allow ad hoc access to external data, so you should check with your DBA to discuss.  I hope this helps.

Greg

Contributor
Posts: 20

Re: Creating a table on SQL server from a SAS table

Greg,

Thank you for this information. I inherited this task from someone who recently left the company. After running the code over a few months and having each run take so long, I figured I should look into whether there is a quicker way to accomplish the task. I didn't expect it to be quite so complicated! It's definitely outside of my experience level and while I'm sure I could get it working with time, I unfortunately don't have much time to spend on it due to other work requirements. So I think I am stuck with the slow and inefficient method for now. Thankfully it's only once a month and I can run it overnight while people are not using the db.

Since we will be going to UNIX SAS by the end of the year and it doesn't support bulk loading, I think Patrick's comment that it may not be worth it to get the bulk loading working now is spot on due to the amount of time it will take me to figure it out.

If anyone has the knowledge to continue to point me in the right direction for making this work between our new UNIX server and the SQL server, I'm still very open to that help! I do have access to the UNIX server already, so I can start getting things like this working on it.

Thank you again for all of the advice!

Tamara

PROC Star
Posts: 1,085

Re: Creating a table on SQL server from a SAS table

Unfortunately, running SAS on a UNIX server connecting to MS SQL Server, which by definition has to run on Windows, is one of the more challenging combinations. I agree, that with you moving to UNIX servers for SAS, it's not worth digging into the bulk loading options.

I'm not an expert on SQL Server, but gsnidow's approach looks very similar to what the bulk loader does, so if it's easy to implement it might really work well for you.

If not, Patrick's suggestions to check those setting is worth following up with. Changes to them certainly might help.

And last, fly this by SAS Tech. Support. They may be aware of other approaches; surely this comes up fairly often.

Good luck!

  Tom

Frequent Contributor
Posts: 124

Re: Creating a table on SQL server from a SAS table

Tamara

To Tom's point, it's not that difficult to set it up on SQL Server.  It's like this: you can have someone who's native language is Chinese, say, and who speaks limited English, read you a set of instructions that are written in Chinese and need to be translated to English.  Then you have one opportunity to carry out the instructions perfectly on the first try.  Coming from the SQL Server world, I can tell you that you will be far better off just dropping off the file for SQL Server, and letting it do what it does in it's own language, so you don't have to figure out how to translate the instructions.  That way, if you change SAS platforms again, the only thing you ever need to worry about is getting the file to the drop off directory.  There are a whole host of options for importing flat files into SQL Server, just like there are in SAS, and the one I provided above is just one of them.  I am basically floundering here trying to use SAS, but thanks to the good folks here I have been able to muddle through what to you all are probably routine actions.  It may be a faux pas to recommend another forum, but since your question is specific to SQL Server, if you can get the file to it and want to pursue handling it from the SQL Server side, look me up on www.sqlservercentral.com.  That is absolutely the best resource for all things SQL Server.  You can either post a question on the forum specific to your version of SQL Server, or you can post a message to me directly.  You can find me by searching the profiles for 'Greg Snidow'.  Good luck.

Contributor
Posts: 20

Re: Creating a table on SQL server from a SAS table

Thanks Greg! I will probably get one of our SQL-knowledgeable programmers to write up the SQL code for me, having them tell me how and to where they want me to output the SAS file for SQL processing, so that I don't have to mess with the SQL side of it. But I will definitely keep this info on hand and get in touch with you in the SQL forum if we have any problems. I really appreciate all the advice!

Tamara

Ask a Question
Discussion stats
  • 10 replies
  • 13963 views
  • 6 likes
  • 4 in conversation