BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thomas111
Calcite | Level 5

I am trying to get SAS data into my Excel spreadsheet and my below code can get the data from SASHelp library but not from WORK library. 

 

Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
sas.InsertDataFromLibrary "SASApp", "SASHelp", "CLASS", Sheet1.Range("A1")

 

But after copying the CLASS data to WORK library in SAS EG 

proc copy in=sashelp out=home;
select class;
run;

 

And changing the VBA code in my Excel to 

sas.InsertDataFromLibrary "SASApp", "WORK", "CLASS", Sheet1.Range("A1")

 

It gives me the below error:

 

sas error.png

 

Has anyone encounter the same issue and how did you resolve this? 

 

Thanks for your help 🙂

 

1 ACCEPTED SOLUTION

Accepted Solutions
fifthand57th
SAS Employee

@thomas111 

 

The WORK library in EG and the WORK library in the SAS Add-in are not the same libraries. Enterprise Guide instantiates it's own workspace server session with a unique WORK library for that session of EG. The SAS Add-in does the same. They do not share a WORK library.

 

In Excel run the following code using SAS Programs to create the CLASS data set in the WORK library in the current workspace server session:

 

data class;
set sashelp.class;
run;

 

Then run the following VBA in the same session of Excel:

 

Sub InsertDataView()

  Dim sas As SASExcelAddIn
  Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

 

  Dim data As SASDataView
  Set data = sas.InsertDataFromLibrary("SASApp", "WORK", "CLASS", Sheet1.Range("A5"), 10, False, "Sex='F'", "Age DESC", True)

End Sub

 

Should see the results below:

 

2019-10-03_10-58-09.jpg

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

Try copying to WORK rather than HOME.

thomas111
Calcite | Level 5
Sorry, when I actually tried I had WORK library in PROC COPY statement. I can also see the CLASS dataset added into the WORK library.
thomas111
Calcite | Level 5
I can use the _PRODSAVAIL dataset in the WORK library and it works fine but not any other dataset in the WORK library.
fifthand57th
SAS Employee

@thomas111 

 

The WORK library in EG and the WORK library in the SAS Add-in are not the same libraries. Enterprise Guide instantiates it's own workspace server session with a unique WORK library for that session of EG. The SAS Add-in does the same. They do not share a WORK library.

 

In Excel run the following code using SAS Programs to create the CLASS data set in the WORK library in the current workspace server session:

 

data class;
set sashelp.class;
run;

 

Then run the following VBA in the same session of Excel:

 

Sub InsertDataView()

  Dim sas As SASExcelAddIn
  Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

 

  Dim data As SASDataView
  Set data = sas.InsertDataFromLibrary("SASApp", "WORK", "CLASS", Sheet1.Range("A5"), 10, False, "Sex='F'", "Age DESC", True)

End Sub

 

Should see the results below:

 

2019-10-03_10-58-09.jpg

thomas111
Calcite | Level 5

Thanks. That makes sense why it didn't work. 

 

Appreciate your help 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Discussion stats
  • 5 replies
  • 1162 views
  • 3 likes
  • 3 in conversation