topic Re: SAS 9.3 import excel file in SAS Procedures

SAS 9.3 import excel file

dmerom — Sat, 30 Aug 2014 06:23:16 GMT

I used the same codes s in SAS 9.2 to imort excel file - however it did not work

the log file had ERROR message

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

I don't understant what I need to do so it will work

my operating system is Window XP version 2010

Re: SAS 9.3 import excel file

Ksharp — Sat, 30 Aug 2014 11:18:50 GMT

Try different engine :

dbms=excel

dbms=xls

dbms=xlsx

Re: SAS 9.3 import excel file

dmerom — Sat, 30 Aug 2014 11:27:26 GMT

This is what I used in my code

but even if I try to import the file by locating its place on my folders it will not let me -" no connection"

I wonder why - this is no longer a cyntax problem

Re: SAS 9.3 import excel file

Ksharp — Sat, 30 Aug 2014 12:10:15 GMT

As my opinion, you have no excel engine like XLS EXCEL registered in your SAS system .therefore you can't import excel file . Contact SAS support to confirm what is your problem .

Xia Keshan

Re: SAS 9.3 import excel file

Cynthia_sas — Sat, 30 Aug 2014 14:21:53 GMT

Hi:

This is a known issue. If you had searched on Tech Support (support.sas.com) for notes, using the text of the message (Class not registered) you would have found this note that says what the fix is for the Class not registered message: 52649 - Using the IMPORT and EXPORT procedures with Microsoft Excel and Microsoft Access fails and returns a "Class not registered" error

Usually, when you get this message, it is because you have a "bitness" mismatch between SAS and Office. For example, I have 64 bit SAS on my machine, but I have 32 bit Office. That means code that used to work in SAS 9.2 (when both applications were 32 bit applications) will no longer work with 64 bit SAS and 32 bit Excel. For an excellent discussion of why you need a "helper" file like the PC File Server as the middleman to do your export, please see this blog post by Chris Hemedinger How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy

Until you install the PC File Server, your import/export will not work as it used to.

cynthia

Re: SAS 9.3 import excel file

NL — Tue, 02 Sep 2014 22:06:25 GMT

Hi,

Would SAS 9.3 or 9.4 take care the "bitness" issue with the use of PC File Server?

Nelson

Re: SAS 9.3 import excel file

SASKiwi — Tue, 02 Sep 2014 22:24:53 GMT

Correct. But you can also cure this problem in SAS 9.3/9.4 by aligning the bitness of MS Office with SAS. For example we have 64-bit MS Office components stored on our SAS server to align with 64-bit SAS software and that avoids using the PC File Server.

If you are running SAS on the desktop only you can choose to install 32-bit SAS 9.3/9.4 to be compatible with 32-bit MS Office.

Might be also worth confirming that your SAS 9.3 has SAS/ACCESS for PC Files both installed and licensed. Use PROC SETINIT to confirm your licence.

Re: SAS 9.3 import excel file

NL — Tue, 02 Sep 2014 22:42:24 GMT

This may be a bit off the topic. I have SAS 9.2 (32 bit) installed in a 64 bit OS (laptop) and have MS Office 2010 (32 bit). However, I noticed the ACCESS Connectivity Engine is missing. I tried to download and install it but still unbale to see it in Registry editor. What is the root cause? Would SAS still be able to read Excel and export to Excel?

Re: SAS 9.3 import excel file

Cynthia_sas — Wed, 03 Sep 2014 01:53:54 GMT

Hi:

I do not know whether you need the Microsoft ACE engine if you have SAS/Access to PC File Formats. This would be a question for Tech Support.

cynthia

Re: SAS 9.3 import excel file

PGStats — Wed, 03 Sep 2014 02:26:17 GMT

Read this page from the doc

SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition

for a partial answer to your question. Engine XLSX doesn't require ACE but has limited capabilities. It comes with the second maintenance release of SAS 9.4

(untested)