topic InsertDataFromLibrary works for some dataset but not for others in Microsoft Integration with SAS

InsertDataFromLibrary works for some dataset but not for others

thomas111 — Wed, 02 Oct 2019 21:40:50 GMT

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 🙂

Re: InsertDataFromLibrary works for some dataset but not for others

SASKiwi — Wed, 02 Oct 2019 22:13:26 GMT

Try copying to WORK rather than HOME.

Re: InsertDataFromLibrary works for some dataset but not for others

thomas111 — Wed, 02 Oct 2019 22:22:41 GMT

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.

Re: InsertDataFromLibrary works for some dataset but not for others

thomas111 — Thu, 03 Oct 2019 14:34:51 GMT

I can use the _PRODSAVAIL dataset in the WORK library and it works fine but not any other dataset in the WORK library.

Re: InsertDataFromLibrary works for some dataset but not for others

fifthand57th — Thu, 03 Oct 2019 15:00:42 GMT

@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:

Re: InsertDataFromLibrary works for some dataset but not for others

thomas111 — Thu, 03 Oct 2019 15:29:34 GMT

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

Appreciate your help