topic SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet in SAS Programming

SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet

kibicrealu1 — Thu, 15 Nov 2018 17:50:15 GMT

In Excel I am executing a stored process that in last step creates a table in external database (Teradata):

data teradb.a;
set b;
run;

When this is executed, a new sheet in Excel with table contents is also created. How can I prevent this from happening? I just want teradb.a to be created, don't need a separate worksheet in Excel with it...

I execute stored process in vba using following code:

Dim SAS2 As SASExcelAddInSet SAS2 = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim streams As SASRangesSet streams = New SASRanges
Sheets("Entry Criteria").Range("J9").Clear
streams.Add "XcelData", Sheets("Entry Criteria").Range(Cells(4, "D"), Cells(25, "F"))
SAS2.InsertStoredProcess "/system/UpdateParameters", Sheets("Entry Criteria").Range("J9"), , , streams

I was playing with ODS options or using proc sql with NOPRINT option, but this doesn't help. Would highly appreciate your suggestions!

Re: SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet

ChrisNZ — Thu, 15 Nov 2018 21:57:01 GMT

What happens if you clear the libname at the end of the stored process?

Re: SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet

kibicrealu1 — Fri, 16 Nov 2018 09:17:29 GMT

When I create dataset in work or any other SAS library I assigned during stored process execution everything works as intended - dataset is created, no additional info (apart from stored process status) is shown in Excel.

Re: SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet

ChrisNZ — Sun, 18 Nov 2018 20:01:11 GMT

What happens if you clear the TERADB libname at the end of the stored process?

Re: SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet

kibicrealu1 — Mon, 19 Nov 2018 09:21:27 GMT

Submitting

libname TERADB clear;

unfortunately has no impact.

Just a note (in case it is relevant here) - library is preassigned.

Re: SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet

ChrisNZ — Tue, 20 Nov 2018 01:04:13 GMT

I am unsure what to do next really. One last idea: declare a new, user-defined,library.

I am just trying to prevent to process from accessing the data and copying it to Excel.

No idea if it's any useful but that's all I have.

libname TERADB2 (TERADB);
data TERADB2.A;
  set B;
run;
libname TERADB2 clear;

The fact that you request insertion in Excel makes the not inserting when using a SAS library the odd-looking behaviour.

Re: SAS Add-In - Excel & Stored Process - don't show external database table in a separate sheet

kibicrealu1 — Wed, 21 Nov 2018 11:23:49 GMT

Thanks Chris, that didn't work as well.

However, solution turned out to be in the Tools -> Options of SAS ribbon in Excel.

Unchecking "Open output data automatically" under "Output Data Sets" section in "Data" tab did the trick.