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

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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