Help using Base SAS procedures

Excel and Lib statement on demand -- Excel Engine (?)

Reply
Contributor
Posts: 35

Excel and Lib statement on demand -- Excel Engine (?)

Can you not use the libname statement to open an excel file in on demand? A search reveals others try this with an Excel engine.

That is not my "training" however for Excel.

I would think this should work if not for on demand.....

libname flipboy "/home/nyioves/Nikfold/sales.xls";

data flip;

set flipboy.'Australia$'n;

run;

It doesn't even assign the lib....

ERROR: Library FLIPBOY is not in a valid format for access method RANDOM.

ERROR: Error in the LIBNAME statement.

59         data flip;

60         set flipboy.'Australia$'n;

ERROR: Libref FLIPBOY is not assigned.

61         run;

Super User
Posts: 11,343

Re: Excel and Lib statement on demand -- Excel Engine (?)

ERROR: Library FLIPBOY is not in a valid format for access method RANDOM.

You get this message because you did not specify the EXCEL engine option.

libname flipboy excel "/home/nyioves/Nikfold/sales.xls";

SAS Super FREQ
Posts: 8,866

Re: Excel and Lib statement on demand -- Excel Engine (?)

Hi:

However, with SAS University Edition and SAS OnDemand, you cannot use the LIBNAME engine for Excel. The "helper" files needed to make the LIBNAME engine work are not available. However, you CAN use PROC IMPORT with either DBMS=XLS or DBMS=XLSX. There have been some previous postings about this. Mostly about the University Edition, but the same holds true for SAS OnDemand.

cynthia

Trusted Advisor
Posts: 3,214

Re: Excel and Lib statement on demand -- Excel Engine (?)

SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition   Libname XLSX is not needing any helper files but is new in sas 94m2   SAS UE is 94m1   too old version be patient for an update.

---->-- ja karman --<-----
Contributor
Posts: 35

Re: Excel and Lib statement on demand -- Excel Engine (?)

So do you always need to specify the Excel Engine when trying to read it in w the libname statement? I'm confused.

Trusted Advisor
Posts: 3,214

Re: Excel and Lib statement on demand -- Excel Engine (?)

With the Libname access method you can access the Excel in the same way as en external DBMS as long as it used and stored like a DBMS (data-table format) way. You can also use that with XML files as they are describing data in a table interface. This newest one XLSX is only available at the newest SAS release. the XLSX is a zipfile archive having XML-files inside.

The older interface (excel) is based on using the Microsoft ACE driver. That one is only available at Windows machine not Unix.

Yup the libname syntax is:  Libname libref <engine>  'SAS-library'  < options > <engine/host-options>;    SAS(R) 9.4 Statements: Reference, Third Edition

That <engine> is optional the default is native eg V9. SAS(R) 9.4 Language Reference: Concepts, Fourth Edition     You can use for special ones SAS(R) 9.4 Language Reference: Concepts, Fourth Edition or  alternate native ones SAS(R) 9.4 Language Reference: Concepts, Fourth Edition. SAS can read old release sas7bdat file types. Sometimes this is causing an in the fly conversion associated with locks as not being native. When those locks are caused by that specifying the real engine type can help.    

Yes, when you want to work on an excel with the libname method you have to specify then engine.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 5 replies
  • 714 views
  • 1 like
  • 4 in conversation