08-06-2014 01:45 PM
I am running SAS Enterprise Guide version: 22.214.171.12419 (32-bit)
Running in WOW64 (32-bit subsystem): Yes
Running as a 64 bit process: No
and Access 2010 version 14.0.6023.1000 (32-bit)
I am attempting to import a table (1.1 million rows, 35 columns) from a database in Access to SAS EG. Every time I use File > Import Data and select the file I get an error that "SAS has encountered a problem and needs to close." Below is the error. More details can be provided if needed. Needless to say, the import fails. What is causing this, and how can I fix it/is there an alternative method? Currently we have been forced to export the table from Access to Excel in two pieces, then import it to SAS and stitch it back together. It's inefficient and time-consuming, so any help would be greatly appreciated! Thank you.
-------------------------- Technical Information Follows --------------------------
Exception type: System.IO.IOException
Message: No more data is available.
Target Site: Win32Error
08-07-2014 09:26 PM
Do you have the SAS product SAS/ACCESS to PC Files? In EG in Server List, check in properties for the SAS products installed. If you do then you can run a SAS program/EG process to try importing the data.
08-08-2014 08:42 AM
It appears the server does have ACCESS to PC Files installed, but when I try Proc Import that fails as well. Here is I have tried to run:
PROC IMPORT OUT=fakeserver.cxb_iha_ops_2014_08_07
DATATABLE='Member Level Data'
DATABASE="&Q:\fakepath\Member Level Detail1.accdb";
What's the problem with my code? I'm still getting familiar with SAS, so I welcome corrections/alternatives. Thank you!
08-08-2014 08:49 AM
To diagnose your code, you absolutely need to post the log that contains the error message(s). Since I'm closing shop for the weekend, it may be some time before I can help you further.
08-08-2014 08:58 AM
Apologies, Kurt. I meant to post the log and forgot.
1 The SAS System 08:26 Friday, August 8, 2014
1 rsubmit task1;
NOTE: Remote submit to TASK1 commencing.
1475 %put NOTE: Using grid node &SYSHOSTNAME to run task.;
NOTE: Using grid node isbsas09 to run task.
1476 %let _EGRC=0;
1478 OPTIONS PAGENO=MIN;
1479 %put NOTE: The current task will be submitted to the grid due to the server configuration policy.;
NOTE: The current task will be submitted to the grid due to the server configuration policy.
1480 %LET _CLIENTTASKLABEL='Program1';
1481 %LET _CLIENTPROJECTPATH='/sascode/myuserID/IHAWeeklyUpdate.egp';
1482 %LET _CLIENTPROJECTNAME='IHAWeeklyUpdate.egp';
1483 %LET _SASPROGRAMFILE=;
1485 ODS _ALL_ CLOSE;
1486 OPTIONS DEV=ACTIVEX;
2 The SAS System 08:26 Friday, August 8, 2014
1487 %let sasworklocation="&_eg_gridworkpath/";
1488 GOPTIONS XPIXELS=0 YPIXELS=0;
1489 FILENAME EGSR "&_eg_gridworkpath/%sysfunc(uuidgen(0))_eggen";
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
1490 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
1490! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE
1490! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
1492 GOPTIONS ACCESSIBLE;
1493 PROC IMPORT OUT=servername.cxb_iha_ops_2014_08_07
1494 DATATABLE='Member Level Data'
1495 DBMS=ACCESS REPLACE;
ERROR: DBMS type ACCESS not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
OS Memory 14372.00k
Timestamp 08/08/2014 08:39:18 AM
Page Faults 0
Page Reclaims 25
Page Swaps 0
Voluntary Context Switches 6
Involuntary Context Switches 5
Block Input Operations 0
Block Output Operations 0
WARNING: Apparent symbolic reference Q not resolved.
1496 DATABASE="&Q:\fakepath\Member Level Detail\Member Level Detail1.accdb"
1502 GOPTIONS NOACCESSIBLE;
1503 %LET _CLIENTTASKLABEL=;
1504 %LET _CLIENTPROJECTPATH=;
1505 %LET _CLIENTPROJECTNAME=;
1506 %LET _SASPROGRAMFILE=;
1509 ODS _ALL_ CLOSE;
1512 QUIT; RUN;
1513 %sysrput _EGRCGRID=&_EGRC;
NOTE: Remote submit to TASK1 complete.
2 %let _EGRC=&_EGRCGRID;
Hope that helps. The items in strikethrough (user ID, server name, filepath), have been altered for confidentiality.
08-08-2014 09:20 AM
The PROC IMPORT step is running in your SAS Grid environment on a UNIX/Linux platform. This session probably does not have access to the path where your MDB sits, and even if it did the SAS session would not be able to process this import of a Windows database directly. Instead, it would require a PC Files Server specification and use of the DBMS=ACCESSCS option to trigger it. I'm guessing that you don't have all of that set up, but if you do a SAS admin would need to tell you exactly what the PC File Server host and port specification should be.
Another possibility to get the data into the session via EG -- you could try File->Open->From ODBC. Then you could use the Microsoft Access ODBC driver to open the database file. However, for this large table it might be slow -- it's not usually the method we recommend.
08-08-2014 02:38 AM
Do you get the error immediately or does it take some time?
08-08-2014 08:42 AM
Could it be that you are running out of space for the work library? It may be that EG tries to send a piece of data (converted into a SAS-readable format by MS DAO) to the workspace server so that SAS can take a look at it.
Another possible failure point is of course the MS DAO, which may not be happy with the data it reads from Access.
Consider a call to TS if nobody here has another idea.
08-08-2014 09:05 AM
From the error and from looking at other tracks, I think that this is failing when EG is trying to decide whether there is a Microsoft data access engine (ACE) driver installed.
It's looking for the ACE footprint in the Windows registry. For this 32-bit process on your 64-bit machine, this would be in:
Specifically, it's looking to see if you have MS Access:
Or at least the ACE driver:
But this step seems to be failing while simply getting the list, implying that you might not even have the rights to query this. Perhaps there is a security package that is getting in the way? Can you view these values in RegEdit?