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:
Has anyone encounter the same issue and how did you resolve this?
Thanks for your help 🙂
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:
Try copying to WORK rather than HOME.
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:
Thanks. That makes sense why it didn't work.
Appreciate your help
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!