Learning SAS? Welcome to the exclusive online community for all SAS learners.

SAS/ACCESS LIBNAME not working

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

SAS/ACCESS LIBNAME not working

I was hoping a couple of threads here may have helped us, as I think our issue is similar, to what others have experienced. We have been trialing the University Edition SAS Studio 3.1 with some of the Base SAS course exercises and we have hit an issue with SAS/ACCESS LIBNAME

If we assign a LIBNAME to a an Excel workbook with two worksheets

libname custfm excel '/folders/myfolders/SASCourse062014/Data/custfm.xls';

generates the following error messages

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

  ERROR: Error in the LIBNAME statement.

Please note, the file is in the right shared folder and we have the correct pathname.

The same statement works perfectly (allowing for changes in the pathname) in Base SAS and recognises both worksheets in the workbook properly.

Although we would like the Libname in Studio to work just as it does in Base 9.4, to confirm the file path etc are correct I tested importing from the workbook using the following...

proc import datafile='/folders/myfolders/SASCourse062014/Data/custfm.xls'

  out=work.custfm

  dbms=xls

  replace;

run;

The workbook custfm.xls is recognised and the first worksheet of 30 observations is imported

NOTE: Variable Name Change. Customer ID -> Customer_ID 

NOTE: Variable Name Change. First Name -> First_Name 

NOTE: Variable Name Change. Last Name -> Last_Name 

NOTE: Variable Name Change. Birth Date -> Birth_Date 

NOTE: Variable Name Change. Customer Type -> Customer_Type 

NOTE: The import data set has 30 observations and 6 variables.

NOTE: WORK.CUSTFM data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

  real time 0.17 seconds

  cpu time 0.06 seconds

The second worksheet of 47 entries is ignored in its entirety.

This driving us mad, please can someone advise?as/exce.1; ;


Accepted Solutions
Solution
‎06-11-2014 06:45 PM
SAS Super FREQ
Posts: 683

Re: SAS/ACCESS LIBNAME not working

This is due to the fact that the EXCEL libname engine is only supported on the Windows operating system. The SAS University Edition runs on Linux. So you have to use the Proc IMPORT way of accessing the data. Another way would be with using the SAS® PC Files Server.

Find more information here: 50530 - Web download of the SAS® 9.4 version of the SAS® PC Files Server

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: SAS/ACCESS LIBNAME not working

I think you are using file name while defining library. Define libname without file name like this then access file custfm. Hope this helps.

libname custfm '/folders/myfolders/SASCourse062014/Data';

proc print data=custfm.custfm;

run;

Super User
Posts: 17,823

Re: SAS/ACCESS LIBNAME not working

That is the correct method for accessing a file via the libname reference.

The sheets in the file then become the "data sets" in the library viewer.

Trusted Advisor
Posts: 1,204

Re: SAS/ACCESS LIBNAME not working

Thanks Reeza for the correction.

Occasional Contributor
Posts: 5

Re: SAS/ACCESS LIBNAME not working

Hello, I have similar problem and I tried your solution but I get the error "ERROR: File PACKAGE.EMPLOYEE.DATA does not exist."

Super User
Posts: 17,823

Re: SAS/ACCESS LIBNAME not working

You can specify a sheet name in your proc import otherwise I think SAS just imports the first sheet.

proc import datafile='/folders/myfolders/SASCourse062014/Data/custfm.xls'

  out=work.custfm

  dbms=xls

  replace;

sheet='first sheet';

run;

proc import datafile='/folders/myfolders/SASCourse062014/Data/custfm.xls'

  out=work.custfm2

  dbms=xls

  replace;

sheet='second sheet';

run;

Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME not working

Thank you Reeza. This is good to know.

Solution
‎06-11-2014 06:45 PM
SAS Super FREQ
Posts: 683

Re: SAS/ACCESS LIBNAME not working

