04-02-2012 04:09 PM
Here is some sample code that I use to read in an Excel file:
proc import datafile="T:\X21AB100\X2100LIXI.xlsx" out=work.lixi dbms=excelcs replace;
proc print data=lixi;
Every once-in-a-while the proc import hangs when trying to read a file. No error message is displayed.
It just hangs until I either kill the SAS window or end the "pcfservice" process. If I copy the .xlsx file
to a new name and change the pathname in the code, it works. Once this code hangs on a .xlsx
file no amount of copying or renaming will make sas read that same file out of the same directory.
Is there a way to debug this? I do not have this file open in Excel while I am trying to run this code.
04-03-2012 03:26 PM
I have found this article:
It appears that mapped network drives are not supported when using the pcfilesserver which aparently one uses when importing data from .xlsx files. I tried changing "T:" to the UNC equivalent and the import function worked. The think that I don't understand is why drive letters work most of the time. I would have thought that if drive letters were not supported in the pcfilesserver then they would never work. In my case they work most of the time but sometimes cause SAS to hang.
Does everyone else use UNC paths when importing data from .xlsx files?
04-03-2012 06:51 PM
Not just when using PC file servers. I prefer UNC's all the time. They are much more portable from machine to machine and user to user.
The only time you need to map drives is if you are running an old DOS program. Wordperfect 5.1 anyone?
04-03-2012 07:56 PM
Tom: Do you REALLY have to select a Canadian company to pick on?
Wes: A nice and free tool for identifying UNCs was mentioned in the following SAS-L post: http://listserv.uga.edu/cgi-bin/wa?A2=ind0803b&L=sas-l&D=0&P=3585
04-04-2012 07:12 AM
We have had a track with tech support on this problem several months ago. They have suggested to install some packs. But no success.
Finally, tech support wrote that they have made tests on their machines and everything works fine.
I have this issue with xls files. I did not imported xlsx files yet.
I am running EG 4.3 (SAS 9.2) on Windows Server x64.
04-03-2012 11:59 PM
Try replacing dbms=excel.Hope this helps.
proc import datafile="T:\X21AB100\X2100LIXI.xlsx" out=work.lixi dbms=excel replace;
04-05-2012 04:31 PM
I contacted SAS support about this problem. They said a number of things can cause this. The first fix we tried appears to have fixed the problem. The fix was to install the new 2010 ODBC drivers from the link below:
After installing this I have not had proc import hang... yet.