SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Reply
New Contributor
Posts: 3

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

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!

Esteemed Advisor
Posts: 5,915

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 5,063

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

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.

Data never sleeps
Esteemed Advisor
Posts: 5,915

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


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

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

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.

 

 

Post a Question
Discussion Stats
  • 4 replies
  • 250 views
  • 0 likes
  • 4 in conversation