BookmarkSubscribeRSS Feed
WesBarris
Obsidian | Level 7

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.

11 REPLIES 11
art297
Opal | Level 21

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

WesBarris
Obsidian | Level 7

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?

Tom
Super User Tom
Super User

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?

art297
Opal | Level 21

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

Oleg_L
Obsidian | Level 7

I have the same problem even with UNC paths.

Oleg.

Oleg_L
Obsidian | Level 7

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.

shivas
Pyrite | Level 9

Hi,

Try replacing dbms=excel.Hope this helps.

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

run;

Thanks,

Shiva

WesBarris
Obsidian | Level 7

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.

Oleg_L
Obsidian | Level 7

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

Oleg.

Ksharp
Super User

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

Oleg_L
Obsidian | Level 7

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 6794 views
  • 0 likes
  • 6 in conversation