CODEWITHSUNDEEP
vbaexcelbatch-filesasbatch-processing
user3714330
user3714330

Reputation: 689

SAS-code can't scan the parameters from Bat file which came from VBA

I am trying to run my SAS code from my VBA-code by using bat file (batch run). So the system should work like this: First VBA-code sends some parameters to bat file, then the bat file sends this parameters into my SAS code. Then the bat file executes my SAS code.

However, obviously the Bat file can not send the parameters into my SAS-code, they don't exist according to the SAS-system. When I run my VBA-code (which runs also the Bat file), I get an error message like

This window is unavailable in line-mode.

The VBA code is like this:

Public Sub RunSASCodeViaBatFile()
    ...
    Parameter settings here 
    ...

    Dim cmdString As String: cmdString = batPath & batFile & " " & SASFilePath & " " & SASFile & " " & SASOutputPath & " " & YearMonth
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 2
    wsh.Run cmdString, windowStyle, waitOnReturn
End Sub

The commands in batch file looks like this:

Some General SAS settings in bat file like:
set sas="C:\Program Files\SAS\SASFoundation\9.2(32-bit)\sas.exe" -autoexec
....

Settings for the input parameters
set SASFilePath=%1
set SASFile=%2
set SASOutputPath=%3
set YearMonth=%4

if %debug%==1 (
   echo %thisscript%: Debug: Execute SAS program
   echo %thisscript%: Debug: sas=%sas%
)   
%sas% -sysin "%sasfilepath%\%sasfile%" -SYSPARM "%SASFilePath%#%SASFile%#%SASOutputPath%#%YearMonth%" -log "%SASOutputPath%\log\%SASFile%_%date_time%.log" -nosplash -icon -no$syntaxcheck -rsasuser -noprint
if %debug%==1 echo %thisscript%: Debug: errorlevel=%errorlevel%
if %errorlevel% neq 0 (
  echo %thisscript%: Error: Execution of SAS program returned code %errorlevel%
  exit /b %errorlevel%
)

And finally the SAS code scans (reads in) the batch parameters like below. I am trying to read batch parameters with SYSPARM command:

    %LET sasfilepath = %SCAN(&SYSPARM, 1, '#');
    %LET sasfile = %SCAN(&SYSPARM, 2, '#');
    %LET sasoutputpath = %SCAN(&SYSPARM, 3, '#');
    %LET perdate = %SCAN(&SYSPARM, 4, '#');

I think the problem happens when the SAS code try to scan the parameters batPath, batfile, SASFilePath, SASFile, SASOutputPath, YearMonth. Because the batch file can use these parameters correctly to create log names or to find paths with these parameters.

So anyone has any idea about why my SAS can't scan the bat-parameters?

Upvotes: 0

Views: 497

Answers (1)

Quentin
Quentin

Reputation: 6378

From this note, looks like your batch SAS job is trying to do something that requires an interactive session, such as using DM commands. http://support.sas.com/kb/44/705.html

The %let statements you showed should not cause this problem. I would look elsewhere in the SAS code.

Actually first I would remove the -icon option on the SAS invocation. That option probably expects an interactive session with windows to minimize.

If you don't see a suspect in your SAS code after that, try brute force debugging. Comment out all of your SAS code and see if it works. Then uncomment half of it and see if it breaks. Repeat.

If by small chance the %let statements do throw this error, please show the parameter values you are passing in SYSPARM.

Upvotes: 1

Related Questions