Help using Base SAS procedures

proc import hangs when reading .xlsx file

Reply
Contributor
Posts: 44

proc import hangs when reading .xlsx file

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 Star
Posts: 7,492

proc import hangs when reading .xlsx file

Posted in reply to WesBarris

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

Contributor
Posts: 44

proc import hangs when reading .xlsx file

Posted in reply to WesBarris

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?

Super User
Super User
Posts: 7,076

proc import hangs when reading .xlsx file

Posted in reply to WesBarris

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 Star
Posts: 7,492

proc import hangs when reading .xlsx file

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

Regular Contributor
Posts: 151

proc import hangs when reading .xlsx file

Posted in reply to WesBarris

I have the same problem even with UNC paths.

Oleg.

Regular Contributor
Posts: 151

proc import hangs when reading .xlsx file

Posted in reply to WesBarris

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.

Super Contributor
Posts: 349

proc import hangs when reading .xlsx file

Posted in reply to WesBarris

Hi,

Try replacing dbms=excel.Hope this helps.

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

run;

Thanks,

Shiva

Contributor
Posts: 44

Re: proc import hangs when reading .xlsx file

Posted in reply to WesBarris

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.

Regular Contributor
Posts: 151

Re: proc import hangs when reading .xlsx file

Posted in reply to WesBarris

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

Oleg.

Super User
Posts: 10,044

Re: proc import hangs when reading .xlsx file

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

Regular Contributor
Posts: 151

Re: proc import hangs when reading .xlsx file

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

Ask a Question
Discussion stats
  • 11 replies
  • 4229 views
  • 0 likes
  • 6 in conversation