topic Re: Assign prompts value in a vbs for SAS Automation in SAS Enterprise Guide

Assign prompts value in a vbs for SAS Automation

KBou — Thu, 17 Sep 2020 17:46:58 GMT

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

Re: Assign prompts value in a vbs for SAS Automation

pink_poodle — Wed, 16 Sep 2020 15:30:43 GMT

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

Re: Assign prompts value in a vbs for SAS Automation

ChrisHemedinger — Thu, 17 Sep 2020 13:09:08 GMT

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.

Re: Assign prompts value in a vbs for SAS Automation

KBou — Thu, 17 Sep 2020 18:57:59 GMT

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

Re: Assign prompts value in a vbs for SAS Automation

ChrisHemedinger — Thu, 17 Sep 2020 18:01:42 GMT

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?

Re: Assign prompts value in a vbs for SAS Automation

KBou — Thu, 17 Sep 2020 18:56:09 GMT

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 !

Re: Assign prompts value in a vbs for SAS Automation

TomKari — Thu, 17 Sep 2020 20:04:29 GMT

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