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

[cercig]

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 these 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. That's why I am writing under "Base SAS PRogramming" location on this forum. Because I believe that I am not scanning the parameters inside the SAS code correctly.

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

[Kurt_Bremser]

Do a %put "&sysparm" in a test program to see what is handed over as a whole.

Since you already have the separate environment vars SASFilePath, SASFile, SASOutputPath and YearMonth, why not use %sysget to retrieve these separately?

[cercig]

Hi Kurt,

First of all thanks for your tips.

I already tried to save a parameter (for instance &sasoutputpath) like below since log file from batch doesn't show if "sasoutputpath" exists or not. And also because I run the system inside a VBA code.

%LET sasoutputpath = %SCAN(&SYSPARM., 3, '#');
data tryin;
	pathname=&sasoutputpath;
run;

But SAS complained about it expected a string or numeric variable. Because obviously there was no such a variable like sasoutputpath according to SAS.

%sysget seems to be very useful for my case. But how can I use it for multiple variables?  How can I separate them? For example, if the bat file is sending variables like:

%sas% -sysin "%sasfilepath%\%sasfile%" -SYSPARM "%SASFilePath%#%SASFile%#%SASOutputPath%#%YearMonth%" -log "%SASOutputPath%\log\%SASFile%_%date_time%.log" -nosplash -icon -no$syntaxcheck -rsasuser -noprint

[Kurt_Bremser]

As I already said, you need to check first how sysparm is handed over to the SAS system. At the moment, you can't say what happens when the DOS batch job is executed and what actually appears on the commandline so that SAS can see it.

So do a

%put "&sysparm";

and look in the log first before anything else.

Now, regarding %sysget (or the sysget function in a data step)

When you have done

set A=X

in a DOS batch file (or export A=X in a UNIX shell script), and call SAS from that batch file (script), then

%let SYS_A=%sysget(A);

will give you a macro variable that contains X (note no quotes, as the macro language is character only and needs no quotes).

You then have no need to use &sysparm at all.

This method is actually how all our batch jobs are run.

[Kurt_Bremser]

%LET sasoutputpath = %SCAN(&SYSPARM., 3, '#');
data tryin;
	pathname=&sasoutputpath;
run;

In this piece of code, if sasoutputpath was c:\temp\sas, the data step would look like this after the macro var is resolved:

data tryin;
	pathname=c:\temp\sas;
run;

So you get the complaint about the incorrect variable name. Use "&sasoutputpath" instead, to make it a string value.

[RW9]

Sorry, this is going to sound harsh.  Why?  What you have there is a mess.  What are you doing in VBA that it needs to goto a batch file and then run SAS?  SAS has several functions to import data directly from Excel - which is what I assume you are using - which would remove the need for VBA and the batch file - i.e. simpler more robust.  

As for parsing parameters into macro variables, firstly I generally advise against using macro parameters until absolutely needed.  SAS datasets are specifically designed to deal with data, macro language isn't.  Your code will be easier to read, simpler, and more robust if you put parameters into a dataset, and if you really have to, call symput them into macro variables where needed.  This will also help you debug:

data parameters (drop=i);
  array param{4} $ ("sasfilepath","sasfile","sasoutputpath","perdate");
  do i=1 to counw("&syspam.","#");
    parameter=param{i};  
    result=scan("&sysparm.",i,"#");
    output;
  end;
run;

This will give you a dataset with the sysparm split so you can see each string, you could also have another variable for the full string passed in.  

[cercig]

@RW9 Thanks for your reply. I also think that it seems a bit mess there.

Now I realize that I remembered something a bit wrong. Before I tried to get a parameter from an open Excel file by using below (because the sheet name was too long, so only Connect To Excel option could work with long sheet name) but I got error because the file was open.

PROC SQL;
	CONNECT TO EXCEL (PATH="&ExcelFolder\&file" HEADER=NO);
	create table ...
        ...
QUIT;

Now I tried with `proc import` and it seems like it works with open Excel file.

So I want to ask about your solution in anyway. Because in case if I have to work with long sheet names again, then I can't use `proc import`.

I implemented your solution, with do-loop and scan-sysparm, but I still get the same error like "This window is unavailable in line-mode." Do you know what can cause this error? I searched this error on google, and it seems like you get this error when there is something wrong in the SAS code.

[RW9]

So, question 1, why is the sheet name too long (in fact why use Excel in the first place, but hey).  Shorten it, the fact that something allows you to do something, does not necessarily mean its a good thing to do.  As for your error:

http://support.sas.com/kb/44/705.html

I would say somewhere in your code you have something that can only be run interactively see the link.  Could also be in the autoexec.sas.  Post your code as well.

[cercig]

@RW9 unfortunately I don't have the authority not to choose Excel or long sheet name. My responsibility is just to find solutions somwhow.

I also commented out the most SAS lines. I have only 2 command lines left in my SAS, and I still get the same error from batch.

%let Server=ServerPath; 
libname mylib "&Server\ExcelFolder";

I also tried to do that do-loop and scan as you wrote below. 

data parameters (drop=i);
     array param{4} $ ("sasfilepath","sasfile","sasoutputpath","perdate");
     do i=1 to count("&sysparm.","#");
     parameter=param{i};
     result=%scan("&sysparm.",i,"#");

 However, I got an error for the "scan" line like:

 ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: i 
ERROR: Argument 2 to macro function %SCAN is not a number.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, 
a numeric constant, a datetime constant, a missing value, INPUT, PUT.

Then I wrote %put instead of %scan, but it showed an output like:

("",i,"#")

@Kurt_Bremser and Kurt, like you said, I used

%put "&sysparm";

and the log shows only "", so there is nothing as sysparm according to SAS. 

As a result, even if I removed almost everything from the SAS code, i still get the same batch error like "This window is unavailable in line-mode."

[Kurt_Bremser · Accepted solution]

Well, I still suggest as the easiest way to directly use the DOS environment vars with %sysget.

And go at it step-by-step:

Just have a SAS program (c:\temp\test.sas) that does

%let env_a=%sysget(A);

%put env_a="&env_a";

and nothing else.

Then write the batch file as

set A=xxxx

sas -sysin c:\temp\test.sas -log c:\temp\test.log

and look at the log after the run. Once that works, expand from there until you have a complete batch file that works the way you originally intended. Then go to VBA and build that batch file from there.

Everytime something fails, do an output instead of running a program. In UNIX shell scripts, I use the echo command to simply show me the commandline I would have run. I only remove the echo once its generated the way I want it.

[cercig]

@Kurt_Bremser thanks Kurt, it really worked. The interesting point is that I don't get that error window when I run the bat file directly. Now I realized that I get that error window when I run the batch script inside the VBA code, even if the process works without problem.

It is a bit interesting, because on SAS page, it says that error page appears if some code line is not correct in SAS when running the batch code. But as I said, when I click and run the batch file directly, that window doesn't appear.

[RW9]

You will need to speak with your IT desk, who installed the SAS system.  It could be an issue in the autoexec.sas.

data parameters (drop=i);
     array param{4} $ ("sasfilepath","sasfile","sasoutputpath","perdate");
     do i=1 to count("&sysparm.","#");
     parameter=param{i};
     result=%scan("&sysparm.",i,"#");

This isn't the code I posted, there is no need to use %scan .

This in the log:

("",i,"#")

Shows you that nothing is present in &sysparm.  So the problem is not in the SAS code but in your batch file or VBA, which is outside the remit of a SAS forum.