Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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


Accepted Solutions
Solution
‎02-13-2014 04:41 AM
Super Contributor
Posts: 273

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

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


All Replies
Frequent Contributor
Posts: 106

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

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;

Super Contributor
Posts: 338

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

Posted in reply to pradeepalankar

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

Valued Guide
Posts: 2,177

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

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 ;

Super Contributor
Posts: 338

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

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

Valued Guide
Posts: 2,177

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

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?

Super Contributor
Posts: 273

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

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

Super Contributor
Posts: 338

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

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

Solution
‎02-13-2014 04:41 AM
Super Contributor
Posts: 273

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

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

Super Contributor
Posts: 338

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

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

Occasional Contributor
Posts: 9

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

Miris,

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

Regards,

Chad

Super Contributor
Posts: 338

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

Posted in reply to cwcaulkins

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 1167 views
  • 8 likes
  • 5 in conversation