BookmarkSubscribeRSS Feed
KendallM
Calcite | Level 5

Hello, 

I've gone through several pages of google searches and the only topic that comes up about this is that it won't work if SAS and Excel are different bit versions. Both of mine are x64, and I had no issues until "upgrading" to Office 2013 (x64). I'd prefer to not go the PCFiles route. Has anyone found a reason why 9.4 and Office 2013 will not play nice?

 

My code:

%let path=...\CR\;

/* Setting Thesis library to store data sets */
libname CR "...\CR\SAS";
libname PageL excel "&path\Arizona\Keep_tabs_Page_level_Arizona_January.xls";
libname PostL excel "&path\Arizona\Keep_tabs_Post_level_Arizona_January.xls";

 

Log output:

121 %let path=C:\Users\Symtrips\Google Drive\Jobs\CR\;
122
123 /* Setting library to store data sets */
124 libname CR "...\CR\SAS";
NOTE: Libref CR was successfully assigned as follows:
Engine: V9
Physical Name: ...\CR\SAS
125 libname PageL excel "&path\Arizona\Keep_tabs_Page_level_Arizona_January.xls";
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
126 libname PostL excel "&path\Arizona\Keep_tabs_Post_level_Arizona_January.xls";
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

 

Thank you in advance! Again both programs are running the same bit version (x64)

15 REPLIES 15
Ksharp
Super User

Did you try XLSX , XLS engine ?

 

libname PostL XLSX  "&path\Arizona\Keep_tabs_Post_level_Arizona_January.xls";

libname PostL XLS    "&path\Arizona\Keep_tabs_Post_level_Arizona_January.xls";

KendallM
Calcite | Level 5

I was able to work around by saving the file as an .xls and not specifying any engine.

 

"libname PageL "&path\... etc.xls" and it read in with no errors.

 

Still not sure what the issue is with Office 2013 and SAS 9.4.

 

As a note I was able to also use the "excel" engine after resaving the files as .xls and recieved no errors.

SASKiwi
PROC Star

What format were the files in originally if saving as XLS fixes the problem?

KendallM
Calcite | Level 5

The files had originally been saved as *.xlsx workbooks, but re-saving them as excel 97-2003 *.xls workbooks allowed SAS 9.4 to import them without error.

SASKiwi
PROC Star

We use SAS 9.4 and Office 2010 without problems, but I guess you did as well until upgrading to 2013. I'm assuming here you are using SAS running locally on a PC not on a server?

KendallM
Calcite | Level 5

Correct, no issues until upgrading from Office 2010 to 2013. SAS is running locally. Funnily enough my thesis spreadsheets are .xlsx but I haven't touched them since before the Office upgrade, those code files still function perfectly. Must be a header change in the 2013 file formatting causing SAS not to recognize it correctly. I also noticed that when using PCFiles, it will "import" the data from the newer .xlsx files but it doesn't actually read in correctly. Comes out with some garbage and corruption.

lindseyb
Calcite | Level 5

Hi,

 

Were you ever able to get your code to work using the excel or xlsx engine?

 

Thanks!

 

RXM
Fluorite | Level 6 RXM
Fluorite | Level 6

I have the same problem.  A temporary solution is to export the file as a .csv instead of as Excel.  You can always open the .csv file in Excel and save it as whatever Excel version you prefer.

Vince_SAS
Rhodochrosite | Level 12

The "Class not registered" error indicates a bitness mismatch between SAS and the Excel data access libraries on the machine.  Using the XLS engine to work with .xls files or the XLSX engine to work with .xlsx files should get around this issue.

 

Vince DelGobbo

SAS R&D

RXM
Fluorite | Level 6 RXM
Fluorite | Level 6

Vince, thanks.  I tried exporting the file as .xls and still got the error, so I went the .csv route.

Vince_SAS
Rhodochrosite | Level 12

Did you explicitly specify DBMS=xls? You should not have gotten the error in that case.

 

Vince DelGobbo

SAS R&D

RXM
Fluorite | Level 6 RXM
Fluorite | Level 6

Vince,

 

Thank you!  That did it.  I still had dbms=EXCEL with the file extension .xls.

 

Rob

PaigeMiller
Diamond | Level 26
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed.  See log for details.

I have code that we run every month to read .xlsx and .xlsm files with PROC IMPORT and DBMS=EXCEL, and I have never had this particular problem until today. I am not aware of any system changes here, but there's no way I can be sure. Why does this happen, and can I go back to using PROC IMPORT with DBMS=EXCEL so I don't have to change my code? I can read the files correctly with DBMS=XLSX, but the column names change from F1 F2 F3 etc. to A B C etc., meaning I have to change my code.

--
Paige Miller
Vince_SAS
Rhodochrosite | Level 12

It seems likely that something changed on your system.

 

You should contact your IT department to ensure that the bitness of Microsoft Excel installed on the machine running SAS matches the bitness of SAS on that machine.  The problem should go away once that mismatch is fixed.

 

Another possibility is to install the 32-bit Excel/Office libraries along with the 64-bit libraries.  I doubt that this is a "Microsoft supported/suggested" environment but it might work for you.  This is also something that you will have to work with your IT department to implement.

 

You can contact our Technical Support Department if you have questions about these two strategies, or if you have other questions:

 

https://support.sas.com/en/technical-support/submit-a-support-request.html

 

Going forward, I would like to suggest using the XLSX engine for all new code, and that you prepare to transition to using XLSX in the older code.  If you run SAS on a different machine/server in the future, your working EXCEL engine code might also fail.  For example, the EXCEL engine works only on Windows systems, so moving to a UNIX server will also cause your code to fail, but for a different reason.

 

Vince DelGobbo

SAS R&D

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 7511 views
  • 6 likes
  • 7 in conversation