BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DipeshGupta
Calcite | Level 5

Okay so I guess there is no other way to get through this..??

 

I will have to let excel import all the files..??l

1 ACCEPTED SOLUTION

Accepted Solutions
CaseySmith
SAS Employee

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

View solution in original post

16 REPLIES 16
DipeshGupta
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Kurt_Bremser
Super User

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.

DipeshGupta
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

DipeshGupta
Calcite | Level 5

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.

DipeshGupta
Calcite | Level 5

okay, I didn't know there is such option in SAS Stored Process.

How can we do that..??

Kurt_Bremser
Super User

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
DipeshGupta
Calcite | Level 5

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.

Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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

DipeshGupta
Calcite | Level 5

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.

CaseySmith
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 16 replies
  • 5109 views
  • 1 like
  • 4 in conversation