SAS Add-In for Microsoft Office: Excel & Stored Process - don't show external database table in a...
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, new sheet in Excel with its 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 SASExcelAddIn
Set SAS2 = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim streams As SASRanges
Set 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!
EDIT:
As requested - some screens with stored process properties :
excel vba sas
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
|
show 1 more comment
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, new sheet in Excel with its 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 SASExcelAddIn
Set SAS2 = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim streams As SASRanges
Set 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!
EDIT:
As requested - some screens with stored process properties :
excel vba sas
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
3
What are the properties of the Stored Process in the SAS Management Console ? In particular, in the Execution tab is Stream or Package checked ? Can you show more of the stored process source code ?
– Richard
Nov 15 '18 at 19:07
I have tried with checking/unchecking those options - result is the same. It is worth mentioning that when I create output dataset in work or any other SAS library, that I have assigned during stored process execution, everything works as intended - dataset is created and no additional info (apart from stored process status) is shown in Excel.
– kibicrealu1
Nov 16 '18 at 9:19
During the testing, are you pre-deleting (if exists) tableteradb.a? Remote data bases don't overwrite existing tables (SAS will overwrite native data sets unless NOREPLACE is active). Is it possible the STP is logging any errors ?
– Richard
Nov 16 '18 at 12:11
Yes - I do delete that table if it exists before re-creating it. Code runs with no errors. Table is created as intended, the only problem is that a new spreadhseet with its contents is also created in Excel ( I don't need it). When output dataset is saved in SAS library instead of Teradata library, mentioned new spreadsheet is not created.
– kibicrealu1
Nov 16 '18 at 12:34
What is the name of the unexpected new sheet ? Are you certain the parameters are correct ? Check the documentation documentation.sas.com/… -- What happens if the output location (question showsSheets("Entry Criteria").Range("J9")) is left blank ?
– Richard
Nov 16 '18 at 14:03
|
show 1 more comment
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, new sheet in Excel with its 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 SASExcelAddIn
Set SAS2 = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim streams As SASRanges
Set 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!
EDIT:
As requested - some screens with stored process properties :
excel vba sas
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
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, new sheet in Excel with its 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 SASExcelAddIn
Set SAS2 = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim streams As SASRanges
Set 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!
EDIT:
As requested - some screens with stored process properties :
excel vba sas
excel vba sas
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
edited Nov 16 '18 at 9:42
kibicrealu1
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
asked Nov 15 '18 at 17:38
kibicrealu1kibicrealu1
2816
2816
3
What are the properties of the Stored Process in the SAS Management Console ? In particular, in the Execution tab is Stream or Package checked ? Can you show more of the stored process source code ?
– Richard
Nov 15 '18 at 19:07
I have tried with checking/unchecking those options - result is the same. It is worth mentioning that when I create output dataset in work or any other SAS library, that I have assigned during stored process execution, everything works as intended - dataset is created and no additional info (apart from stored process status) is shown in Excel.
– kibicrealu1
Nov 16 '18 at 9:19
During the testing, are you pre-deleting (if exists) tableteradb.a? Remote data bases don't overwrite existing tables (SAS will overwrite native data sets unless NOREPLACE is active). Is it possible the STP is logging any errors ?
– Richard
Nov 16 '18 at 12:11
Yes - I do delete that table if it exists before re-creating it. Code runs with no errors. Table is created as intended, the only problem is that a new spreadhseet with its contents is also created in Excel ( I don't need it). When output dataset is saved in SAS library instead of Teradata library, mentioned new spreadsheet is not created.
– kibicrealu1
Nov 16 '18 at 12:34
What is the name of the unexpected new sheet ? Are you certain the parameters are correct ? Check the documentation documentation.sas.com/… -- What happens if the output location (question showsSheets("Entry Criteria").Range("J9")) is left blank ?
– Richard
Nov 16 '18 at 14:03
|
show 1 more comment
3
What are the properties of the Stored Process in the SAS Management Console ? In particular, in the Execution tab is Stream or Package checked ? Can you show more of the stored process source code ?
– Richard
Nov 15 '18 at 19:07
I have tried with checking/unchecking those options - result is the same. It is worth mentioning that when I create output dataset in work or any other SAS library, that I have assigned during stored process execution, everything works as intended - dataset is created and no additional info (apart from stored process status) is shown in Excel.
– kibicrealu1
Nov 16 '18 at 9:19
During the testing, are you pre-deleting (if exists) tableteradb.a? Remote data bases don't overwrite existing tables (SAS will overwrite native data sets unless NOREPLACE is active). Is it possible the STP is logging any errors ?
– Richard
Nov 16 '18 at 12:11
Yes - I do delete that table if it exists before re-creating it. Code runs with no errors. Table is created as intended, the only problem is that a new spreadhseet with its contents is also created in Excel ( I don't need it). When output dataset is saved in SAS library instead of Teradata library, mentioned new spreadsheet is not created.
– kibicrealu1
Nov 16 '18 at 12:34
What is the name of the unexpected new sheet ? Are you certain the parameters are correct ? Check the documentation documentation.sas.com/… -- What happens if the output location (question showsSheets("Entry Criteria").Range("J9")) is left blank ?
– Richard
Nov 16 '18 at 14:03
3
3
What are the properties of the Stored Process in the SAS Management Console ? In particular, in the Execution tab is Stream or Package checked ? Can you show more of the stored process source code ?
– Richard
Nov 15 '18 at 19:07
What are the properties of the Stored Process in the SAS Management Console ? In particular, in the Execution tab is Stream or Package checked ? Can you show more of the stored process source code ?
– Richard
Nov 15 '18 at 19:07
I have tried with checking/unchecking those options - result is the same. It is worth mentioning that when I create output dataset in work or any other SAS library, that I have assigned during stored process execution, everything works as intended - dataset is created and no additional info (apart from stored process status) is shown in Excel.
– kibicrealu1
Nov 16 '18 at 9:19
I have tried with checking/unchecking those options - result is the same. It is worth mentioning that when I create output dataset in work or any other SAS library, that I have assigned during stored process execution, everything works as intended - dataset is created and no additional info (apart from stored process status) is shown in Excel.
– kibicrealu1
Nov 16 '18 at 9:19
During the testing, are you pre-deleting (if exists) table
teradb.a ? Remote data bases don't overwrite existing tables (SAS will overwrite native data sets unless NOREPLACE is active). Is it possible the STP is logging any errors ?– Richard
Nov 16 '18 at 12:11
During the testing, are you pre-deleting (if exists) table
teradb.a ? Remote data bases don't overwrite existing tables (SAS will overwrite native data sets unless NOREPLACE is active). Is it possible the STP is logging any errors ?– Richard
Nov 16 '18 at 12:11
Yes - I do delete that table if it exists before re-creating it. Code runs with no errors. Table is created as intended, the only problem is that a new spreadhseet with its contents is also created in Excel ( I don't need it). When output dataset is saved in SAS library instead of Teradata library, mentioned new spreadsheet is not created.
– kibicrealu1
Nov 16 '18 at 12:34
Yes - I do delete that table if it exists before re-creating it. Code runs with no errors. Table is created as intended, the only problem is that a new spreadhseet with its contents is also created in Excel ( I don't need it). When output dataset is saved in SAS library instead of Teradata library, mentioned new spreadsheet is not created.
– kibicrealu1
Nov 16 '18 at 12:34
What is the name of the unexpected new sheet ? Are you certain the parameters are correct ? Check the documentation documentation.sas.com/… -- What happens if the output location (question shows
Sheets("Entry Criteria").Range("J9")) is left blank ?– Richard
Nov 16 '18 at 14:03
What is the name of the unexpected new sheet ? Are you certain the parameters are correct ? Check the documentation documentation.sas.com/… -- What happens if the output location (question shows
Sheets("Entry Criteria").Range("J9")) is left blank ?– Richard
Nov 16 '18 at 14:03
|
show 1 more comment
1 Answer
1
active
oldest
votes
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.
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325078%2fsas-add-in-for-microsoft-office-excel-stored-process-dont-show-external-da%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
add a comment |
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.
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
add a comment |
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.
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
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.
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
answered Nov 21 '18 at 11:24
kibicrealu1kibicrealu1
2816
2816
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325078%2fsas-add-in-for-microsoft-office-excel-stored-process-dont-show-external-da%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
What are the properties of the Stored Process in the SAS Management Console ? In particular, in the Execution tab is Stream or Package checked ? Can you show more of the stored process source code ?
– Richard
Nov 15 '18 at 19:07
I have tried with checking/unchecking those options - result is the same. It is worth mentioning that when I create output dataset in work or any other SAS library, that I have assigned during stored process execution, everything works as intended - dataset is created and no additional info (apart from stored process status) is shown in Excel.
– kibicrealu1
Nov 16 '18 at 9:19
During the testing, are you pre-deleting (if exists) table
teradb.a? Remote data bases don't overwrite existing tables (SAS will overwrite native data sets unless NOREPLACE is active). Is it possible the STP is logging any errors ?– Richard
Nov 16 '18 at 12:11
Yes - I do delete that table if it exists before re-creating it. Code runs with no errors. Table is created as intended, the only problem is that a new spreadhseet with its contents is also created in Excel ( I don't need it). When output dataset is saved in SAS library instead of Teradata library, mentioned new spreadsheet is not created.
– kibicrealu1
Nov 16 '18 at 12:34
What is the name of the unexpected new sheet ? Are you certain the parameters are correct ? Check the documentation documentation.sas.com/… -- What happens if the output location (question shows
Sheets("Entry Criteria").Range("J9")) is left blank ?– Richard
Nov 16 '18 at 14:03