topic Re: Macro/vbscript breaking down? in SAS Enterprise Guide

Macro/vbscript breaking down?

Simi — Tue, 14 Jun 2011 12:59:35 GMT

Hi,

I am using the EG 4.3 ,where I have a certain project set up. Inside the project there are 3 simple macros (for interchanging some filters based on a name from a list) as well as approx 200 other query buiders, simple run and extraction programs etc. Now, I need to run this project 70 times based on those different names from the list, therefore I have found the program in VBS that I have been using.

Now, this all runs smoothly, however the problem I have is that it keeps on 'crashing' (i.e. stops processing) in random places at random times. Sometimes it does 20 items from the list (i.e. runs the project 20 times) and then stops, other times it just runs 2 times and stops. I have tried to split the project into 2 smaller ones (thinking that this could be due the the amount of queries), but the problem still prevails.

On the other hand I have identical VBS attached to different project (it's kind of a prestage to this one above), which has identical macros, but has only about 10 queries and this one runs smoothly (i.e. does it 70 times without interuption).

Does anyone know what could cause these interuptions and how to rectify this?

Many thanks in advance,

Simi

P.S.: the VBS code is as follows:
[pre]
Option Explicit
Dim app
Call dowork

If not (app Is Nothing) Then
app.Quit
Set app = Nothing
End If

Sub dowork()
On Error Resume Next
Dim prjName
Dim prjObject
Dim dc
Dim d
Dim tc
Dim t
Dim rc
Dim r
Dim outfile
Dim fileSys
Dim Contents
Dim ext

outfile = "" 'Output file name

Set fileSys = CreateObject("Scripting.FileSystemObject")
Set Contents = fileSys.OpenTextFile(outfile, 2, True)

'Name the project to run
prjName = "" 'Project Name

Set app = CreateObject("SASEGObjectModel.Application.4.3")
If Checkerror("CreateObject") = True Then
Exit Sub
End If

Set prjObject = app.Open(prjName,"")
If Checkerror("app.Open") = True Then
Exit Sub
End If

d = 0
do while d < 75

prjObject.run
If Checkerror("Project.run") = True Then
Exit Sub
End If

d = d + 1
Loop

prjObject.Close
If Checkerror("Project.Close") = True Then
Exit Sub
End If

End Sub

Function Checkerror(fnName)
Checkerror = False

Dim strmsg
Dim errNum

If Err.Number <> 0 Then
strmsg = "Error #" & Hex(Err.Number) & vbCrLf & "In Function " & fnName & vbCrLf & Err.Description
'MsgBox strmsg
Checkerror = True
End If

End Function
[/pre]

Re: Macro/vbscript breaking down?

Patrick — Thu, 16 Jun 2011 11:26:14 GMT

Hi

My VBS knowledge is rather basic but if I get this right then you're starting 70 EG sessions more or less in parallel.
May be you're just running out of memory and some kind of sleep statement would help.

Not that I really understand what you're doing but may be another approach could be to wrap all your code into a macro and then loop over this macro (just having as first node a "%macro" statement and as last node a "%mend" and then a loop calling the macro and passing the parameter values.

HTH
Patrick

Re: Macro/vbscript breaking down?

Simi — Thu, 23 Jun 2011 08:58:55 GMT

Hi Patrick,

Technically speaking, I am not running them in parallel, but they are executed one after another from my index list. Unfortunately, I am not very familiar with SAS macros, hence bit of a no go for me.

Back to the problem, I may have found a source of my problems. Today, when running a single iteration (as opposed to the whole list), my project designer got stuck on 1 (completely random) query, saying that it was collecting data. Normally, it is only matter of seconds for this particular query, however after 5 minutes EG was still showing 'collecting' without any error messages displayed. I started to check the previous datasets linked to this query, when I got to the very last one preceding this query.

Upon opening I received the following error message: "SAS Enterprice Guide cannot open the data file:"NAME OF THE FILE". Error HRESULT E_Fail has been returned from a call to a COM component".

I was quite surprised that upon failing to open this dataset the EG was still trying to run the query without breaking down (stricktly speaking it had no dataset to run the query for!). And this is exactly the situation, when I run it for the whole list. At some point CPU usage for both tasks drops to 0%, whilst still using the system memory and no error messages show up.

Has anyone encountered similar problem or anyone has suggestion on this?

Thank you,

Simi

Re: Macro/vbscript breaking down?

Patrick — Thu, 23 Jun 2011 09:38:23 GMT

Hi Simi

Thanks for your patience with my ignorance.

What you describe starts to feel like a question for SAS Tech Support.

Below some code to illustrate the macro approach:

/* first node */
%macro loop(parameter);

/* all the nodes in between */
data _null_;
put / 80*'*';
put "Value of parameter is: &parameter";
put 80*'*' /;
run;
/* ********* */

/* last node */
%mend;

data YourList;
var='This Value 1'; output;
var='This Value 2'; output;
var='This Value 3'; output;
run;

data _null_;
set YourList;
call execute ('%loop('||var||')') ;
run;

HTH
Patrick