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
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
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.
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
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?
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 !
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: