Running a saved SAS process in Excel fails after two starts

Question

Hope this is the right place to ask this question.

I recently created a data analysis tool in Excel that works by sending data to a stored SAS process (as an "input stream"), starting processes and displaying the results in Excel.

I also use some code to check and delete all active stored processes from the workbook before starting the process again.

This is executed successfully the first 2 times, but on the third attempt it fails. He always fails on his third attempt, and I cannot understand why.

Is there any kind of memory allocation for Excel VBA that is exhausted at this point? Or some other buffer that exceeded? I entered every line of VBA code and it seems to hang (in the third run) in the following line:

SAS.InsertStoredProcess processLoc, _ outputSheet.Range("A1"), , , inputStream 

---

Code used to run the SAS add-in for Microsoft Office:

 Dim SAS As SASExcelAddIn Set SAS = Application.COMAddIns.Item("SAS.ExcelAddIn").Object 

Code used to remove stored processes from the target output sheet:

 Dim Processes As SASStoredProcesses Set Processes = SAS.GetStoredProcesses(outputSheet) Dim i As Integer For i = 1 To Processes.Count ' MsgBox Processes.Item(i).DisplayName Processes.Item(i).Delete Next i 

The code used to insert and start the stored process:

 Dim inputStream As SASRanges Set inputStream = New SASRanges inputStream.Add "Prompts", inputSheet.Range("DrillDown_Input") SAS.InsertStoredProcess processLoc, _ outputSheet.Range("A1"), , , inputStream 

Greetings

Answer 1

I do not know if this will be useful, but I had a similar problem with running a DLL written in C ++ through VBA. The problem arose because the function in the DLL returned a double value that I do not need, and therefore the code in VBA did

 Call SomeProcessFromDLL() 

But the process returned a double floating point value that “populated” some buffer memory in VBA, and VBA has a limited buffer (I think it refused 8 attempts). So the solution for me was

 Dim TempToDiscard as Double TempToDiscard = SomeProcessFromDLL() 

Perhaps searching for documentation on the called process will help here, especially if it returns some value that needs to be dropped anyway, for example

 Return 0; 

Answer 2

I never liked using IOM in VBA, mainly due to link issues and the need to make client installations when deploying applications. Last year, I found MUCH the best way to connect Excel and SAS - using the Stored Process web application. Just set up the server side SAS process with streaming output and pass your inputs through an Excel web request. No client installs, does not worry about updates to the SAS version, hardly any code - I am surprised that it is not used more often!

See: http://rawsas.blogspot.co.uk/2016/11/sas-as-service-easy-way-to-get-sas-into.html

Answer 3

With regard to reflection, my theory here will be that you cross the boundaries of multi-bridge connections. Each multi-bridge connection represents a port, and the more ports you use, the more parallel connections. By default, there are three, maybe you have two, or are you simultaneously launching another STP?

This explains the behavior. I had a spreadsheet called STP, and it always failed in the fourth call because the first three were running. You can get around this: a) by increasing the number of multi-bridge connections, or b) by a chain of processes so that they are executed sequentially.