topic Re: Importing SAS Dataset into excel using SAS Add-in for Excel in Microsoft Integration with SAS

Importing SAS Dataset into excel using SAS Add-in for Excel

AnandV — Thu, 16 Jun 2016 09:10:40 GMT

I am using SAS 9.4 hosted on Linux servers. Using EG I have run my code and created a dataset which is saved in my linux home directory.

I also have SAS Addin to Excel connected to same sas 9.4 on linux. I want to Import my sas dataset current stored at my linux home dir to excel and create a pivot out of it. Is there any way to do so? When I go to sas data under sas addin for excel I only see default sas folders.

I know I can do winscp to move the sas dataset from linux to windows and then use that in excel but since these are very large datasets and It takes time, also I have to move these after every execution. looking for more better solution. Kindly share any example links if possible.

thanks!

Re: Importing SAS Dataset into excel using SAS Add-in for Excel

Kurt_Bremser — Thu, 16 Jun 2016 09:21:34 GMT

In order to see SAS datasets in the AddIn, they have to reside in libraries that are assigned in the workspace server by default (through metadata or the autoexec file).

Libraries that you assign "manually" in your EG will not be present in a workspace server session started from elsewhere.

I suggest you put your dataset in your SASUSER library, as that is assigned automatically and persists over sessions.

Re: Importing SAS Dataset into excel using SAS Add-in for Excel

LinusH — Thu, 16 Jun 2016 11:39:42 GMT

Allthough, I think that the workspace server by default uses the -rsasuser option, so may need to involve your SAS server admin to make this work.

If this is for your personal use/pleasure, another option is to export the data directly to Excel from EG, hence using Excel standard pivoting features.

Re: Importing SAS Dataset into excel using SAS Add-in for Excel

Kurt_Bremser — Thu, 16 Jun 2016 12:15:16 GMT

@LinusH wrote:

Allthough, I think that the workspace server by default uses the -rsasuser option

You're right.

That obviously was one of the first things I changed when setting up our own server instances. Only the SASApp tree here has that option.

So in this case a library needs to be set up by the admin to facilitate the OP's wishes.

Re: Importing SAS Dataset into excel using SAS Add-in for Excel

rogerjdeangelis — Fri, 17 Jun 2016 21:56:09 GMT

R/Python e excellent Unix(and widows) packages to operate on excel

http://www.r-bloggers.com/a-million-ways-to-connect-r-and-excel/

https://automatetheboringstuff.com/chapter12/

and if you have IML, can interface with SAS to read and wrtite SAS datasets.

R can already read SAS datasets, using two packages, haven and sas7bdat.

If you tell us what you need to accomplish we may be able to show you several solutions.

Re: Importing SAS Dataset into excel using SAS Add-in for Excel

Newuser663 — Wed, 26 Jun 2019 22:58:58 GMT

Thank you so much! I am having the same issue. We are saving output data in remote server through Winscp. I am wondering how can we show those data folders in SAS Excel add-in? We only can see default folders, like SASUser. Thank you in advance!

Re: Importing SAS Dataset into excel using SAS Add-in for Excel

Kurt_Bremser — Tue, 02 Jul 2019 11:33:16 GMT

Define a library in metadata for that directory, in the correct Application Server.

Note that responding to a three-year-old thread won't get you much of an audience. If you want more people to see your question, open a new thread of your own.