topic Re: read 64bit excel file using 9.2 32bit in SAS Programming

read 64bit excel file using 9.2 32bit

scan — Thu, 16 Feb 2017 09:25:59 GMT

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.

Re: read 64bit excel file using 9.2 32bit

Kurt_Bremser — Thu, 16 Feb 2017 09:55:31 GMT

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

Re: read 64bit excel file using 9.2 32bit

scan — Thu, 16 Feb 2017 10:25:49 GMT

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.

Re: read 64bit excel file using 9.2 32bit

Kurt_Bremser — Thu, 16 Feb 2017 10:48:31 GMT

@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

Re: read 64bit excel file using 9.2 32bit

scan — Thu, 16 Feb 2017 11:21:27 GMT

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?

Re: read 64bit excel file using 9.2 32bit

Kurt_Bremser — Thu, 16 Feb 2017 12:24:06 GMT

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

Re: read 64bit excel file using 9.2 32bit

scan — Thu, 16 Feb 2017 14:09:18 GMT

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?

Re: read 64bit excel file using 9.2 32bit

rogerjdeangelis — Thu, 16 Feb 2017 15:06:59 GMT

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

Re: read 64bit excel file using 9.2 32bit

rogerjdeangelis — Thu, 16 Feb 2017 15:09:40 GMT

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

Re: read 64bit excel file using 9.2 32bit

ballardw — Thu, 16 Feb 2017 15:19:01 GMT

Show the code you used and the log.

Re: read 64bit excel file using 9.2 32bit

scan — Thu, 16 Feb 2017 15:30:03 GMT

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.

Re: read 64bit excel file using 9.2 32bit

Kurt_Bremser — Fri, 17 Feb 2017 08:43:20 GMT

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.