read 64bit excel file using 9.2 32bit

Reply
Contributor
Posts: 25

read 64bit excel file using 9.2 32bit

Hello community,

 

I'm trying to read an excel file (xls or xlsx) generated with Excel 64 bit using SAS 9.2 32bit, with poor results.


Basically I googled some information about this specific task, but I end up finding information only related to the inverse problem that could be easily solved with the package SAS PC FILES SERVER (reading 32bit documents with SAS 64 bit), using as engine EXCELCS or other.
I actually own the SAS PC FILES SERVER and it seems it is correctly installed and working on my SAS machine, running the proc setinit.

But this seems unable to solve the problem described in this topic.

Any solution?


Many thanks.

Super User
Posts: 7,758

Re: read 64bit excel file using 9.2 32bit

Which steps have you tried, and what were the log responses to your code?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: read 64bit excel file using 9.2 32bit

Posted in reply to KurtBremser

thank you Kurt for your interest,

 

I tried many engines, as well as the internal guided procedure for import but the log response is:

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Super User
Posts: 7,758

Re: read 64bit excel file using 9.2 32bit


scan wrote:

thank you Kurt for your interest,

 

I tried many engines, as well as the internal guided procedure for import but the log response is:

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.


That usually comes from a missing (not installed) or non-licensed module.

The most stable way to import data from Excel is to save from Excel to a text-based format and read that with a data step.

Advantages:

Saved data can be inspected with a simple text editor

No licenses beyond Base SAS needed

Will work with future Excel versions, no matter what the idiots in Redmond cobble together

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: read 64bit excel file using 9.2 32bit

Posted in reply to KurtBremser

Thank you Kurt,

 

I'm in a  situation in which I can't convert these files to another format.

Do you know the name of the product you're saying is not licensed?

 

 

Super User
Posts: 7,758

Re: read 64bit excel file using 9.2 32bit


scan wrote:

Thank you Kurt,

 

I'm in a  situation in which I can't convert these files to another format.

Do you know the name of the product you're saying is not licensed?

 

 


Open with Excel, File - Save As, select csv as file type.

If you don't have Excel, the free Libreoffice will do the trick.

As for the product, it is SAS/ACCESS to PC Files

Since you're still on 9.2, the xlsx format may not be supported yet.

Which operating system is SAS running on?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 25

Re: read 64bit excel file using 9.2 32bit

Posted in reply to KurtBremser

Thanks,

 

OS is W7, SAS/ACCESS to PC Files is present according to the setinit.

Neither the xsl without the x can be imported, if it is generated with a 64bit excel framework.

Any solution?

Valued Guide
Posts: 505

Re: read 64bit excel file using 9.2 32bit

If you have the IML interface to R you should be able to paste theis code into IML.

 

* create a 32 bit excel workbook with sheet have;
%utlfkil(d:/xls/have.xlsx); * delete if exists;
libname xel "d:/xls/have.xlsx";
data xel.have;
  set sashelp.class;
run;quit;
libname xel clear;


* import 32bit sheet have;
%utl_submit_r64('
library(rio); library(foreign); have <-import("d:/xls/have.xlsx"); write.dta(have, "d:/dta/want.dta"); '); * import to SAS; proc import out=want file='d:\dta\want.dta' replace; ;run;quit; proc print data=want; run;quit; Obs NAME SEX AGE HEIGHT WEIGHT 1 Alfred M 14 69.0 112.5 2 Alice F 13 56.5 84.0 3 Barbara F 13 65.3 98.0 4 Carol F 14 62.8 102.5 5 Henry M 14 63.5 102.5 6 James M 12 57.3 83.0 7 Jane F 12 59.8 84.5 8 Janet F 15 62.5 112.5 9 Jeffrey M 13 62.5 84.0 10 John M 12 59.0 99.5 11 Joyce F 11 51.3 50.5 12 Judy F 14 64.3 90.0 13 Louise F 12 56.3 77.0 14 Mary F 15 66.5 112.0 15 Philip M 16 72.0 150.0 16 Robert M 12 64.8 128.0 17 Ronald M 15 67.0 133.0 18 Thomas M 11 57.5 85.0 19 William M 15 66.5 112.0
Valued Guide
Posts: 505

Re: read 64bit excel file using 9.2 32bit

Posted in reply to rogerjdeangelis

It should also work with 32bit SAS and 64 or 32 bit excel.

 

SOAPBOX ON

I wish SAS would stop promoting 32bit SAS, I think SAS stopped promoting 16bit WIn31 SAS?

SOAPBOX OFF

Super User
Posts: 11,338

Re: read 64bit excel file using 9.2 32bit

Show the code you used and the log.

Contributor
Posts: 25

Re: read 64bit excel file using 9.2 32bit

no IML interface to R here.

 

I'll post the code in a while, but I've just generated a simple xls document with excel 64 and tried the guided procedure to import.

Then I tried a lot of different engines found on the web such as excelcs excel xls and so on. The log was always the same as reported some post ago.
Will return here in a while, anyway.

Super User
Posts: 7,758

Re: read 64bit excel file using 9.2 32bit

Just think of the time you already wasted trying to get the Excel file format to work. Saving to csv and importing that would have solved your problem in a moment.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 11 replies
  • 214 views
  • 1 like
  • 4 in conversation