BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bream_bn
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

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

7 REPLIES 7
Doc_Duke
Rhodochrosite | Level 12

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

bream_bn
Fluorite | Level 6

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.

Astounding
PROC Star

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.

FriedEgg
SAS Employee

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

bream_bn
Fluorite | Level 6

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

FriedEgg
SAS Employee

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.

bream_bn
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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