topic Re: Proc Import in SAS Procedures

Proc Import

kewong — Tue, 20 Nov 2012 23:57:07 GMT

Hi everyone,

I'm a new SAS user and running into a problem when trying to import an excel file. I'm using SAS 9.1 and import excel 1997-03 file. Below is my code and the error message I am receiving:

Proc import out = work.abc

datafile="P:\pathway\test.xls"

dbms=excel replace;

sheet="Sheet1";

run;

ERROR: Connect: Could not find installable ISAM.

ERROR: Error in the LIBNAME statement.

Connection Failed. See log for details.

Has anyone come across this before? Any potential solutions? I know I can save the file in a CSV format but I would rather import one XLS with 10 tabs, then 10 separate CSV files.

Appreciate the help and thanks in advance!

Re: Proc Import

art297 — Wed, 21 Nov 2012 00:11:29 GMT

One quick thing you could try: assuming you have a more recent version of excel than the one used to create the file, open the file in Excel, resave it, and try to import it again.

Another approach you could try is to import the file using the XLS engine.

Re: Proc Import

kewong — Wed, 21 Nov 2012 18:39:32 GMT

Thanks Arthur - appreciate the help!

I tried re-saving the file and importing using the XLS engine - both options still gave the error mentioned above.

Re: Proc Import

art297 — Wed, 21 Nov 2012 18:43:01 GMT

What version of Excel do you have and can you post the file here?

Re: Proc Import

kewong — Wed, 21 Nov 2012 18:52:02 GMT

I have Microsoft Excel 2007 - I can't provide you with the actual excel file due to confidentiality. That being said, this error shows up with ANY excel file. Even as simple as:

Name Score

a 2

b 5

b 6

Re: Proc Import

art297 — Wed, 21 Nov 2012 19:36:30 GMT

After opening the file in Excel did you try to save it as a newer version of Excel and/or when you saved it did the extension change from xls to xlsx? According to both SAS and MS, resaving the file should correct the problem.

Can you import the attached file?

Re: Proc Import

kewong — Wed, 21 Nov 2012 19:44:33 GMT

I save the file I was using to XLS extension (Microsoft 1997-2003 version) - it did not change from xls to xlsx.

I ran the codes using the file you attached:

Proc import out = work.abc

datafile="C:\Documents and Settings\kwong3\Desktop\teststuff.xls"

dbms=excel replace;

sheet="Sheet1";

run;

AND

libname xls excel 'C:\Documents and Settings\kwong3\Desktop\teststuff.xls';

Error still pops up. Thank you for the continued advice, Arthur. As frustrating as this is, I guess I can work around with saving my XLS to CSV files for now...

Re: Proc Import

art297 — Wed, 21 Nov 2012 19:55:23 GMT

I would submit this one to SAS tech support. If they can find the solution, please post it back here.

Re: Proc Import

kewong — Tue, 27 Nov 2012 17:56:05 GMT

Below is the response I got from SAS Tech Support:

This may be caused by a registry key not being present or not registered. Here is how you deal with this normally.

Usage Note 11012: Error: Trying to establish connection and ISAM errors when importing and exporting PC File Formats

When you attempt to import or export PC File Formats such as Microsoft Excel or Microsoft Access files, the following error messages might appear:

Error trying to establish connection.

Could not find installable ISAM.

The error message can appear even when the latest Microsoft Data Access Components (MDAC) have been installed. They appear because the registry entries listed in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines are not pointing to the correct locations. The most common problem is with the Msexcl40.dll where it is pointing to C:\Windows rather than C:\winnt or vice versa.

Other dlls being pointed to incorrectly include msexch40.dll, msrd2x40.dll, msrd3x40.dll, msltus40.dll, mspbde40.dll, mstext40.dll, and msxbde40.dll.

For more information, consult Microsoft Knowledge Base articles 283881 and 209805 at http://www.microsoft.com.

This can also be caused by the MSEXCL40.DLL not being registered. You can check it as follows:

1.Make sure that Msexcl40.dll exists in the system or system32 folder.

2.Open regedit. Make sure that the correct path to Msexcl40.dll is located at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

3.If both conditions exist, re-register the DLL using regsrv32.exe:

“regsrv32 c:\winnt\system32\Msexcl40.dll”

(From Start>Run or command prompt. You may need to find the location of regsrv32 before running.)

If 1 or 2 are false, you will need to install (or re-install) Office (or at the very least Excel).

Re: Proc Import

art297 — Tue, 27 Nov 2012 17:59:43 GMT

Kewong: and did that correct the problem?