topic Re: EXCEL VBA SAS ADD IN in Microsoft Integration with SAS

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Wed, 28 Jun 2017 10:41:47 GMT

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

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

EXCEL VBA SAS ADD IN

DipeshGupta — Wed, 28 Jun 2017 08:00:23 GMT

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.

Re: EXCEL VBA SAS ADD IN

RW9 — Wed, 28 Jun 2017 08:26:45 GMT

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?

Re: EXCEL VBA SAS ADD IN

Kurt_Bremser — Wed, 28 Jun 2017 08:43:35 GMT

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.

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Wed, 28 Jun 2017 08:52:06 GMT

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

Re: EXCEL VBA SAS ADD IN

Kurt_Bremser — Wed, 28 Jun 2017 08:56:33 GMT

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.

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Wed, 28 Jun 2017 09:05:32 GMT

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.

Re: EXCEL VBA SAS ADD IN

Kurt_Bremser — Wed, 28 Jun 2017 09:30:44 GMT

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

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Wed, 28 Jun 2017 09:33:00 GMT

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

How can we do that..??

Re: EXCEL VBA SAS ADD IN

Kurt_Bremser — Wed, 28 Jun 2017 10:11:12 GMT

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

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Wed, 28 Jun 2017 10:25:46 GMT

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.

Re: EXCEL VBA SAS ADD IN

Kurt_Bremser — Wed, 28 Jun 2017 10:39:28 GMT

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.

Re: EXCEL VBA SAS ADD IN

Kurt_Bremser — Wed, 28 Jun 2017 10:53:41 GMT

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

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Wed, 28 Jun 2017 11:00:12 GMT

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.

Re: EXCEL VBA SAS ADD IN

CaseySmith — Thu, 29 Jun 2017 01:58:17 GMT

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.

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Thu, 29 Jun 2017 07:07:17 GMT

Thankyou Smith.

Thats exactly what I was looking for.

Thanks Kurt for always helping Me 🙂

Just a normal advice

Is running SAS from command prompt and then running command prompt from Excel more efficient then running SAS directly from excel..??

Efficiency in terms of speed and quality.

Re: EXCEL VBA SAS ADD IN

DipeshGupta — Tue, 04 Jul 2017 05:21:39 GMT

ThankYou So Much. It did my Work and sorry for replying late 🙂