topic Re: Read an Excel File in Linux in SAS Programming

Read an Excel File in Linux

bream_bn — Tue, 12 Jun 2012 15:13:46 GMT

Has anyone had an luck using SAS to read an excel file on a linux installation of SAS? I have been searching all morning but have not had any luck finding significant information.

Re: Read an Excel File in Linux

Doc_Duke — Tue, 12 Jun 2012 19:25:06 GMT

You should be able to do it with SAS/Access to PC Files. Have you tried that? You can't do it with just base SAS.

A workaround would be to save the Excel file as a .CSV format on the PC side and then ftp that to Linux for processing; ugly, but effective.

Doc Muhlbaier

Duke

Re: Read an Excel File in Linux

bream_bn — Tue, 12 Jun 2012 19:42:32 GMT

The SAS PC Files server must be running on a windows server though correct? The excel files are essentially uploaded to a linux server where which have web applications and SAS running on it.

Re: Read an Excel File in Linux

Astounding — Tue, 12 Jun 2012 19:52:35 GMT

Assuming you use the workaround ... save to a file and upload ... I usually take a 3-program approach.

Get an idea of what's in the data. Here's the key part of this first program:

data _null_;

infile whatever;

input;

if _n_ < 10 then list;

else stop;

run;

The second program would actually attempt to read in the data. It would allow extra length for storing each variable, and would measure how many characters are actually needed. An abbreviation:

data test_length;

infile whatever firstobs=2 dlm='09'x dsd; /* skip the header line, and use tabs as delimiters */

length var1 $ 50 var2 $ 20;

input var1 var2;

len_var1 = lengthn(var1);

len_var2 = lengthn(var2);

run;

proc means data=test_length min max maxdec=0;

var len_:;

run;

The third program would shorten the lengths to what is necessary. It would read in the data and save it as a SAS data set.

Good luck.

Re: Read an Excel File in Linux

FriedEgg — Tue, 12 Jun 2012 20:38:29 GMT

The PC Files server is OPTIONAL. It adds substantially more options for handling excel files in linux, but it is not necessary to have the actual server running on a windows machine. You still have two options assuming you have a valid license for SAS ACCESS for PC Files.

libname x excel '/tmp/excel.xls';

data blah;

set x.sheet_1;

run;

proc import datafile '/tmp/excel.xls'

out=blah

dbms=xls; /* under 64-bit you are limited to only using this dbms without the server (EXCELCS) in 9.2 this does not support xlsx or xlsb, I am not sure about 9.3 */

run;

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

Re: Read an Excel File in Linux

bream_bn — Wed, 13 Jun 2012 16:02:19 GMT

The proc import statement works as stated, the excel engine does not work though even though I have installed SAS Access to the PC Files. Also in order to use the proc import statement with dbms=xls does SAS Access for PC Files need to be installed? I could test this but it would require me to uninstall PC Files

Re: Read an Excel File in Linux

FriedEgg — Wed, 13 Jun 2012 16:11:12 GMT

As I said, SAS Access for PC Files is required, the PC Files Server (part of the SAS Access for PC Files) is the optional piece. Without SAS Access for PC Files installed and licensed you options are to convert the file to an alternative dbms, such as CSV or TAB using an external utility and then reading that file into SAS. If you do not know if you have SAS Access for PC Files run:

PROC SETINIT; RUN:

Then review the data it presents in your log.

Re: Read an Excel File in Linux

bream_bn — Wed, 13 Jun 2012 19:20:08 GMT

Thanks for your help regarding this, we will be using the proc import statement and xls files going forward.