Hi SAS Forum,
I have been trying (from yesterday) to import "3 sheets excel workbook" into 3 different SAS data sets as the first step of automating an analysis process in my company but failed.
Based on Internet search, I found 2 approaches.
Approach I: Importing multi-sheet excel workbooks into SAS having saved the excel workbook as XML
In this approach, As the first step, following statement has to be run.
%include 'LoadXL.sas';
I get this error messege......
ERROR: Cannot open %INCLUDE file LoadXL.sas.
Approach II: using Excel Libname Engine
libname myxls excel \\cbmcc-a8-sas01\RRM\user\DELETE.xls;
I get this error messege......
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.
Q: Could any one help me ?
Miris
Mirisage,
Did you read this http://support.sas.com/kb/33/228.html in relation to 9.2 or
if you are in a more recent context read this
http://support.sas.com/kb/43/802.html
about the pcfile server you have to install or activate
if you want applying code like the mentionned inside this last document!
Excel engine in the libname statement is NOT working.
The other aspect is in relation to X64_ESRV08:
Are you speaking of sas working on a server? and downloading to a local pc?
The note applies to both cases:
a local pc with w7 64bits with sas 64 bits and office 32 bits
or (that was my other case)
a sas (32 bits linux) turning on a distant server on which i submit code to produce an excel file
on my local pc through the pcfilesserver installed on my local pc.
HTH
Andre
For second option :
check your path where excel exists.
libname myexcel excel 'C:\Users\Path\myexcel.xls';
data test1;
set myexcel.'sheet1$'n;
data test2;
set myexcel.'sheet2$'n;
data test3;
set myexcel.'sheet3$'n;
run;
libname myexcel clear;
Hi Pradeep,
Yes, I checked the path.
It is the correct location where my Excel file called "Delete.xls" is residing.
But still below error messege pops up.
I get this error messege......
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.
Anyway, thank you very much for your reply.
Miris
I get this error messege......
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.
Implies you do mot have a suitable SAS/ACCESS product licensed.
run the code below to discover abd let us know the components licensed
PROC SETINIT NOALIAS ; RUN ;
Hi Peter,
Thanks.
I ran your code.
PROC SETINIT NOALIAS ; RUN ;
Among many other things, below things were found in the log.
---SAS/ACCESS Interface to ORACLE 29APR2014
---SAS/ACCESS Interface to PC Files 29APR2014
---SAS/ACCESS Interface to ODBC 29APR2014
Would this give you the clue that we are looking for?
Please let me know.
Thanks
Miris
then I can see no reason why you would find that message
ERROR: The EXCEL engine cannot be found.
Perhaps
SAS/ACCESS Interface to PC Files
cannot be accessed from your SAS session.
Is your SAS a 64bit application and your excel a 32bit app?
Further on the last question of Peter Crawford:
Check the answers of Paul Holmes at the bottom of this page
http://www.runsubmit.com/questions/520/excel-engine-not-found-on-sas92-64-bit
Excelcs + the sas note for examples of code
see this too this note
http://support.sas.com/kb/47/237.html
Andre
Hi Peter and Andre,
I ran the following statements in SAS editor.
%put _automatic_ ;
run;
Then I got these in the log.
…………………………………………
………………………………………..
AUTOMATIC SYSSCP WIN
AUTOMATIC SYSSCPL X64_ESRV08
…………………………………………
………………………………………..
· My Excel is 32bit (I checked it)
Q: If the situation is like above (64 bit SAS and 32 bit Excel), wouldn't there be any mthod to fix the problem of getting errors when I employ Excel Libname Engine below....
Approach II: using Excel Libname Engine
libname myxls excel \\abcd-k6-sas01\xxx\user\DELETE.xls;
I get this error messege......
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.
Thanks
Miris
Mirisage,
Did you read this http://support.sas.com/kb/33/228.html in relation to 9.2 or
if you are in a more recent context read this
http://support.sas.com/kb/43/802.html
about the pcfile server you have to install or activate
if you want applying code like the mentionned inside this last document!
Excel engine in the libname statement is NOT working.
The other aspect is in relation to X64_ESRV08:
Are you speaking of sas working on a server? and downloading to a local pc?
The note applies to both cases:
a local pc with w7 64bits with sas 64 bits and office 32 bits
or (that was my other case)
a sas (32 bits linux) turning on a distant server on which i submit code to produce an excel file
on my local pc through the pcfilesserver installed on my local pc.
HTH
Andre
Hi Andre,
Thank you very much for all these helps.
(I can see my system knowledge needs a lot of work, it is very poor).
Regards
Mirisage
Miris,
Have you tried using proc import instead? See second link that Andre replied.
Regards,
Chad
Hi Chad,
Many thanks.
Yes, it worked for Excel 2003.
/*When you import several sheets from a Excel 2003 workbook, I found below macro code works….*/
%macro name1 (one,two);
PROC IMPORT OUT=work.&one
DATAFILE="\\xxxx-xx-sas01\abc\myfiles\testing.xls"
DBMS=xls REPLACE;
GETNAMES=YES;
SHEET="&two"; /*SHEET= option is acceptable */
RUN;
%mend name1;
%name1 (file1,Sheet1);
%name1 (file2,Sheet2);
%name1 (file3,Sheet3);
But for Excel 2010, the macro I have shown below did not work.
I do not know why.
But when I imported one sheet at a time (without macronizing) then it worked. So, no problem I can import Excel 2010 sheets one at a time.
/*IMPORT EXCEL 2010*/
/*BELOW MACRO CODE DID NOT WORK????*/
/*When you import several sheets from a Excel 2010 workbook, I found below macro code DID NOT WORK….*/
/*But worse comes to worse you can import 3 sheets one by one, one at a time without macronizing*/
%macro name2 (three,four);
PROC IMPORT OUT=WORK.&three
DATAFILE="\\xxxx-v7-sas01\xyz\mydata\testing.xlsx"
DBMS=EXCELCS REPLACE;
/* GETNAMES=YES;*/ /*You cannot use GETNAMES=YES; in this code*/
SHEET="&four"; /*SHEET= option is acceptable */
RUN;
%mend name2;
%name2 (file4,Sheet1);
%name2 (file5,Sheet2);
%name2 (file6,Sheet3);
Thanks everybody for all these helps!
Mirisage
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.