This is due to the fact that the EXCEL libname engine is only supported on the Windows operating system. The SAS University Edition runs on Linux. So you have to use the Proc IMPORT way of accessing the data. Another way would be with using the SAS® PC Files Server.

Find more information here: 50530 - Web download of the SAS® 9.4 version of the SAS® PC Files Server

Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME not working

Thank you Bruno.

This makes sense.

Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME not working

I found this link http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002644677.htm which seems to indicate how we can achieve the same end on Linux.

On this page it suggests that Linux equivalent LIBNAME statement should be:

LIBNAME myxls PCFILES SERVER=D2323 PATH='c:\demo.xls';

Applying this to the custfm.xls workbook I have in my SAS Studio shared folder I tried

libname custfm PCFILES SERVER=localhost PATH='/folders/myfolders/SASCourse062014/Data/custfm.xls';

and log showed the following error message:

ERROR: A socket routine returned error: The connection was refused.

ERROR: Unable to connect to server.

ERROR: Error in the LIBNAME statement.

I know that the path name is correct as SAS Studio happily uses it in PROC IMPORT.

Does anyone know how I should reference the SAS-University-Edition as a server in this LIBNAME statement?

It would be great to get this to work.

Valued Guide
Posts: 2,175

Re: SAS/ACCESS LIBNAME not working

2 years ago, Bruno posted a link to download the pc files server

Unfortunately that link no longer works.

Is the facility still available - somewhere else, perhaps?

 

Peter

Super Contributor
Posts: 275

Re: SAS/ACCESS LIBNAME not working

Check whether SAS/Access has been installed or installed correctly. Libname statement to set association with excel file need  SAS/Access module.

Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME not working

Thank you vey much to every one who answered.

Taking a quick look at what is installed in our VirtualBox (below) is "SAS/ACCESS Interface to PC Files" anything similar to SAS® PC Files Server?

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 proc setinit;

44 run;

NOTE: PROCEDURE SETINIT used (Total process time):

  real time 0.00 seconds

  cpu time 0.02 seconds

 

Original site validation data

Site name: 'SAS University Edition'.

Site number: 70068118.

CPU A: Model name='' model number='' serial='+2'.

Expiration: 16JUN2015.

Grace Period: 0 days (ending 16JUN2015).

Warning Period: 45 days (ending 31JUL2015).

System birthday: 05FEB2014.

Operating System: LIN X64 .

Product expiration dates:

---Base SAS Software 16JUN2015 (CPU A)

---SAS/STAT 16JUN2015 (CPU A)

---SAS/IML 16JUN2015 (CPU A)

---SAS/Secure 168-bit 16JUN2015 (CPU A)

---SAS/ACCESS Interface to PC Files 16JUN2015 (CPU A)

---SAS/ACCESS Interface to ODBC 16JUN2015 (CPU A)

---SAS/IML Studio 16JUN2015 (CPU A)

---SAS Workspace Server for Local Access 16JUN2015 (CPU A)

---SAS Workspace Server for Enterprise Access 16JUN2015 (CPU A)

---High Performance Suite 16JUN2015 (CPU A)

45

46 proc product_status;

47 run;

For Base SAS Software ...

  Custom version information: 9.4_M1

  Image version information: 9.04.01M1P120413

For SAS/STAT ...

  Custom version information: 13.1

For SAS/IML ...

  Custom version information: 13.1

For SAS/Secure 168-bit ...

  Custom version information: 9.4

  Image version information: 9.04.01M0P061913

For High Performance Suite ...

  Custom version information: 2.2

  Image version information: 9.04.01M0P022614

For SAS/ACCESS Interface to PC Files ...

  Custom version information: 9.4_M1

For SAS/ACCESS Interface to ODBC ...

  Custom version information: 9.4_M1

NOTE: PROCEDURE PRODUCT_STATUS used (Total process time):

  real time 0.02 seconds

  cpu time 0.04 seconds

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 7086 views
  • 6 likes
  • 7 in conversation