Assign prompts value in a vbs for SAS Automation

[KBou]

Hi, 

I have a simple code as an example. The program has 1 prompt that opens before running. It is compare which is assigned 0 or 1 so the code executes or not.

LIBNAME valid BASE "path" ;

%Let compare_test = &compare;

data valid.test;
if &compare_test = 1 then do x = 1 to 10;
y = x*x;
output;
end;
run;

Is there such a way I could insert something in the VBS file to assign the value for &compare ? Right now my vbs is running, but only with default parameters. Ultimately, I want to transpose this kind of treatement on a much bigger scale for another program with about a dozen prompts to define before running. Any help would be very appreciated !

Cheers,

Edit :

Ty for advice. I've progressed quite a bit, but not there still.

I can now assign value to my prompts manually via the object parameters. I'm now trying via an excel sheet, and everytime i try it does fail.

Here's a part of my VBS. Compare and Date are my two prompts that opens when running the SAS program. If i enter the values manually, it does work. Otherwise if I try to read the excel value it fails. Any insight on that ?

SAS program is already opened at this point.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("path")
Set ws = objWorkbook.Sheets("Invites")
rowcount = ws.usedrange.rows.count



Set oparameters = prjobject.Parameters ' Définition des paramètres pour l'exécution du programme SAS

For Each oparameter In oparameters ' Assigner une valeur aux paramètres pertinents

vb_date = ws.cells(y,4)
vb_compare= ws.cells(y,5)

If oparameter.Name = "Compare" Then oparameter.Value = vb_compare ' 1
If oparameter.Name = "Date" Then oparameter.Value = vb_date ' "01/01/2020"

Next

[pink_poodle]

Please check out this post:
https://communities.sas.com/t5/SAS-Programming/Calling-SAS-from-VBScript-Setting-Macro-Variables/td-...

[ChrisHemedinger]

In the EG automation model, "Prompts" are ProjectParameter objects, and you get that list from the Project object.  

Sometimes it might be easier to just set macro variables and/or environment values for your SAS programs to pick up. Use the CodeCollection to add a new program with macro var assignments and run it, like so:

' add a new code object to the Project
Set sasProgram = Project.CodeCollection.Add
 
 Set the server (by Name) and text for the code
sasProgram.Server = "SASApp"

' Create the SAS program to run, assign macro vars
sasProgram.Text = "%let param = value;"
 
' Run the code
sasProgram.Run

But if your prompts are encoded in tasks, like the Query Builder, then the Project.Parameters property will get you the list.  You can then set the Value attribute on each ProjectParameter object.

[KBou · Accepted solution]

Ty for advice. I've progressed quite a bit, but not there still.

I can now assign value to my prompts manually via the object parameters. I'm now trying via an excel sheet, and everytime i try it does fail.

Here's a part of my VBS. Compare and Date are my two prompts that opens when running the SAS program. If i enter the values manually, it does work. Otherwise if I try to read the excel value it fails. Any insight on that ?

SAS program is already opened at this point.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("path")
Set ws = objWorkbook.Sheets("Invites")
rowcount = ws.usedrange.rows.count



Set oparameters = prjobject.Parameters ' Définition des paramètres pour l'exécution du programme SAS

For Each oparameter In oparameters ' Assigner une valeur aux paramètres pertinents

vb_date = ws.cells(y,4)
vb_compare= ws.cells(y,5)

If oparameter.Name = "Compare" Then oparameter.Value = vb_compare ' 1
If oparameter.Name = "Date" Then oparameter.Value = vb_date ' "01/01/2020"

Next

Edit :

It all worked out finally. The cells were formatted on date format on my Excel. Switched it to standard and worked on the first try.

Good to know if anyone has the same issue

[ChrisHemedinger]

I'd say make sure the Value you assign is a text value -- if you pull the value from an Excel object, it might not interpret as text.  Probably a way to get the text representation of the Excel cell value though, right?

[KBou]

Thank you sir, it gave me the good insight to find myself potnetial error sources.

I've put my excel sheet cells on date formats. Once I switched it to standard, it made my code work ! 

[TomKari]

If you ever need to convert an Excel number to a character field, the "text" function does this. For example, if A1 is a number, use:

=TEXT(A1,"00")

Tom