read 64bit excel file using 9.2 32bit

[scan]

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.

[Kurt_Bremser]

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

[scan]

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.

[Kurt_Bremser]

---

@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

[scan]

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?

[Kurt_Bremser]

---

@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?

[scan]

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?

[rogerjdeangelis]

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

[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

[ballardw]

Show the code you used and the log.

[scan]

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.

[Kurt_Bremser]

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.