DATA Step, Macro, Functions and more

Read an Excel File in Linux

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Read an Excel File in Linux

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.


Accepted Solutions
Solution
‎06-12-2012 04:38 PM
Trusted Advisor
Posts: 1,301

Re: Read an Excel File in Linux

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

View solution in original post


All Replies
Trusted Advisor
Posts: 2,116

Re: Read an Excel File in Linux

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

Contributor
Posts: 28

Re: Read an Excel File in Linux

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.

Super User
Posts: 5,511

Re: Read an Excel File in Linux

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.

Solution
‎06-12-2012 04:38 PM
Trusted Advisor
Posts: 1,301

Re: Read an Excel File in Linux

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

Contributor
Posts: 28

Re: Read an Excel File in Linux

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

Trusted Advisor
Posts: 1,301

Re: Read an Excel File in Linux

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.

Contributor
Posts: 28

Re: Read an Excel File in Linux

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 2305 views
  • 3 likes
  • 4 in conversation