topic Re: Reading Excel file in Unix in SAS Programming

Reading Excel file in Unix

anandbillava — Wed, 10 Nov 2010 19:47:12 GMT

Anybody has idea about imporint Excel file in unix to sas dataset.

I tried proc import and libname engine.
Unforunately both are noworking for me
PROC IMPORT OUT = srcdat
DATAFILE= "/home/user/test.xlsx"
DBMS=EXCEL REPLACE;
SHEET="&shtName";
GETNAMES=YES;
SCANTIME=YES;
MIXED=YES;
RUN;
This resulted me saying DBMS type excel is not found.

With the libname
libname mxls PCFILES PATH='/home/user/test.xlsx';

It gave me below error
-------
22
ERROR: Libname MXLS is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR 22-7: Invalid option name LIBNAME.

Let me know if anybody has idea about what I am doing wrong ?

Re: Reading Excel file in Unix

Daryl — Wed, 10 Nov 2010 20:14:34 GMT

You might have a problem with your syntax.

For example, I was able to reproduce your error with the following bad syntax:

[pre]
1237 libname mylib oracle libname;
-------
22
ERROR: Libname MYLIB is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR 22-7: Invalid option name LIBNAME.
[/pre]

Try copying the libref statement into a separate program and submitting that by itself. See if you get the same error. Or, could you post your entire program here for others to try?

Re: Reading Excel file in Unix

Doc_Duke — Wed, 10 Nov 2010 20:15:47 GMT

In short, Excel is not supported on Unix. It is documented in the Unix companion.

Your best bet may be to export the Excel file to a .CSV file and read it in that way. See the google search

csv to sas macro site:sas.com

Doc Muhlbaier
Duke

Re: Reading Excel file in Unix

anandbillava — Wed, 10 Nov 2010 20:28:03 GMT

You mean export the excle file to CSV outside SAS?

Re: Reading Excel file in Unix

data_null__ — Wed, 10 Nov 2010 20:46:55 GMT

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003094743.htm

Re: Reading Excel file in Unix

anandbillava — Wed, 10 Nov 2010 22:13:52 GMT

Thank you. But nothing got worked for me. Do you have any sample programs which does that ?

Re: Reading Excel file in Unix

Ksharp — Thu, 11 Nov 2010 09:07:48 GMT

http://support.sas.com/kb/32/455.html
or Maybe you need 'DBMS=EXCELCS '

Ksharp Message was edited by: Ksharp

Re: Reading Excel file in Unix

barwala — Mon, 15 Nov 2010 19:22:22 GMT

dbms=xls is supported for unix. It should work if you are using sas9.2 . I think for 9.1 the option sheet does not work with dbms=xls.

Re: Reading Excel file in Unix

KimLeBouton — Wed, 31 Aug 2011 02:35:33 GMT

Like anandbillava, I was having the same problem. I am unable to get SAS Access to PC File Formats to read a DBMS of Excel in UNIX.

First off, thanks for the original post and also the responses. It helped me find my solution for SAS 9.13 SP4 on AIX. (SAS 9.2 looks a lot better.)

I uploaded the following binary Excel file via FTP.

x	y
a	2
b	3
c	4
d	5
e	6
f	7

PROC IMPORT OUT= WORK.pctounix

DATAFILE= "a unix file.xls"

DBMS=EXCEL REPLACE; /* couldn't get this dbms to work */

SHEET="Sheet1$"; /* SAS 9.13 doesn't support Sheets */

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Code that worked...

PROC IMPORT OUT= WORK.pctounix

DATAFILE= "a unix file.xls"

DBMS=XLS REPLACE;

GETNAMES=YES;

MIXED=NO;

RUN;

If anyone is doing this with SAMBA (or similar product) and bypassing the FTP, please share your experience.

Other Useful papers and notes...

http://support.sas.com/kb/16/812.html

http://support.sas.com/resources/papers/proceedings09/139-2009.pdf

Kim LeBouton

Reading Excel file in Unix

Peter_C — Wed, 31 Aug 2011 22:01:41 GMT

Kim

on unix SAS has module SAS/Access to PC files

It still does not support the sophistication of the windows excel libname engine

libname libref excel 'excel workbook' ;

which "cans the import routines" to provide access to all objects in the workbook, like "SHEET1$"n and (my preference) ranges named like sas tables

it is very awkward to handle excel in unix.

With enterprise guide connected to SAS on unix, reading from excel became an "interactive" task because guide performed the data transfer from the windows environment to a csv-type file in unix for each sheet or range that I had to read.

maybe I just couldn't read the right manuals

best of luck

Peter

Reading Excel file in Unix

FriedEgg — Wed, 31 Aug 2011 22:13:25 GMT

These issues are not isolated to SAS on unix. It is more a issue of SAS in a 64bit enviornment vs. a 32bit enviornment. DBMS=excel is a 32 bit engine as well as most of the other similar. XLS and EXCELCS are the 64bit engines. They have less functionality and compatibility that the 32bit engines. The module SAS/Access for PC Files that Peter mentions comes into play when either using the EXCELCS engine or the libname pcfiles engine. It requires you to have a pc files server running on a windows machine is utilzing unix, I beleive. Other options for accessing excel files are through the DDE components but I have never used these tools before. I have also not utilized anything that require the pcfiles server. I frequently do however use the XLS engine through proc import/export and have experienced very few issues (mostly under 64bit unix). The XLS engine cannot read the newer versions of excel documents (2007++) where they switched from .xls type extensions to .xlsx types so usually in this situation I convert the document, through excel to a compatible format and then import the new file.

Reading Excel file in Unix

FriedEgg — Wed, 31 Aug 2011 22:36:25 GMT

I remembered one additional way to access excel file data through SAS. If you have the SAS/Access module for odbc connection you could read excel files through the excel odbc driver...