Desktop productivity for business analysts and programmers

Read and write excel file in SAS EG

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Read and write excel file in SAS EG

 

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. 


Accepted Solutions
Solution
‎02-13-2017 07:38 AM
Esteemed Advisor
Posts: 6,723

Re: Read and write excel file in SAS EG

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,723

Re: Read and write excel file in SAS EG

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 81

Re: Read and write excel file in SAS EG

[ Edited ]

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?

Solution
‎02-13-2017 07:38 AM
Esteemed Advisor
Posts: 6,723

Re: Read and write excel file in SAS EG

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 81

Re: Read and write excel file in SAS EG

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?

Esteemed Advisor
Posts: 6,723

Re: Read and write excel file in SAS EG

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Super FREQ
Posts: 274

Re: Read and write excel file in SAS EG

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

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 231 views
  • 0 likes
  • 3 in conversation