topic Re: proc import hangs when reading .xlsx file in SAS Procedures

proc import hangs when reading .xlsx file

WesBarris — Mon, 02 Apr 2012 20:09:27 GMT

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;

run;

proc print data=lixi;

title 'Test';

run;

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.

proc import hangs when reading .xlsx file

art297 — Mon, 02 Apr 2012 20:27:04 GMT

The following might be applicable: http://support.sas.com/kb/40/409.html

proc import hangs when reading .xlsx file

WesBarris — Tue, 03 Apr 2012 19:26:56 GMT

I have found this article:

http://support.sas.com/kb/35/064.html

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?

proc import hangs when reading .xlsx file

Tom — Tue, 03 Apr 2012 22:51:48 GMT

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?

proc import hangs when reading .xlsx file

art297 — Tue, 03 Apr 2012 23:56:42 GMT

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

proc import hangs when reading .xlsx file

shivas — Wed, 04 Apr 2012 03:59:06 GMT

Hi,

Try replacing dbms=excel.Hope this helps.

proc import datafile="T:\X21AB100\X2100LIXI.xlsx" out=work.lixi dbms=excel replace;

run;

Thanks,

Shiva

proc import hangs when reading .xlsx file

Oleg_L — Wed, 04 Apr 2012 04:20:07 GMT

I have the same problem even with UNC paths.

Oleg.

proc import hangs when reading .xlsx file

Oleg_L — Wed, 04 Apr 2012 11:12:41 GMT

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.

Regards,

Oleg.

Re: proc import hangs when reading .xlsx file

WesBarris — Thu, 05 Apr 2012 20:31:09 GMT

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:

http://www.microsoft.com/download/en/details.aspx?id=13255

After installing this I have not had proc import hang... yet.

Re: proc import hangs when reading .xlsx file

Oleg_L — Fri, 06 Apr 2012 08:24:42 GMT

We installed these drivers some months ago. No success. Proc import still hangs.

Oleg.

Re: proc import hangs when reading .xlsx file

Ksharp — Mon, 09 Apr 2012 02:17:11 GMT

Can you use Libname + ODBC to import these xlsx files ?

Re: proc import hangs when reading .xlsx file

Oleg_L — Mon, 09 Apr 2012 04:27:14 GMT

I can use libname pcfiles but it also hangs every once-in-a-while.