BookmarkSubscribeRSS Feed
shwilliams4
Obsidian | Level 7

Dear SAS Community,

Does anyone have experience using Azure SQL Server to read in a sas7bdat file. I found this article saying R can do it as a plug into SQL Server. The reason I ask is that in using a DSN libname to Azure, putting the sashelp.class (19 records, less than 50 KB) table on Azure takes 33 seconds and I need Azure to handle 90 GB of data transfers.

 

https://social.msdn.microsoft.com/Forums/en-US/da14839c-94bf-4ba3-b0cc-e3801d53760d/use-sas7bdat-for...

 

I would prefer a direct read without adding another tool. Thank you,

 

Sumner

5 REPLIES 5
AhmedAl_Attar
Rhodochrosite | Level 12

Hi,

 

If you are using SAS/ACCESS to ODBC to interact with MS SQL Server on Azure, check out the BULKLOAD= option for you ODBC libname statemant.

 

and while you are at it, I would recommend enabling SAS/ACCESS tracing/debugging via the following options

 

/*Turn on SAS tracing */
options sastrace=”,,t,” sastraceloc=saslog nostsuffix;

 

For additional details and usage examples, Check this SGF paper "SASTRACE: Your Key to RDBMS Empowerment"

 

Hope this helps,

Ahmed

 

 

 

shwilliams4
Obsidian | Level 7

The issue is I get this message. I hadnlt considered using sastrace. Thank you for reminding meof it.

 

ERROR: BCP initialize error: [Microsoft][ODBC Driver 13 for SQL Server]Connection is not

enabled for BCP

 

Does anyone know how to turn BCP on for Azure?

AndreasMenrath
Pyrite | Level 9

Hi Sumner,

 

the R Services are only available for SQL Server on premise. I think Azure SQL DB does not support R code and R packages yet.

 

If you plan to shift that much data into the cloud you should also consider other scenarios like exporting your data into CSV files and send the data on a HDD disk to your Azure datacenter. You can process the data much faster if it is already located in the cloud Smiley Happy

Ksharp
Super User

Yeah. You push a too big table into DB.

Here are some options could get you a little faster.

 

options DBIDIRECTEXEC;
libname x ...... insertbuff=10000 bulkload=yes dbicommit ;

shwilliams4
Obsidian | Level 7

Thank you. DBIcommit isn;t an option is SAS 9.2. The

options DBIDIRECTEXEC; seems to have sped up the process, but not by enough to use it.

 

I am going with the BCP load option available in SQL Server

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2683 views
  • 1 like
  • 4 in conversation