topic Re: Read and write excel file in SAS EG in SAS Enterprise Guide

Read and write excel file in SAS EG

Riana — Mon, 13 Feb 2017 10:04:56 GMT

Hi,

I've an excel file on drive C: and run this code in SAS EG to read it:

libname db xlsx "C:\Users\myID\SAS\test_Libname.xlsx";

and it runs without errors but when I click on 'Libraries' folder under 'Servers', I can view the 'db' but when I try to open it, it doesn't show anythings(the file is not empty).

Thanks.

Re: Read and write excel file in SAS EG

Kurt_Bremser — Mon, 13 Feb 2017 10:13:35 GMT

Please post how your SAS is set up. Do you have a remote server? Is the server's name "Local", or something else?

Re: Read and write excel file in SAS EG

Riana — Mon, 13 Feb 2017 12:39:59 GMT

I'm using VPN to connect to the remote SAS servers, no local server, the 'libraries' folder is under Servers->SASApp. I've installed SAS EG locally on my computer.

If I had access to a local server and ran this libname, could I see the dataset?

Re: Read and write excel file in SAS EG

Kurt_Bremser — Mon, 13 Feb 2017 12:07:31 GMT

So you have a server SASApp, which is the default name of the workspace server when a BI server has been set up.

This means that the SAS workspace server instance runs on a machine different from your desktop. To have access to your C:\ drive, you would need to share it as a network resource and then have that network resource mounted on the SAS server. In most organizations, this will not be done by the IT people, for good reasons (manageability, security etc).

Alternatively, you should let your IT people set up a shared network resource on a file server which can then be mounted on both your desktop and the SAS server; you can then use that location to share data with the SAS server.

A Local SAS server would need an additional installation and license; highly unlikely that someon would spend the effort and money just so you can read Excel files directly.

Enterprise Guide has an Import task that can be automated within the context of a EG project. This Import task handles the file transfer to the SAS server transparently for you.

Re: Read and write excel file in SAS EG

Riana — Mon, 13 Feb 2017 12:38:10 GMT

Thank you KurtBremser for your quick answer.

Can you please guide me if there is any link(or links) about it that i can read more?

Re: Read and write excel file in SAS EG

CaseySmith — Mon, 13 Feb 2017 17:05:46 GMT

I'll also mention, there was an issue viewing XLSX engine data in the EG data grid (would cause EG to hang) that was resolved in EG 7.13. If the problem you are seeing is specific to XLSX engine data (ex. doesn't occur with BASE engine data), you may be encountering this issue. If you are encountering this issue, upgrading to EG 7.13 should resolve.

Casey

Re: Read and write excel file in SAS EG

Kurt_Bremser — Mon, 13 Feb 2017 22:51:06 GMT

Setting up file shares and mounting them on other machines is not so trivial and would go beyond the scope of this forum. As already mentioned, talk to your IT people, they know your network topology and the tools/protocols available.

The data import task in Enterprise Guide is basically self-explaining (like all EG tasks).