Okay so I guess there is no other way to get through this..??
I will have to let excel import all the files..??l
Unchecking "Open output data automatically" (in SAS Add-in's->Tools->Options->Data) should prevent any output data sets created by your stored process (and any other stored processes, programs, or tasks, since a global option) from automatically being opened in Excel.
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
I have a program where I have connected SAS through VBA using Excel Add-in and then I am running my SAS Stored Process from Excel-VBA but the problem is the Stored Process creates several outputs and I dont want to call those outputs in excel and just want to run the program.
I am doing something like this.
sub sas() Dim sas As SASExcelAddIn Set sas = Application.COMAddIns.Item("Sas.ExcelAddIn").Object Dim prompts As SASPrompts Set prompts = New SASPrompts Dim rng As Range Set rng = Worksheets(1).Range("A1") Dim cov As String prompts.Add "STATS", cov sas.InsertStoredProcess "/User Folders/klj/My Folder/Stored Process for Program 6", rng, prompts end sub
Now I dont want the output of this program to be called in excel when this runs.
Any idea how can I run the SAS Stored Process from Excel-VBA without calling any outputs in Excel.
If you don't want the outputs in Excel, why run it through Excel at all, thats just using it for the sake of using it?
If you just want to run a program without output, add a respective prompt to the stored process and use that to control if output is created or not.
Sorry,
Maybe I haven't explained my problem properly.
Let me explain it properly.
Its not like I dont want to create output sets because creating output datasets is necessary for my project otherwise the next program which depends on the output of the previous program will show error and hence output data sets will always be created in my SAS Stored Process program but I dont want to call the output sets in excel.
I even tried using Proc sql -> Drop Table (to drop unnecssary tables i.e. to drop 10 tables out of 15)
but whenever I use drop table in my program then it doesn't even call the 5 remaining tables and shows error.
I just simple want to run SAS from excel addin and dont want the output sets to be displayed in excel
If you don't need output in Excel, then what's the use in adding an additional level of complexity and instability when you just need to run a sequence of stored processes? You can do that quite easily from EG.
I know that I can do that easily in EG but for my model to run I have to run it through excel.
Actually I have 8 Stored Process Programs in SAS EG and the first seven programs are used to create the 5 tables that are required in the end.
Now for my purpose I want to run all the programs from excel but want to import output only from program 8 and not from other programs because importing the output dataset for all the programs takes a lot of time.
Then you should add options to the first 7 STPs that allow running them without creating output for Excel.
okay, I didn't know there is such option in SAS Stored Process.
How can we do that..??
Just add prompts, and make the creation of output dependent on those.
%macro do_output;
%if &prompt_for_output = yes %then %do;
/* insert your output/export code here */
%end;
%mend;
%do_output
Actually I am writing a code in Excel-VBA to run a sas program i.e.
sub sas() Dim sas As SASExcelAddIn Set sas = Application.COMAddIns.Item("Sas.ExcelAddIn").Object Dim prompts As SASPrompts Set prompts = New SASPrompts Dim rng As Range Set rng = Worksheets(1).Range("A1") Dim cov As String prompts.Add "STATS", cov sas.InsertStoredProcess "/User Folders/klj/My Folder/Stored Process for Program 6", rng, prompts end sub
This code is wriiten in VBA and so I dont have any export code in SAS to impose if condition on it.
This VBA code connects to SAS and runs the stored process for program 6. Everything is fine but it automatically imports all the created output data set so I wanted to know is there a condition which can be placed in Excel-VBA Add in to import data only when the user want else only run the program.
The specific purpose of the AddIn for MS Office is to catch all the output from SAS into the Office application. As long as the SAS side sends output, the AddIn will deal with it. Actually it has to, as the STP won't finish until it has delivered all its output.
@DipeshGupta wrote:
Okay so I guess there is no other way to get through this..??
I will have to let excel import all the files..??l
Unless somebody else comes up with a good idea to do this on the AddIn side, I fear so.
Okay but I can't wait till then 🙂
I am thinking of connecting Excel with command line and then connect the command line with SAS and I hope there is no such problem of importing SAS output dataset when through a command prompt.
Unchecking "Open output data automatically" (in SAS Add-in's->Tools->Options->Data) should prevent any output data sets created by your stored process (and any other stored processes, programs, or tasks, since a global option) from automatically being opened in Excel.
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
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 25. Read more here about why you should contribute and what is in it for you!