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

SAS/ACCESS LIBNAME - possible solution on Linux?

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

SAS/ACCESS LIBNAME - possible solution on Linux?

I posted this to my last discussion, but then realised that it has been marked as answered:

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.


Accepted Solutions
Solution
‎06-19-2014 10:20 AM
Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Posted in reply to Cynthia_sas

This is an excellent overview and very much appreciated. I think it also confirms the point Jaap was so kindly trying to make. It has also provided some insights that may help us in understand machine 'bitness' and heterogeneous o/s constraints as we set-up our research servers with SAS.

Jennifer has very kindly offered to put me in touch with the right team to answer the question.

Once again thank you (and to all others who have tried) for all the help.

View solution in original post


All Replies
Trusted Advisor
Posts: 3,212

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

The pcfiles server is a process that should be installed in a Windows environment eg your desktop.

A server could be get a connection to that.

You do not have that software on your desktop. It is not part of the analyticsu installation.

You could install that but then that port connection must be defined available. I do not expect that this has been done.

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

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Thank you Jaap, I read the page on PC file server that Bruno suggested and it said that it does not need to be installed on Windows 64-bit. Did I misread it?

SAS Super FREQ
Posts: 8,864

Re: SAS/ACCESS LIBNAME - possible solution on Linux?


Hi:

  I am not sure how the LIBNAME engine works on the SAS University Edition. Usually, when you are communicating from SAS on Unix to an Excel file on a Windows machine, you need the PC File Server and the PCFILES form of the LIBNAME engine, even when the "bitness" matches.

  You might want to open a track with Tech Support for the definitive answer to this question.

cynthia

Trusted Advisor
Posts: 3,212

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

The pcfiles software is documented to be a dedicated Windows tool to be installed in a Windows environment. It is made dependent of the ace driver of Microsoft. The latest office version is based on a zipped approach using XML files in a dedicated way almost the open office specification (7 years old)

All being documented and to be found googling.

On my handy difficult to include those links.

Do not understand what TO could add to that. Most follow ups that way do not get a follow up. (Never seen one)

---->-- ja karman --<-----
SAS Super FREQ
Posts: 708

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

The server that is named in the SERVER= option of the LIBNAME statement must be that name of the host system (Windows) where the SAS University Edition is running on. On this windows machine you have to install the the PC File Server component.

Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Thank you to all that replied.

I have been trying to get to the bottom of this for the past few days. We are trying to assess if SAS Studio (University Edition) can be used to teach SAS Base to students without having to install any other SAS software. In going through the SAS Base course the brick wall we have hit so far is in Chapter 6 "Reading Excel Worksheets".

As per the course we should be able to assign a LIBNAME to a workbook and it will create datasets for each worksheet in the workbook e.g.

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

However, as Bruno quite rightly pointed out the SAS server is installed in a Linux environment therefore this standard Windows syntax for LIBNAME won't work.

So the question is why does the equivalent Linux statement not achieve the same end? - (ref:http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002644677.htm)

Things to note:

  1. The workbook is in the shared folder accessible to both the Linux VM and the Windows host and is clearly visible through SAS Studio when the folder is expanded
  2. The path name in the call is correct as it is copied from the properties tab for the workbook in SAS Studio and works perfectly when used in a PROC IMPORT statement
  3. proc product_status in SAS Studio shows that SAS/ACCESS Interface to PC Files 16JUN2015 (CPU A) is installed

If my statement

"libname custfm PCFILES SERVER=localhost PATH='/folders/myfolders/SASCourse062014/Data/custfm.xls';" results in the following errors

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

ERROR: Unable to connect to server.

ERROR: Error in the LIBNAME statement.

then either

  • My 'server' identification is wrong, in which case I would be grateful if anyone from SAS could explain what I should do to correct this statement, baring in mind my server is the SAS running in the VM and the workbook and folder are accessible and visible to the VM (therefore the Excel workbook is ostensibly on the same machine)

OR

  • This functionality is not possible in SAS Studio, in which case this part of the course cannot be taught using SAS University Edition

OR

  • As Bruno suggested, we need to install the PC File Server component on the host Windows machine, which also negates the ability to teach the SAS Base course using SAS Studio University Edition exclusively. I would also not know how to make the LIBNAME statement work under these circumstance and would be grateful for some advice.

I am assuming if this functionality does not work for Excel, it will also not work for Microsoft Access DBs?

Super User
Posts: 7,763

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

If it is a _real_ .xls file (not a .xlsx in  disguise), SAS needs a MS component to read it. This component is only available on Windows, so you can't open the .xls locally on ANY other platform, even if the file is visible there.

The PC files server helps you circumvent this by doing the "MS-to-something universally readable" conversion on the Windows machine it is installed on (using aforesaid MS component), and SAS can then get it from there. Therefore, the PC files server _only_ runs on Windows, also.

- the PC where the PC files server is installed must be visible to the server that SAS is running on

- you need to specify a correct DNS name or IP address in the server= option

- localhost will ONLY work if SAS is installed on the SAME machine that runs the PC files server, which is obviously not the case with you since you run SAS on Linux

- keep in mind that Linux in a VM on the same hardware that runs Windows still is a DIFFERENT host with different DNS name and different IP address!!! Windows "localhost" <> Linux "localhost"!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Posted in reply to KurtBremser

