BookmarkSubscribeRSS Feed
Nyioves
Calcite | Level 5

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;

5 REPLIES 5
ballardw
Super User

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";

Cynthia_sas
SAS Super FREQ

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

jakarman
Barite | Level 11

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 --<-----
Nyioves
Calcite | Level 5

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

jakarman
Barite | Level 11

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 --<-----

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
  • 5 replies
  • 1647 views
  • 1 like
  • 4 in conversation