SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Re: EXCEL VBA SAS ADD IN

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Re: EXCEL VBA SAS ADD IN

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

 

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


Accepted Solutions
Solution
‎07-04-2017 01:21 AM
SAS Super FREQ
Posts: 291

Re: EXCEL VBA SAS ADD IN

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.

View solution in original post


All Replies
Super User
Posts: 6,963

Re: EXCEL VBA SAS ADD IN


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 55

Re: EXCEL VBA SAS ADD IN

Okay but I can't wait till then Smiley Happy

 

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.

Contributor
Posts: 55

EXCEL VBA SAS ADD IN

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.

Super User
Super User
Posts: 7,413

Re: EXCEL VBA SAS ADD IN

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?

Super User
Posts: 6,963

Re: EXCEL VBA SAS ADD IN

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 55

Re: EXCEL VBA SAS ADD IN

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

Super User
Posts: 6,963

Re: EXCEL VBA SAS ADD IN

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 55

Re: EXCEL VBA SAS ADD IN

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.

Super User
Posts: 6,963

Re: EXCEL VBA SAS ADD IN

Then you should add options to the first 7 STPs that allow running them without creating output for Excel.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 55

Re: EXCEL VBA SAS ADD IN

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

How can we do that..??

Super User
Posts: 6,963

Re: EXCEL VBA SAS ADD IN

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 55

Re: EXCEL VBA SAS ADD IN

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.

Super User
Posts: 6,963

Re: EXCEL VBA SAS ADD IN

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎07-04-2017 01:21 AM
SAS Super FREQ
Posts: 291

Re: EXCEL VBA SAS ADD IN

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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