Just to add, you could use OpenOffice for Linux, that reads Excel files and can save to various formats.  Haven't tried it myself, but am sure it is scriptable much like Excel.  One benefit is its free and runs on different platforms (and it didn't go with the ridiculous ribbon system), and if you put your data in a more transportable format, e.g. csv, xml etc. then your data becomes platform free and easily read. 

Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Thank you RW9.

The problem is that we cannot install anything on the Linux virtual machine that we download for SAS University Edition. Once the VM is started you simply minimise it and fire up SAS Studio on windows host machine via a web browser using the localhost:10080.

I appreciate your suggestions of workarounds, but we were testing the SAS Base Course against SAS University Edition and this course has a complete chapter on the capabilities of SAS to handle MS Excel workbooks. If this can't be made to work then we can only assume that we cannot use SAS University Edition to teach the existing SAS Base course in its entirety.

Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Posted in reply to KurtBremser

Thank you for your response Kurt.

As you know the SAS University Edition is implemented via a virtual machine. We simply download a fully encapsulated virtual machine, which has SAS 9.4 running on Centos and access SAS via a web browser on the host WIndows machine.

The .xls file I am using is provided by SAS as part of the SAS Base course.

When I execute the command "proc setinit;" on SAS Studio the log displays the following:

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)

As you can see SAS/ACCESS Interface to PC Files and SAS/ACCESS Interface to ODBC are both installed on the Linux (LIN X64) virtual machine.

As part of the download and installation of the SAS University Edition you are provided instructions to set-up a shared folder, which the virtual machine sees as part of its machine and the local Windows machine also sees as part of its machine. In my case this folder is named exactly as per the SAS University Edition installation instructions - "/folders/myfolders/".

The fact that the statement PROC IMPORT, when run via SAS Studio, works perfectly to import Excel worksheets indicates to me that both accessibility to "/folders/myfolders/" and "SAS/ACCESS Interface to PC Files", for this purpose, are functioning correctly on the Linux vm. Therefore, it looks like SAS is installed on the SAME machine that runs the PC files server and the .xls file is also on the same machine. You can assume there is no SAS installation on the Windows host machine.

It would be great if someone could attempt this LIBNAME statement on their version of SAS University Edition. I have attached the .xls we have from SAS.

If libname custfm PCFILES is made to work correctly then 2 datasets should be visible in 'custfm' under Libraries in SAS Studio, 'Females$' and 'Males$''.

Super User
Posts: 7,763

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

- Access to PC Files covers a lot more than just .xls, .doc or .mdb. These only work on Windows.

- once again, logically your Linux VM is a completely different computer that just has (part of) the local Windows drive mounted in its directory tree. Apart from that, it could be sitting on the moon.

- that you see Access to PC Files in your setinit means access to .xlsx, .csv, .rtf and the like, but not the older MS formats.

- if you want to use LIBNAME myxls PCFILES SERVER=D2323 PATH='c:\demo.xls';, you need to start the PC Files Server in your Windows environment and use the correct hostname of your windows partition (not "localhost") in the libname statement.

- you might simply convert the example .xls files to .xlsx with Excel and make appropriate corrections in the course material.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Posted in reply to KurtBremser

Thank you Kurt.

I appreciate all your help, but I am not sure what I have written that has given the impression that I do not understand the concept of a virtual Linux machine on a physical Windows host?

I have taken your advice and saved the custfm.xls file in .xlsx format and was extremely hopeful that this would do the trick, changing the course material would not have been an issue. Unfortunately this did not resolve the problem

All I need to know is whether the functionality to load Excel multisheet workbooks, via LIBNAME, as Library datasets is available in SAS University Edition, without the use of any other SAS software on the host Windows machine? Can someone from the team in SAS who have worked on this product answer this question?

If it is not, then we know we can't use it to teach the course as we would like. If it is, can someone from SAS please try it using either the .xls or xlsx version of the file I attached earlier (from the SAS BASE course) and provide some clear instructions?

Once again, please do not consider me ungrateful for the help, but the issue is not about teaching me to understand the difference between virtual and host, or Windows and Linux for that matter.

It is simply a question of whether the functionality to load Excel workbooks as libraries using LIBNAME is available in SAS University Edition, without having to install any other SAS software on the Windows host? If so, how? 

Once again, thank you for trying to help.

Super User
Posts: 7,763

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

I appreciate all your help, but I am not sure what I have written that has given the impression that I do not understand the concept of a virtual Linux machine on a physical Windows host?


The fact that you tried to connect to a server process running on Windows by using localhost on a UNIX VM?

I have taken your advice and saved the custfm.xls file in .xlsx format and was extremely hopeful that this would do the trick, changing the course material would not have been an issue. Unfortunately this did not resolve the problem


What were your problems? I have to admit that I do not work with Excel data _at all_, but I have got the impression that from SAS 9.3 support for .xlsx is platform independent. With proper information, someone like Jaap Karman will most likely be able to help you.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 23

Re: SAS/ACCESS LIBNAME - possible solution on Linux?

Posted in reply to KurtBremser

Thank you Kurt. I feel duly humbled.

I think it is probably best if I abandon this thread and post another which explicitly asks if the functionality I want is supported. As per my previous post this is:

"Does SAS University Edition support the functionality to load Excel workbooks as libraries using LIBNAME without having to install any other SAS software on the Windows host? If so, how? "

Wishing you all the best.

🔒 This topic is solved and locked.

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

Discussion stats
  • 31 replies
  • 2402 views
  • 5 likes
  • 9 in conversation