topic Re: How to import 3 sheets excel workbook into 3 SAS data sets? in SAS Procedures

How to import 3 sheets excel workbook into 3 SAS data sets?

Mirisage — Wed, 05 Feb 2014 17:10:22 GMT

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

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

pradeepalankar — Wed, 05 Feb 2014 18:05:35 GMT

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;

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Mirisage — Thu, 06 Feb 2014 14:08:22 GMT

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

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Peter_C — Thu, 06 Feb 2014 15:13:18 GMT

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 ;

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Mirisage — Fri, 07 Feb 2014 13:42:32 GMT

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

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Peter_C — Fri, 07 Feb 2014 16:36:09 GMT

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?

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Andre — Fri, 07 Feb 2014 16:49:14 GMT

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

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Mirisage — Mon, 10 Feb 2014 14:37:52 GMT

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

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Andre — Thu, 13 Feb 2014 09:41:16 GMT

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

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Mirisage — Thu, 13 Feb 2014 19:54:12 GMT

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

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

cwcaulkins — Thu, 13 Feb 2014 20:13:07 GMT

Miris,

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

Regards,

Chad

Re: How to import 3 sheets excel workbook into 3 SAS data sets?

Mirisage — Fri, 14 Feb 2014 18:53:47 GMT

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