BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Albert1201
Calcite | Level 5

Hi,

 

I am using SAS Enterprise Miner version 13.2 and trying to import a large data table (about 20GB) from Microsoft SQL Server to SAS Enterprise Miner.  I tried two methods but neither worked for me.

 

Method 1: First save SQL server data in a csv file and then use File Import Node in SAS. The size of data is a problem. The query in SQL failed when I try to select all 20GB data in that table. This method did work if I only select part of the data (ex. first 10,000 rows)

 

Method 2: Create a library in SAS that points to the SQL database. The IT team in our company helped me create a library but I can't see it in SAS Enterprise Miner library list. Unfortunately, the IT team doesn't know how to connect SAS and SQL server.

 

Does anyone know how to import large amount of data from Microsoft SQL Server to SAS Enterprise Miner?

 

Thanks a lot

Albert

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

Normally you use a SAS product like SAS/ACCESS to SQL Server or SAS/ACCESS to ODBC. Do you have either of these? If you don't know check with your SAS admin or run this code - proc setinit; run; - to see if they are included in your SAS licence. 

View solution in original post

2 REPLIES 2
SASKiwi
PROC Star

Normally you use a SAS product like SAS/ACCESS to SQL Server or SAS/ACCESS to ODBC. Do you have either of these? If you don't know check with your SAS admin or run this code - proc setinit; run; - to see if they are included in your SAS licence. 

DougWielenga
SAS Employee

SAS Enterprise Miner relies on the underlying SAS system to read and write data.  Importing a 20 GB csv. file into SAS Enterprise is not a good approach as you found since the first thing SAS will do is to create a SAS data set.  As mentioned before, if you have the appropriate SAS/ACCESS product(s) licensed, you can simply define a libname statement in SAS Enterprise Miner to gain access to that data. If SAS can read the data, then so can SAS Enterprise Miner using the same libname statement. Simply put this libname statement in the Project Start Code for SAS Enterprise Miner so that the data can be allocated as a Data Source in SAS Enterprise Miner.  SAS Enterprise Miner does not copy the entire data set in most situations but simply creates a view pointing to the data and the view is updated during processing.

 

Please note that SAS Enterprise Miner requires you to login so you must make sure that the id which you are using to access SAS Enterprise Miner has the appropriate permissions to read/write to the location where the data is located.  If you defined the libname MYLIB where the data in question is located, SAS Enterprise Miner will write temporary files to that location during processing. 

 

As a result, if the id you used to access SAS Enterprise Miner...

... does not have read permission to access the data, you cannot even see the data (this is also the case if you have not defined the libname pointing to the location as part of the startup for SAS Enterprise Miner or in the Project Start Code of the project in question)

... does not have write permission to the location of the data, Enterprise Miner cannot process the data as needed

 

Hope this helps!

Doug

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 choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 6966 views
  • 2 likes
  • 3 in conversation