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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Andre
Obsidian | Level 7

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

View solution in original post

11 REPLIES 11
pradeepalankar
Obsidian | Level 7

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;

Mirisage
Obsidian | Level 7

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

Peter_C
Rhodochrosite | Level 12

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 ;

Mirisage
Obsidian | Level 7

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

Peter_C
Rhodochrosite | Level 12

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?

Andre
Obsidian | Level 7

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

Mirisage
Obsidian | Level 7

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

…………………………………………

………………………………………..

  •         Would this mean my SAS is a 64bit application.

·      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

Andre
Obsidian | Level 7

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

Mirisage
Obsidian | Level 7

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

cwcaulkins
Fluorite | Level 6

Miris,

Have you tried using proc import instead? See second link that Andre replied.

Regards,

Chad

Mirisage
Obsidian | Level 7

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

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!

What is Bayesian Analysis?

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.

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
  • 11 replies
  • 2739 views
  • 8 likes
  • 5 in conversation