SAS Enterprise Guide is best known as an interactive interface to SAS, but did you know that you can use it to run batch-style programs as well?
SAS Enterprise Guide has always offered an automation object model, which allows you to use scripting languages (such as VBScript or Windows PowerShell) to call the application's features from within other processes. (See Microsoft's "scripting center" for quick access to All Things Scripting for your Windows PC.)
It's this automation model that allows you to schedule a project or process flow to run unattended. When you select File->Schedule Project, SAS Enterprise Guide creates a default script file (using VBScript) and adds a scheduled task to the Windows scheduler. That's convenient, but you don't have to use the Schedule feature to take advantage of automation. You can write your own scripts to automatically run tasks, projects, or SAS programs at any time.
The automation model even allows you to create a new project on-the-fly, and add SAS programs into the project and run them. This way, you don't need to create a predefined SAS Enterprise Guide project (EGP file) in order to run SAS programs in your environment. The automation model uses the SAS Enterprise Guide application to provide the "plumbing" to access your SAS metadata environment and SAS workspace servers and run any SAS job...even when you don't have SAS on your local PC.
Note for Windows x64 users: SAS Enterprise Guide is a 32-bit application. In order to run scripts properly on a 64-bit operating system, be sure to use the 32-bit version of whatever scripting runtime is needed. For example, to run a VBScript file, use a command such as:
c:\Windows\SysWOW64\cscript.exe c:\projects\AutomationNewProgram.vbs
You can learn more about the automation API by perusing the reference documentation (the link is for the 4.2 version, but the 4.3 version is virtually unchanged). You can also learn from the examples provided within this SAS sample.
UPDATE 10Nov2012: Since this original post, I've written a conference paper and several more examples. See the details about the paper and examples at Doing more with SAS Enterprise Guide automation.
I've included an additional useful sample here. This VBScript program allows you to connect to your SAS workspace server and run any program code that you want, and then save the output log and listing to your local PC file system. To use the example, save it to a .VBS file on the machine where SAS Enterprise Guide is installed. Of course you'll need to change the names of the active profile and SAS server to suit your environment. Then you can run the example using the proper version of cscript.exe.
Option Explicit ' Forces us to declare all variables Dim app ' application Dim project ' Project object Dim sasProgram ' Code object (SAS program) Dim n ' counter ' Use SASEGObjectModel.Application.4.2 for EG 4.2 Set app = CreateObject("SASEGObjectModel.Application.4.3") ' Set to your metadata profile name, or "Null Provider" for just Local server app.SetActiveProfile("My Server") ' Create a new project Set project = app.New ' add a new code object to the project Set sasProgram = project.CodeCollection.Add ' set the results types, overriding app defaults sasProgram.UseApplicationOptions = False sasProgram.GenListing = True sasProgram.GenSasReport = False ' Set the server (by Name) and text for the code sasProgram.Server = "SASApp" sasProgram.Text = "proc means data=sashelp.cars; run;" ' Run the code sasProgram.Run ' Save the log file to LOCAL disk sasProgram.Log.SaveAs "c:\temp\outputAuto.log" ' Filter through the results and save just the LISTING type For n=0 to (sasProgram.Results.Count -1) ' Listing type is 7 If sasProgram.Results.Item(n).Type = 7 Then ' Save the listing file to LOCAL disk sasProgram.Results.Item(n).SaveAs "c:\temp\outputAuto.lst" End If Next app.Quit |
178 Comments
Very interesting example, I know several people who want to do something like this. It works in part for me. The SAS log is generated but no other output. If I add a line "msgbox project.Results.Count" then the message box reports a 0. Any idea how to fix this? I'm using SAS 9.2, EG 4.3 with a remote server.
Best regards,
John Hendrickx
Hi Chris,
Great post! Any chance you can post the link differently to the automation API documentation? My laptop is not able to grab the file for some reason.I would be interested in reading it.
Thank you!
Wendy
Hi Wendy, you might be running into the issue with CHM file content being "locked" when you download it from the web? If you can get the ZIP file and extract the CHM file, right-click on the CHM file and select Properties, then Security, and "unblock" it so that you can read it.
If that's not the issue, post back.
Pingback: Running Windows PowerShell Scripts - The SAS Dummy
thanks for sharing. i am stuck on submitting a specific program (or Code Object, which falls under a CodeCollection). it is also of importance that the code object is NOT in the project. basically, i would like to run a stand-alone .sas program that is not in any project (.egp) file.
my gameplan is to write code to accomplish the following:
1. open a new SAS EG app
2. add a new project.
3. open/add a .sas program from the filesystem 4. run that program 5. close the program and project without saving either
i would ideally have liked to do the following, but i THINK i need to have a project first:
1. open a new SAS EG app
2. open/add a .sas program from the filesystem 3. run that program 4. close app
also, if you know of documentation that is less terse (and includes more examples and better description of how to use these objects in conjunction with each other), that would be very helpful.
thanks in advance
You can accomplish this by adapting the technique in the example. But instead of having the SAS program in the script code, use the VBScript File object model to open and read the contents of the SAS program that you want to use (from your local PC) and set that to be the Text value of the Code object.
There are some examples of using VBScript to read text files here.
Awesome job !
Any chance to get this code translate in powershell ?
beg of you :)
Here is a PowerShell example for automating SAS Enterprise Guide.
Pingback: Using Windows PowerShell to report on your SAS Enterprise Guide project files - The SAS Dummy
Hi Chris,
Is there a way to pass macro variables to the SAS program?
Thanks,
Tim
Hi Tim,
The easiest method would be to precede the SAS program in the VB Script with a series of %let statements. That works if you don't mind "hard-coding" the macro variables into the script.
If you want your script to be resusable in multiple scenarios, and dynamically set macro variables, you can modify the script to accept command-line arguments. Then you can use those argument values in your scripting code to build the %LET statements at the front of your SAS program.
You can find more information about using command-line arguments with VB Script here, or more details here.
It is a good thing that we have this functionality in EGuide but I can see many pitfalls for the SAS administrator.
The users will use their PC to automate task, it is preferable to use the server and dedicated tools to do that job (cron, control-M ...)
There is a risk of overload in the server : each time the users will launch a VBS a connection will be made and a process will be launched. Imagine that you have 10 users launching 20 VBS each day.
So administrators think twice if you want to offer this task to your users.
Xavier,
You make some good points. Scripting SAS Enterprise Guide to run your jobs in "batch" is convenient for automating certain tasks and running some jobs unattended, and it's an essential capability for smaller organizations that might lack IT support. But it's not a substitute for an enterprise scheduling platform that can be centrally managed. The IT staff members who support SAS users can also monitor the level of activity each user creates. Sometimes a policy with usage guidelines, combined with a facility for monitoring and "correcting" usage patterns, can be the most effective management tool.
Hi Chris,
I've got it up and running here too, I think it's great! But I was wondering if you know of a way to kill a batch job (under Windows)?
John,
Glad to hear it's working well for you.
I'm a big fan of the Sysinternals tools, now available for download from Microsoft. PSKILL is a very useful command that can kill processes on your local and remote Windows boxes; you can download it here.
Hi John,
I would be very interested in how you had this code work for you because I have exactly the same issue you reported in your first post, namely that the project.Results.Count gives me 0.
Thanks,
Best regards,
Bernard
Pingback: Review your metadata profiles using SAS Enterprise Guide automation - The SAS Dummy
Hi,
Assigning a library missing in above code. Please explain me how to assign any library using macro.
PV, within this example you could include a LIBNAME statement in the Text property of the SAS code that is submitted. I plan to provide additional examples of automating SAS Enterprise Guide using script very soon. It's a topic that I'm preparing for SAS Global Forum 2012.
Pingback: Doing more with SAS Enterprise Guide automation - The SAS Dummy
I embedded this into a Microsoft Excel VBA application and it works for users with 32-bit machines. I have a user with a 64-bit machine who can't get past this statement.
Set app = CreateObject("SASEGObjectModel.Application.4.3")
What do I have to do to get it to work for him?
Tim, if the user has 64-bit MS Office, this won't work. The 64-bit Office process cannot invoke the 32-bit EG modules.
It's not 64-bit MS Windows - that can work fine using the 32-bit subsystem on the OS. It's the fact that MS Office is a 64-bit process.
How to make it work:
Replace 64-bit MS Office with 32-bit MS Office. Do you need the 64-bit version of Office? Most people do not.
Use EG 5.1, which offers a 64-bit version.
Hi Chris,
I can't get past the same line Tim had issues with above.
I have a 32bit version of XL & eGuide 7.1 (32bit).
Any quick wins you might be able to point out for me?
PS. It's also a Windows 7 Pro, 32 bit os.
Thanks
Steve, does the script work in a standalone VBS with CSCRIPT.exe (32-bit version)? And make sure you're invoking the proper version of SEGuide ("SASEGObjectModel.Application.7.1").
Hi, I am having an issue when trying to schedule SAS project using the scheduler. After selecting File - schedule project and setting the parameters, the task is not showing up in Windows task scheduler. We have tried creating our own task in scheduler, but it will only run with the option where a user must be logged on. It will not run when selecting the option for a user not logged on. We are using SAs EG 4.3 and SAS v9.2. Any suggestions?
Carolyn, this might be due to a policy setting in Windows that won't allow a scheduled task when the user is not logged in. (When permitted, such a task must be set up with "stored" credentials, so your IT staff might have disabled this.) You might need to petition for a change from IT, or look into a 3rd-party Windows task scheduler that is more permissive/flexible.
Thanks for the reply. I was able to get a .bat file pointing to a .sas file to run with the 'user logged off'option on the Windows 7 machine. (Justtried this today). And we are able to use this option on our Windows XP machine. So wondering why the .vbs file won't execute? Any other ideas are welcome!
The .vbs file works when you're logged in, even via the scheduler? Try making sure the command to run the script includes the complete path to cscript.exe (ex: %windir%\System32\cscript.exe c:\projects\MyJob.vbs).
Hi, just getting back to this issue. We are not using cscript, we are using wscript. Does that matter? C:\Windows\System32\WSCRIPT.EXE The vb script runs if the user is logged on. The task does not automatically show up in the Windows Scheduler, we must add it. Any other suggestions are appreciated! Carolyn
Carolyn, yes, CSCRIPT might make a difference. If you have any Echo statements that display a message, WSCRIPT pops a window up, whereas CSCRIPT can write to the console or "stdout". See this topic from a Microsoft blog for more details.
Having the window pop up might get in the way of a successful "unattended" run.
Hello,
We have a similar issue with SAS 9.2 and SAS 9.3. The project don't run if the user is not logged. The same project in the same machine with SAS 8.2 works fine even without user logged in. Maybe a SAS misconfiguration?
You might need to store your credentials for retrieval while you're not logged on, so that EG can connect to the SAS metadata server and workspace. See the SAS Note 30917 for details.
Hi Chris,
I am trying to find an answer to the following question:
I need to run 10,000 simulations each on 20 different variables and because of the size of the dataset, each variable is taking ~8 hours to complete. I am now trying to run the simulations for each variable in parallel and am looking for a way to do this in SAS EG. Appreciate any pointers!
-Mark
Mark,
Here are some great tips from Rick Wicklin about how to make your simulation faster. Rick also offers many other articles about simulation.
Thanks Chris!
I did browse through the link and found it helpful. I also poste da query on that page, but haven't got a response yet. Can you please provide some inputs on the following query:
I want SAS EG to run simulations for each of the variables in parallel which would help me complete the process in ~8 hours. Can you please confirm whether this would be possible and how?
Thanks,
-Mark
Mark,
There are a few ways to run things in parallel with EG.
- Set up one or more separate workspace server definitions, and pin different parts of your process to different servers.
- Use the new "parallel processing" feature of EG 5.1 (go to File->Project Properties to enable this on the project level)
- Use multiple EG instances to run processes together, as long as they don't have any dependencies on each other. Each EG instances will have its own connection to a separate SAS session for working.
Chris
Hi Chris,
I have a SAS EG in my PC interacting with a remote server. I am trying to understand how the scheduling works offline, that is when I logged off my PC.
1. When I schedule the project it runs fine even when I am offiline - Where is the scheduling information stored and how does it get triggered?
2. When I simply submit the code from SAS EG directly, why is the process gets killed when I go offline. Should not the process run in server once I submit the code, even I am offline.
3. Is there any SAS EG component in SAS server that stores the scheduling information and runs only for scheduled projects? because it is logical to think if scheduled tasks runs offline, why not directly submitted tasks, given the server is remote.
Kathiravan,
When you start a process (like EG) and then LOG OFF your machine, all of the processes that are running under your identity are closed. In the case of EG, this terminates your connection with the SAS session, which then "abandons" your running job.
When you schedule a task in Windows Scheduler and mark it to run "when logged off", the task scheduler logs in for you and launches the process.
I'm guessing that your goal is to submit a job in EG, close EG and have the SAS process keep running to completion. Unfortunately, EG doesn't support that "disconnect and reconnect" mode.
Thanks Chris,
This is of a great help. So, Scheduler logs in my PC for me and intiates the process.
Is there a way to submit my code(not an EG project, just SAS code) to the remote server (windows NT), from my PC(Windows XP), through batch mode so that I can just submit the code and let it run in server and log off my PC. I have done this in UNIX. I have no clue about windows. The scripts I found in support and other sites works only in local. Could you share the script for doing the same in remote server, if you any?
I don't know of a script that will do exactly that. When I have such a need, I usually create a cron job on the UNIX system directly, and have it run a shell script that runs the SAS programs that I need.
The pieces are there though: SAS Workspace Servers can be configured to support "client reconnection", and client programs can take advantage of this. However, this feature is not built into SAS Enterprise Guide.
Hi Chris,
I'm trying to implement this and I am consistently getting the following error:
SAS.EG.SDS.Metadata.MetadataException: Request for login credentials canceled by user
despite never being given control, nor the opportunity to enter my credentials. If I open SAS EG and manually set "No Active Profile", it works the first time, prompting me for my credentials but on subsequent attempts, I need to set "No Active Profile" again.
Any ideas?
George,
I've seen that before, and I think something like that was addressed in a hotfix. You didn't mention the EG version, but check to make sure that you have the most recent fixes from tech support.
If you continue to have troubles, contact SAS Technical Support and they'll be glad to help you or track the issue.
Thanks Chris, we're using SAS EG 4.3 (4.3.0.11123). I'll ask our internal tech support team and give them your advice. Cheers!
Hi Chris,
I am a newbie into SAS and struggling to find a solution for requirement. What I need to do is, to run a SAS eguide code on windows command line with two parameters. Parameter values are "LibraryName" and "TableName". Then, eguide code should export the table into a excel file in the local drive. I greatly appreciate if you could send me the eguide code, vbscript and how I need to run it in the command line.
Thank you.
Regards,
Sam
Saman, I recently wrote a conference paper on this topic, and I developed several examples that use different scripting technologies. Check out this post for a description of the paper, and links to the paper and the examples.
Hi Chris,
Thanks for your reply. I have already read the same on another paper (http://support.sas.com/resources/papers/proceedings12/298-2012.pdf) from you. However I had errors when tried the code with EG 4.1.
As I mentioned, I have started looking at SAS a week ago for the first time in my life. So I still dont know the SAS code to export library data into a excel. I really appreciate if you can post the code bit for that.
Do you think I can still use the same vb code with EG 4.1?
Thanks in advance.
Regards,
Sam
Further to to previous message, I am neither SAS coder nor VB coder. However I just investigated bit more and found some of the COM objects being used in your code are not available with SAS EG 4.1. Unfortunately I cannot use a later version as the remote SAS/SHARE I am connecting into is version 9.1 and link http://support.sas.com/kb/43/871.html explain it. Appreciate if you can post the relevant code for 4.1
Can you please advise.
Regards,
Sam
Hi Chris,
I played with your code and got it run for EG 4.1.
Thanks a lot.
This was really helpful and much appreciated.
Regards,
Sam
Sam, I'm glad that you got it working!
Hi Chris,
Thanks once again for your script and it worked well on most of the SAS data sets. However, when data set exceeds excel's max records(1,048,576), code ends up with an error. Then I simply changed the code save it into a .csv file (changed only .xls into .csv in the script). Then script ran fine and I got the csv file.
However this has some issues.
If you take a following row of a data as an example
when you save this as csv with excel or other tools, the actual data get save as,
But with your code it saves as,
Which means 3 columns have been converted into 4 columns. If you consider a big data set this become troublesome.
Is there any way to to modify your code to get this issue sorted. I really appreciate if you can post an updated code to solve this problem.
Thanks in advance.
Regards,
Sam
Sam, it looks like the nest quotes are a problem and are not properly escaped in the output. I can't find this as a known documented problem, but I'm guessing it's fixed in later releases of EG. Can you worked with Microsoft Access files? If you change your SaveAs to write to a local MDB file, you might be able to get around the size limitation and the CSV formatting problem.
Otherwise, I advise working with SAS Tech Support to see if there is a resolution.
Hi Chris,
Thanks for your quick response and much appreciated!
It seemed much more better than CSV. It solves escape and data overflow issues as you said. Only thing which I noticed is bit slower compared to CSV generation, but I am more than happy with this solution as it reduces some of the overheads down the stream of data integration process.
I have another question for you.
I am running this script as a bath, passing "Data set name" (inside a library "LPG") as parameter. Would it be possible to amend the local LPG.mdb file to add data sets as tables in the same LPG.mdb file rather than creating separate .mdb file for each data sets?
Thanks in advance.
Regards,
Sam
Sam,
I'm glad we're making progress.
EG doesn't support appending/amending an existing MDB file. The Save As (or Export) capability always creates a new file. With some creative VBScript code, you could probably create a "holder" MDB file for each table, and then append that table to an existing MDB file, so that at the end of the process you have one MDB file with everything. All of that MDB "post processing" would need to occur in your script code. I'm sure that there are plenty of VBScript examples for manipulating Microsoft Access databases.
Hi Chris,
Thanks for your quick reply once again as you do always. Much appreciated it!
I just thought to check with you whether there is a way to do it with SASEGObjectModel.
However I can look for scripting option as you explained and alternatively I can have one master.mdb where it has all the other tables as link tables.
Regards,
Sam
Hi Chris,
When I schedule my project (no issues there), the OLAP connectivity is lost when running in batch. It always prompts me to re-connect to the MSOLAP server, then it runs fine. Is there a way to hard code the connectivity either in the VBS script or in the project itself.
Thanks, Chris Paskins
Chris, in Tools->Options->Security there is an option to "cache" credentials in the project (or user profile). I don't know if that will affect OLAP credentials for the Microsoft SQL Server (or Analysis Services) provider, but that's something to try.
Hi Chris,
great article! I'm trying to accomplish this in my environment.Unfortunately with no succes.
I have no permission to use cscript or wscript. That doesn't matter to me because i would like to use vba in excel like Tim Walters.
Unfortunately i don't have the SASEGObjectModel.Application.4.1 object (or other version) available in excel, so the statement 'Set app = CreateObject("SASEGObjectModel.Application.4.1") ' gives an error.
I read that this object shout be registered in the registry. Unfortunately (again) i have no permission te read my registry. But i do know that my sas application is running in a citrix environment , so it is not installed on my local pc. That must be the reason that SASEGObjectModel.Application.4.1 object is not registered.
But then, you state in your article that : "run any SAS job...even when you don't have SAS on your local PC."
So, my question is; how can i get access to the SASEGObjectModel.Application.4.1 object in vba when i don't have SAS installed on my local machine.
By the way, I'm using SAS EPG 4.1 .
Thanks in advance.
Bart Stokkermans
Bart,
If you're running EG on a Citrix box, then that's where your EG installation resides and where the scripting objects are registered. You may be able to use automation within an application on Citrix (such as a Citrix instance of MS Excel, if you have one), but not on your local PC. Your local PC doesn't have the necessary bits to invoke SAS Enterprise Guide.
The object model (SASEGObjectModel.Application) is part of the SAS Enterprise Guide application. The only way you would have access to it is to have SAS Enterprise Guide installed on the machine where you're running the script. My statement that you quoted: "run any SAS job...even when you don't have SAS on your local PC" -- that assumes that you DO have SAS Enterprise Guide (a lightweight client compared to SAS) on the local PC.
Hi Chris,
thanks a lot for your very quick answer.
I will now ask system administration for a local install of SAS EPG or a citrix Excel with a citrix EPG installation.
But I don't think that either is possible.
Thanks a lot anyway.
Even with a local SAS EG (if you get one), that local instance might not be able to reach the remote SAS environment. It depends on your network configuration. Sometimes Citrix is used to encapsulate the SAS environment in a network zone that isn't directly accessible by end-user desktop machines.
Good luck!
Hi Chris,
I am a newbie into SAS. I have started looking at SAS from past couple of days. Can you please tell me on how to retrieve the ERRORLEVEL codes after the run command. My requirement is to automate the SAS programs running through vbs. I need the Error codes for checing the status of the job.
Thanks!
S
The SAS log provides the most detail about how your program ran and whether there were any errors encountered. Ideally, you should parse through the log to ensure that all steps ran without ERROR. You could, at the most simple level, look for the phrase "ERROR:" at the start of each line for an indication.
Traditional return codes such as SYSERR aren't reliable for this case, as one step may fail and trigger the SYSERR code, but a subsequent step in the same program might succeed and thus clear the flag. It's best to define what "success" means (no ERROR lines, expected data created, etc.) and test for that.
Hi Chris,
I've been reading this post as well as other ones you've done and for some reason I cannot get past this:
Set app = CreateObject("SASEGObjectModel.Application.5.1")
I'm on 64bit Windows but specifically using the 32 bit cscript.exe file to execute the .vbs file. MS Office is NOT installed on the system (I read an earlier post/response about that) ... could that be causing an issue? How can I tell if the SASEGObjectModel is even available for use on the system and registered with Windows?
Thanks!
Daniel, see if you can run this from a command prompt:
Expected result:
HKEY_CLASSES_ROOT\SASEGObjectModel.Application.5.1\CLSID (Default) REG_SZ {B675F31D-F673-4619-B3FF-876FE697C8F6}Then run:
reg query HKCR\Wow6432Node\CLSID\{B675F31D-F673-4619-B3FF-876FE697C8F6}Expected result:
HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{B675F31D-F673-4619-B3FF-876FE697C8F6} (Default) REG_SZ SAS.EG.Scripting.Application HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{B675F31D-F673-4619-B3FF-876FE697C8F6}\Implemented Categories HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{B675F31D-F673-4619-B3FF-876FE697C8F6}\InprocServer32 HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{B675F31D-F673-4619-B3FF-876FE697C8F6}\ProgIdNote the use of Wow6432Node, indicating the 32-bit portion of the Windows registry.
If these don't yield the results I've mentioned, you might need to re-register the automation component. Usually "SEGuide.exe /register" will do it, but it might require more steps that you can get from SAS tech support.
Chris,
I get the same exact results as your example output when I run each of the commands. I'll reach out to our SAS Admins here locally to see if re-registering is required. We use EGuide on a remote server ... which just dawned on me might have been useful information before ... so any configuration has to be done by somebody else.
Thank you for the quick response ... much appreciated.
Narrow your test down to a simple script:
' force declaration of variables in VB Script Option Explicit Dim Application On Error Resume Next Set Application = WScript.CreateObject("SASEGObjectModel.Application.5.1") If Err.Number <> 0 Then WScript.Echo "Error: " & Err.Description Else WScript.Echo Application.Name & ", Version: " & Application.Version Application.Quit End IfHi Chris,
I am having another issue with regards to automation. Hope you might be able to help me as you did always.
Issue what I have now is when I try to run the same vb script in my local machine by login as a different OS user it pops up the user credential (SAS share server connection) screen . How can I login bit plug into the script.
My vb script - ExtractData.vbs
------------------------------------------------------------------------------------------------
'Option Explicit ' Forces us to declare all variables
Set args = Wscript.Arguments
Wscript.Echo args(0)
Wscript.Echo args(1)
Dim Application ' Application
Dim Project ' Project object
Dim sasProgram ' Code object (SAS program)
Dim n ' counter
Set Application = CreateObject("SASEGObjectModel.Application.4")
Set Project = Application.New
Set sasProgram = Project.CodeCollection.Add
' set the results types, overriding Application defaults
sasProgram.UseApplicationOptions = False
sasProgram.GenListing = True
sasProgram.GenSasReport = False
' Set the server (by Name) and text for the code
sasProgram.Server = "SASMain"
' Create the SAS program to run
sasProgram.Text = "data work." & args(1) &"; set " & args(0) & "." & args(1) & "; run;"
'sasProgram.Text = sasProgram.Text & " proc means; run;"
' Run the code
sasProgram.Run
' Save the log file to LOCAL disk
sasProgram.Log.SaveAs _
getCurrentDirectory & "\" & WScript.ScriptName & "_log.log"
' Save all output data as local Excel files
For n=0 to (sasProgram.OutputDatasets.Count -1)
Dim dataName
dataName = sasProgram.OutputDatasets.Item(n).Name
sasProgram.OutputDatasets.Item(n).SaveAs _
"D:\projects\UAC_Integration\Data\" & dataName & ".mdb"
Next
Application.Quit
' function to fetch the current directory
Function getCurrentDirectory
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
getCurrentDirectory = _
oFSO.GetParentFolderName(WScript.ScriptFullName)
End Function
------------------------------------------------------------------------------------------------
How I run it in command prompt > c:\Windows\SysWOW64\cscript.exe D:\projects\UAC_integration\ExtractData.vbs LIB1 DATASET1
This downloads the data set "DATASET1" from library "LIB1 " as DATASET1.mdb
Please advice.
Regards,
Saman
Saman, you may need to provide a "credentials.xml" file to allow the other user's credentials to be used for the job. See SAS Note 30917.
Hi Chris,
Thanks! I tested it and it works fine with a new user as explained in the link.
Now I am testing this with a windows service (Not windows schedule job), This windows service (OPMN instance) executes the vb script and still have the same problem. If I check the properties of this windows service, in the log on tab it says "Log on as Local system Account". How can I get this sorted. please advice.
Thank you!
Regards,
Saman
What I meant there is no " %appdata%\SAS\Enterprise Guide\4 " folder for this "local system account" to copy credentials.xml file.
Regards,
Saman
Saman,
You might have to experiment with a test script to find the correct placement for this file.
Dim objUser Set objUser = CreateObject("WScript.Network") WScript.Echo "User name is " & objUser.UserName WScript.Echo "Computer name is " & objUser.ComputerName WScript.Echo "Domain name is " & objUser.UserDomain WScript.Echo Dim wshShell Set wshShell = CreateObject( "WScript.Shell" ) WScript.Echo " %APPDATA% expands to: " & wshShell.ExpandEnvironmentStrings( "%APPDATA%" ) Dim wshFile Set wshFile = CreateObject("Scripting.FileSystemObject") If wshFile.FileExists(wshShell.ExpandEnvironmentStrings("%APPDATA%") & "\SAS\Enterprise Guide\4") Then WScript.Echo "Credentials file exists" Else WScript.Echo "Credentials file DOES NOT exist" End IfSchedule this script in the Windows Task scheduler with a command that redirects the output:
Hi Chris,
I am writing to you again after 2 years. With your great help last time my data integration job which pulls data from SAS/SHARE sever has been running OK until they upgraded SAS sever from 9.1 to 9.4 (a remote server controlled by another org). Previous EG version which I used was 4.1 and now I installed EG 6.1 for the newer SAS sever. I changed my vb script accordingly and now getting an error which says "No columns defined in the originating data source" as below.
Thanks in advance! Your help on this matter is highly appreciated!
C:\Users\mqx802820>C:\Projects\UAC_Integration\ExtractUacData.vbs PG13 COURSE
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
C:\Projects\UAC_Integration\ExtractUacData.vbs(42, 3) SASEGScripting: Failed to save as D:\projects\UAC_Integration\Data
\COURSE.mdb. Reason: Export job
No columns defined in the originating data source
SAS.EG.ProjectElements
Void Export(SAS.EG.ProjectElements.ProjectCollection, SAS.Shared.ICanBeInputData, SAS.Shared.DNADecoder, Boolean, System
.String)
at SAS.EG.ProjectElements.DataExportHelper.Export(ProjectCollection project, ICanBeInputData sourceData, DNADecoder r
esolvedPathDecoder, Boolean useLabelsAsColumnNames, String taskLabel)
at SAS.EG.ProjectElements.DataExportHelper.Export(ProjectCollection project, ICanBeInputData sourceData, SaveAsPath s
ap, Boolean useLabelsAsColumnNames)
at SAS.EG.Scripting.Application.SaveAs(Element element, SaveAsPath sap)
Regards,
Saman
Hi Chris,
In addition previous post, I can find below contents in the log.
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Code';
4 %LET _CLIENTPROJECTPATH='';
5 %LET _CLIENTPROJECTNAME='';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 ODS LISTING GPATH=&sasworklocation;
12
13 GOPTIONS ACCESSIBLE;
14 data work.COURSE; set PG13.COURSE; run;
ERROR: Libref PG13 is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.COURSE may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
15
16 GOPTIONS NOACCESSIBLE;
17 %LET _CLIENTTASKLABEL=;
18 %LET _CLIENTPROJECTPATH=;
19 %LET _CLIENTPROJECTNAME=;
20 %LET _SASPROGRAMFILE=;
21
22 ;*';*";*/;quit;run;
23 ODS _ALL_ CLOSE;
24
25
26 QUIT; RUN;
27
Hi Saman,
It looks like the SHARE library isn't assigned. Is it possible that in the new configuration, the library is not assigned at startup? Perhaps defined in SAS metadata, but not checked as Preassigned?
I recommend opening your project in EG and running it step-by-step to see where it might fail, if it fails interactively.
Hi Chris,
In documentations I found I use something like "libname PG13 meta library="PG13" metaout=data; " for assigning libraries. Is that the right way?
Regards,
Saman
Saman,
Yes, that is one way to do it. Another is to have the SAS admin change the library definition to "Pre-assigned", so that the library will automatically be assigned when the SAS Workspace starts up. (So the icon would show as yellow, not white, initially.)
I am using EG 5.1.
The following line of VBA code executes fine:
Set SASApplication = CreateObject("SASEGObjectModel.Application.5.1")
However I get an error when referencing the following property:
Application.Profiles.Count
Where can I find documentation on the SASEGObjectModel object for 5.1?
I fixed this error, but I am still looking for documentation on the object for 5.1
Rodney, the object model hasn't changed (other than the 4.2, 4.3, 5.1 Application IDs). You can find all of the current resources linked from here. It includes a link to the 4.2 reference doc, which still applies even in 5.1.
Hi Chris, Thanks again for all your great work. It's a brilliant work as usual. Just curious for any ideas on opening an existing SAS project and run the existing order list for the process from VBA? Do we just change code to
Set project = Application.Old
Set sasProgram = project.existingprogram.open ?
Thanks.
Do you mean that you want to run an existing OrderedList within an existing project? To do that:
From the Project object, get the ContainerCollection
Iterate through the Container items until you find the OrderedList container (ContainerType = 5)
From OrderedList container, you can use the name of the Ordered List as an indexer and run it (example: container.Items("MyOrderedList").Run()
Thanks for your quick response. but I'm running to problem to get the containercollection. I use the following code and want to get view of orderlist container but it failed and says object required. I'm new to VB script so gratitude for all your patience.
Option Explicit Public Application 'Application Public Project 'Project object Sub test() Set Application = CreateObject("SASEGObjectModel.Application.5.1") Dim j As Long Dim i As Long Const egFile = 1 Const egOrderedList = 2 Const flowContainer = 5 Dim item Dim flow For Each flow In Project.ContainerCollection If flow.ContainerType = flowContainer Then flow(i) = "Process Flow: " & flow.Name For Each item In flow.Items Select Case item.Type Case egFile item(j) = " " & item.Name & ", File Name: " & item.Filename Case egOrderedList item(j) = " " & item.Name & "" End Select Next End If Next End SubLei,
At a glance your code seems okay EXCEPT that it's missing a statement that opens a project file. For example:
Set Project = Application.Open("c:\myprojects\project.egp","")This has to happen before you begin to use the Project object in the later statements. You might try looking at some of the examples in this paper, if you haven't already.
Hi chris, thanks for your posting. I figured that part out to open the existing project. However, just one simple question about how to change the default server to "sasapp" instead of "Local" ?
do I just insert orderedList(p).Server = "sasapp" in below loop? Thanks
For p = 0 To (orderedList.Count - 1)
MsgBox ("Running Ordered List Number " & p + 1)
orderedList(p).Server = "sasapp"
orderedList(p).Run
Next
You can use script to change the assigned server only for SAS programs (Code) items. All other tasks have the server "defined" by the data sets on which they operate -- where the data resides, that's where the task runs. And if your Ordered List contains all SAS programs, you will need to loop through all of the programs in the list to change the server. You won't be able to change them all at the "Ordered List" level.
Thanks again , Chris. Keep up the good work. You are da man. hehe
Hi Chris, first of all thanks for the great work. I have one question: is it possible to use two server simultaneously? My program in SAS EG, that I would like to schedule, has some queries in sasapp and others in local server.
My idea was doing it by changing one of the following two lines:
' Set to your metadata profile name, or "Null Provider" for just Local server
app.SetActiveProfile("My Server")
' Set the server (by Name) and text for the code
sasProgram.Server = "SASApp"
Do you think there's a chance?
Thanks a lot.
Hi Gianluca,
Yes, the approach depends on your setup.
If you have a single metadata server with multiple SAS Workspaces, you should be able to simply assign the sasProgram.Server value ("SASApp", then "Local") and submit the programs on each. I don't think these would run simultaneously though. I believe that in the script, these calls will run synchronously -- one after the other.
If you have two completely different metadata environments, or one metadata environment and one just "local" (Null Provider), then I think the best course is to create two EG Application objects and SetActiveProfile for each independently. Again, in a single script, these steps will run synchronously.
If you are running an already-established project with code nodes assigned to each server, then you can use the script to run an entire process flow. The programs assigned to different servers will run in parallel -- see this blog post for more.
Hi Chris. I have to change the password of active profile every 60 days because of company policy.
Just wondering if there's any options here we can change the password for the active profile when we choose the profile and server?
You might need to store your credentials for retrieval while you're not logged on, and modify that location as your password changes so that EG can connect to the SAS metadata server and workspace. See the SAS Note 30917 for details.
Pingback: SAS Automation in MS Office, Part 1 | Science of Analytics
Hi Chris,
Apologies if you've already answered this problem. I might not have been able to find the solution posted. I have an EG 4.2 project that I scheduled to run every Friday morning. I did this via the options available in EG, except the automation doesn't work. I'm running Windows 7 x64 so I'm assuming that may be the reason why. Is there a way to edit the vbs file to solve this problem?
You can't edit the VBS to solve it, but perhaps you can edit the scheduled task. In Windows Task Scheduler, you need for the 32-bit version of CSCRIPT.exe to process the program. So instead of just "c:\projects\EGScheduled.vbs" (which will use the 64-bit script engine by default), you need to run "%windir%\SysWOW64\cscript.exe" with the full path of the VBS file as a command argument.
We have SAS 9.3 on 64 bit Windows 2008 r2 server with Powershell version 2.0.
I am running 32-bit Enterprise Guide 5.1 using 32 bit powershell, and I am unable to call the comObject , when I submit this statement:
$egp = New-Object -comObject SASEGObjectModel.Application.5.1
It does not return the cursor back, nor any errors. It hungs up there.
I am able to invoke other comObjects in Powershell in SAS, like
$servDef = New-Object -comObject SASObjectManager.ServerDef
I am also able to invoke SASEGObjectModel.Application.5.1 using vbs script on the same machine.
We tried to unregister teh EG application and register it , but nothing helped. Is this a powershell problem or EG 5.1 issue ?
Thank you.
Hi Tina, I think you are already working with Tech Support on this issue, correct? If so, let's just keep pursuing it that way. It *should* work, so something is obviously amiss with the environment.
Yes. I am working on this with Tech support. We are still figuring out.
Hi Chris,
I am a SAS newbie and I am wondering whether it is possible to use SAS Enterprise Guide without any profile - or whether there is a standalone version of SAS that I can use. I recently had SAS EG 4.3 installed on my company machine but I currently can not have a profile set up for me due to data privacy restrictions of my location. I however would like to use SAS to import and analyse files locally.
When I try running a SAS program without a profile I get an error indicating that I have no server connection. How do I set up a SAS server on my machine?
Is there a way of doing this?
Anumwo
Anumwo,
SAS Enterprise Guide is a front-end client application; a full SAS installation is required somewhere. SAS can be either on the same PC with SAS EG (that's "No Profile" mode), or it can be on a remote Windows, Unix, or mainframe server (with a metadata profile).
If you are interested in using SAS EG for learning purposes (professional development), see this article about SAS OnDemand for Professionals.
Hi Chris,
I am new to using SAS Enterprise Guide. We have SAS on a remote Unix server. I have successfully created several projects in SAS EG from several collections of individual SQL queries in an attempt to automate some monthly reporting, but when I use the built-in functionality within SAS EG to try to schedule these jobs, I get an error in the windows task scheduler that says "Application not found." Have you ever seen this issue before?
Thanks,
Elizabeth
Elizabeth,
The integration with the Windows Task Scheduler requires an admin privilege, and perhaps that's missing in your case. I suggest that you work with SAS Technical Support to see what's amiss.
You can still work with automation though, and schedule/run these scripts on your own -- even if you can't get the direct integration with the scheduler to work. This is described in this article, which links to a paper and to a sasCommunity.org web page with lots of examples.
Hi Chris,
My computer has SAS EG 5.1, MS Office, and Windows 7 Enterprise; however i am not able to run the above script directly - I keep getting the error message "ActiveX component can't create object". MS Office, and Windows Enterprise are x64 bit. Is there anything that I am missing?
Thanks,
Aditya
Aditya,
Be sure that you're using the correct version of CSCRIPT.exe -- there is a 32-bit version and a 64-bit version. You must use the version that matches the "bitness" of the SAS Enterprise Guide installation. If you have 64-bit SAS Enterprise Guide, then use "%windir%\cscript.exe". For 32-bit EG, use "%windir%\SysWOW64\cscript.exe".
You should also try a simple "are you there" script to ensure that SAS EG automation is working. You can find one on this sasCommunity.org page. Here is the direct link to the script.
Hi Chris,
Thanks for your help. I used the appropriate script and it worked! However, I was not able to run the script directly, it involved the use of a command prompt. I want to do an automation task where i can use the vb script on Windows Scheduler. Is it possible to do this without having command prompt to intervene?
P.S. - MS Office is 32 bit, SAS EG 5.1 is 64 bit, and Windows 7 is 64 bit. I had mentioned some wrong info in my earlier note. Not sure if this info would help.
Thanks so much!
Aditya
Aditya,
Yes, you can use Windows Task Scheduler to run this -- but you must include the complete path to the CSCRIPT.exe command in the task.
Since the commands can be ambiguous on a Windows x64 machine, it's best to include the command and path within the task that you want to schedule. Simply "running" the VBS file (without specifying the CSCRIPT command) will usually run WSCRIPT (which might pop message boxes that you don't want), and it will depend on the calling process as to whether you get the 32-bit or 64-bit version of the scripting engine.
Hi Chris,
It works! Thanks you so much for your help! You're the man!
Thanks again!
Aditya
Nice thread, I have SAS EG 5.1, testing the code provided here in VBA (see top of the page). Everything work fine. I got my project run and produce an Excel file as output, OK.
Now I've just created a prompt for my project and when I re-executed the code, I'd expect the prompt to appear and ask for input...but it didn't! Any suggestions? Thanks in advance!
Vale
Vale,
Because this is an automation API, prompts are not displayed by default. But you can force the prompts by using this call in your script:
Hi Chris,
I'm using the following code to successfully call 3 individual .sas files through VBA. These 3 .sas files are nodes inside an enterprise guide project. Is there a way I can call the .egp file and run that rather than all the .sas files individually?
I have tried - obSAS.LanguageService.Submit ("options source2; %include '\\Path\Test.egp';") - but this returns an error message saying the statement is not valid or it is used out of proper order.
Sub Test() Dim obObjectFactory As New SASObjectManager.ObjectFactory Dim obSAS As SAS.Workspace Dim obServer As New SASObjectManager.ServerDef obServer.MachineDNSName = "mymachinename" obServer.Protocol = ProtocolBridge obServer.Port = 8591 Set obSAS = obObjectFactory.CreateObjectByServer("SAS", True, obServer, "myuser", "mypassword") obSAS.LanguageService.Submit ("options source2; %include '\\Path\Test.sas';") obSAS.LanguageService.Submit ("options source2; %include '\\Path\Test2.sas';") obSAS.LanguageService.Submit ("options source2; %include '\\Path\Test3.sas';") Dim sLog As String sLog = obSAS.LanguageService.FlushLog(200000) MsgBox "" & sLog obSAS.Close End SubCraig,
The SAS language processor cannot read an EGP file. You'll need your SAS programs to be on the file system, as per your working example. Or, you could use the EG automation APIs to read the items within your project file, export them, and then use your LanguageService calls to submit them. You can find examples of how to read/export SAS programs from EGP files in my SAS Global Forum paper about automation.
Thanks very much for the info. It's a great help!
When updated to SAS 9.4 and SAS EG 6.1, the App hang in : EGApp = new SAS.EG.Scripting.Application(); Any one can help? Thanks!
From your sample code I'm guessing that you're using C#/.NET for this? This might be something that you need to contact SAS Tech Support for. If you can turn on the logging using the instructions in this SAS Note 17730, then that might help to provide some details for diagnosis.
Hey Chris,
I have a project in EG 6.1 that runs a VB script through system command add in, when I schedule the project, the system command piece doesnt run. Log shows a successful execution , but there are no results.
the project runs fine manually.
RJ, I'm not sure why that would be happening. When you schedule the project, is it using your Windows login to run the job? And does the VB script do anything "interactive" -- displaying a UI or automating a user interface somewhere?
Hello Chris;
I want to thank you first because i learned a lot from your subjects, I have programmed a script in vba that allowed me to turn a sas program on it, it works pretty well, however when the script try to run the sas program, the sas authentication window opens "which makes sens" , the problem is that when i put my (login-password) and i confirm it shows me the same windows again and again .... , and i need to reauthenticate many times ( sometimes it works in the first time, sometimes it do it 10 times).
So my question (or the idea that i had) is there a vbcode that can allow me to save my UserName and my password in 2 variables which are called and integrated in the authentication windows each time that it appears.?
If there is another solution to fix this problem except this one i'm also interested.
waiting for your reply, thank you.
PS: Sorry for my English.
Hi Anass,
You might need the "credentials.xml" approach that is described in this SAS Note. This allows your scheduled jobs to find the correct credentials so that the login prompts do not interfere.
Hello Chris
thank you for your reply, it just that i dont have authorisation from the company to create files in the sas path. Anyway, thank you a lot for your publication and your help, it was verry usefull.
Hi Everyone - I would like to write VB Script to open existing SAS EG project (.egp file) and execute it as per the sequence linked in the project.
Thanks in advance!!
If you use Project.Run or ProcessFlow.Run from the automation API, you should get the desired sequence. For more information about the API and some examples, see this blog post.
Hi,
I work in VBA and I want ton run a SAS program from Excel, so, i used this code. But I got the error message "ActiveX component can't create object" !!
I have SAS EG 5.1 (64 bits) and Excel (32 bits)
thanks a lot
Your 32-bit Excel cannot launch 64-bit EG because the automation objects aren't registered in the 32-bit Windows registry. You can *try* to make this happen manually with this command (might need to adjust paths for your system):
c:\windows\Microsoft.NET\framework\v4.0.30319\RegAsm.exe "C:\Program Files\SASHome\SASEnterpriseGuide\5.1\SASEGScripting.dll" /codebase /tlb
Then see if it works. Otherwise you might need to install 32-bit EG (which is fine, and should work with all of your content).
Hi Chris,
I have an Enterprise Guide project running automatically from a VBA macro within an Excel program. A colleague cannot run the same macro. They have the same installation of Enterprise Guide 4.3, however have a 64 bit Processor, but i have confirmed both the Microsoft Office and Enterprise Guide are 32 bit installations. The program fails at the CreateObject("SASEGObjectModel.Application.4.3") stage, with the error: 'Error #80070002 In Function CreateObject Automation error The system cannot find the file specified'
Having noted the comments on bitness I feel like this is causing the issue but as I do not use the commands 'c:\Windows\SysWOW64\cscript.exe c:\projects\AutomationNewProgram.vbs' to initiate the automation through the VBA macro I cannot see an obvious solution.
Any help you can provide, as always, is much appreciated.
You might need to re-register EG's automation API. Try running the following command on your colleague's system.
c:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe "C:\Program Files\SASHome\x86\SASEnterpriseGuide\4.3\SASEGScripting.dll" /codebase /tlb
Might need to adjust the above path for your EG install folder.
Thanks for the response, 'You must have administrative credentials to perform this task' was the playback. Should an uninstall and re-install of Enterprise Guide perform a similar task?
It would -- but of course that requires admin privileges too.
Is there a way to run SAS EGP file which is stored in unix box, though task scheduler ??
Alas, there is not. The scripting allows you to open only local project files, not remote projects stored on the server.
Hi Chris.
I am running SAS EG 5.1 on an AIX server. We run our batch SAS jobs using a program with multiple %Include statements. Is there a way stop the processing of the next %include if there is an error in the prior %include. Basically, the final program called is the program the writes out the results, but if the prior step encountered an error, we don't want the output to be sent.
Rather than depend on an error flag, I like to use a condition that looks at an expected output, like a data set, as a cue that the results are ready for the next step. Then you can use a macro to test for that condition and %INCLUDE your next file if so. Here's an example that checks for an expected data set.
%macro createoutput; %if %sysfunc(exist(work.results)) %then %do; %include "c:\programs\final.sas"; %end; %else %put ERROR: No output created; %mend; %createoutput;>The scripting allows you to open only local project files, not remote projects stored on the server.
Hi Chris. Thanks to all your blogs, I've successfully been able to develop a .net solution to run .egp files via scripting and output the results to use in my .net solution.
I see that scripting only allows for using files on a local drive however I need to be able to use .egp files that are stored in the SAS Folders Tree. I've seen that I can probably use the SAS IOM to download/save an .egp file from the server to the local drive... Am I right about this and if so, can you point me in the direction of an example or some documentation?
Thanks,
Jason
Jason, that IOM approach can work if the files are on the file system of the SAS server. I have PowerShell examples for that. But if the EGP file is in a metadata folder, then that's using the SAS Content Server as the backing storage -- you might need to use a web service/ WebDAV methods to access the content there.
Hi Chris,
Thank you very much for your post.
I try to schedule my SAS EG project using SAS EG scheduler. It doesn't work. The error is CreateObject("SASEGObjectModel.Application.4.3") cannot create the object. I have checked from VBA it seems SASEGScripting was not registered. I just wonder why it is not register first when SAS EG install. This is first. Second is it is necessary to register SASEGScripting to schedule a sas code to run in SAS EG?
Cheers
SASEGScripting IS registered when you install SAS Enterprise Guide, but sometimes the registration becomes "undone" by some other process. It does need to be registered for the scripts to work. Reregister with this command:
c:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe "C:\Program Files\SASHome\x86\SASEnterpriseGuide\4.3\SASEGScripting.dll" /codebase /tlbHi Chris,
Good day. Actually i have a WScript which triggers SAS EG flow and correspondingly it ll update permanent datasets. There are around 10 datasets which will update one after the other and the process ends thereby. But sometimes, i could see that the update happens only for 4 to 5 datasets and later the process gets stuck and rest of the datasets won't be updated. Could you tell on why this will happen ? Is this due to any connection problem ?
It's difficult to say for your particular project. You could turn on application logging and perhaps collect more details that SAS Tech Support could help diagnose.
Thank you Chris
Hi Chris,
Thanks for your replay. After registering SASEGScripting, I set a schedule run in SAS EG. It works. But I don't understand why the same vbs code works in SAS EG schedule not in VBA. In VBA Set app = CreateObject("SASEGObjectModel.Application.4.3") doesn't work. But I do find there is SASEGScripting in VBA and tick it in tool/referecne of VBA.
Only one thing, I can imagine is password. How I do put username and password in the VBA code?
Cheers
Pihong
I suspect you're using the 64-bit version of VBScript but your EG is 32-bit. Try running your script with this command:
%windir%\syswow64\cscript.exe (path to your vbs file)
Hi Chris,
My end users don't have RDP access to the remote server. Therefore, traditional batch processing is not possible.
I'm developing an application in EG which has many (20+) hand written program entries. This was done to allow processing restartability, since the overall process flow can take a long time to run. A flag is used to either recreate intermediate files, or reuse them if they already exist.
I need to save the complete SAS log when the end users run the application, in case I have to investigate errors. I want the "usual" datetime stamped log each time they run the EG project or converted batch code.
From what I can tell, there is no programming interface into the EG project log, i.e. programmatically turn on the project log, clear log, save log to file. At least within EG itself; perhaps within the EG COM APIs? What I need is analogous to -altlog, where the log continues to go to EG, but is also saved to a file.
What do you recommend:
1) Somehow do this in EG?
2) There are Powershell cmdlets that support IOM processing. See https://gist.github.com/cjdinger/4771455 :) and https://sourceforge.net/p/cmdlets4sas/wiki/Home/. Would this be a better option for my use case?
I'll want to use Powershell, but should be able to convert the above VBScript example to Powershell. If the Powershell example already exists "out there somewhere", it would save me the trouble.
Thanks for all the great tips!!! Much appreciated.
Scott, I have links to several PowerShell examples, including a few for EG automation, here on this sasCommunity page. Hope that helps!
Hi Chris,
My question is pertaining to the communication between SAS EG client / Metadata server. This is more for my understanding. I hope, this helps the other members of the group too!
Let us consider that we are developing a project in SAS Enterprise Guide which accesses a dataset in a permanent library (let us call this library reference as ABC and exists in a UNIX server).
The libref has already been defined in Metadata server using Management Console.
After the completion of development, we take the package of the EG project; then we are heading to schedule the package in CRON using SAS command in UNIX server.
When we generate the package of the project in EG, a META library reference is created (for ABC) in the code generated by EG as below.
libname ABC meta repname='Foundation' liburi="SASLibrary?* [@Name='My_Library_Reference'][DeployedComponent/ServerContext[@Name='SASMeta']]";
I understand 'My_Library_Reference' is the description of the library reference 'ABC' (given at the time of creation).
Note: Development/Production areas are defined in different physical servers. And the complete suite of BI/DI SAS products have been installed in the UNIX servers side. And the GRID functionality as well.
Now when the SAS command starts to be executed (via CRON tab), first it has to communicate to the Meta data server in order to process the above libname statement. The libname statement contains just the label (SASMeta) of the server. It does not have any IP address / port number.
In this case, how the program will execute successfully by communicating with the Metadata server ?
(Do we need to alter the code in the package before placing in the prod area ?)
After the program is able to communicate with the Meta server (for the libname processing), then the code has to be executed in GRID environment.
What if the work space server is in a different machine than the one which is invoking the program ?
I examined the code; and it does not seems to have any sort of indicators that it needs to be run in a GRID environment.
How all the above are achieved ?
And similarly how the code acquires the knowledge that it has to speak with the Object Spawner ?
Can you please help me to understand the events happing behind the screen when the program is getting executed ?
Thanks,
Renganathan M.
Renganathan, thanks for also posting this into the communities. I've responded there, and I'm sure others will as well.
Thanks Chris!! :-)
Hi Ranganathan M,
can you please share the from File > schedule Project step to end step to schedule EG proejct which saved to local PC(EG Installed)?
How to generate log file to local PC for scheduled EG project to check the status?
Thanks,
Ram
Hello Chris,
I would like to convert SAS E. Guide Project from 4.3 to 7.12 using script. I'm using :
prjName = "C:\Temp\Boucle\01_STRUCTURE.egp" 'Project Name
Set prjObject71 = app.Open(prjName,"")
but it give me an error telling i have to convert the project before using automation version.
Is it possible to do that ? Which class/method to use ?
Thanks for your help.
Olivier
Hi Olivier, you can convert EGP files in batch by using the MigrationWizard.exe tool that's in the EG application directory.
Hello Chris,
for som needs in my work, i'd like to connect SAS eg 7.1 to vba. I already did it with sas eg 4.3, but it doesn't work for this version.
I joined some screenshots of errors that i face when i use the same code as with the eg 4.3.
Do you have any solution for this ?
Ps: i can't save files in local machine (C:/) and i don't need to put credentials in every run !
Thank you, sorry for my bad english
Best regards
Hi Karhmich -- did you post on the SAS Support Communities? You can add your screenshots and more detail there, and we'll be able to help.
Hi Chris
No I have not posted on this link SAS Support Communities, but I will do it now.
Thank You,
Hi
I am getting Microsoft 800A08 object required error while I run the script on my Windows 7 PC. However same Script is running in Windows xp PC.
Do i need to make any changes on script or this error pertain to Windows 7...any idea..
appreciate your response....
regds
Is it possible that you are running a 32-bit version of EG on a 64-bit machine? If so, you need to make sure you're running the 32-bit version of CSCRIPT.exe (in %windir%\SysWOW64).
My organization uses this script to enable batch submission from 3rd-party text editors. This process works great, but users must submit the full code each time and, with each submission, are subject to a delay while re-connecting to the SAS server. Is it possible to modify this script so that the EG session remains open and accessible to further submissions? It would be nice to have persistent access to the work library to run partial code, for example.
(Sorry if you've addressed this above or elsewhere! I scanned the comments as best I could, but didn't see an answer to what I'm asking.)
Thanks in advance!
Chris,
In a single script you can keep the connection open, but there isn't a way to carry the connection across to subsequent scripts. You might be able to work something out with a direct connection to the SAS Workspace. I've got examples of doing this in Windows PowerShell and in Microsoft .NET. See:
Articles about SAS and Windows PowerShell
Building your own SAS client with Microsoft .NET
Thank you so much!
Turns out this was precisely what I was looking for. Using your examples, I was able to build a PowerShell utility that provides everything I asked about (and more!).
Thanks again,
Chris
Excellent! If you are so inspired, maybe you'll be willing to share your approach as an article on communities.sas.com -- I'm sure that others would be interested!
I need my organization's approval before releasing any code or concepts. But if they are okay with it, I'll plan on posting to communities.sas.com sometime soon!
Chris,
I am at a bit of a loss of why SAS programmers would want to use something besides SAS to submit SAS programs in batch. I understand the one advantage of scheduling, but if I have to re-submit 500+ programs after validation or after an update, I will just read the directories, obtain a list of paths with file names, and use the X command to submit each individually. Depending on the needs, I will submit them in a certain order (hierarchy), for instance, submitting the Main programs then the Validation programs, keeping that sequence if subsequent programs use the previously created data sets as input data sets. This issue is the same regardless of choice of approach to submitting in batch. Using a SAS program to batch submit SAS programs is an option with SAS Grid, but I am not sure that it is with Enterprise Guide. Typically, a few SAS programmers can write a .bat file and I am not even sure about (VBS) scripts, but all of them can write SAS programs, including checks for errors/failures that should stop the submission and send an email. In Windows, one can usually start with obtaining the path to the executable by right clicking the icon and selecting properties. In that approach, we are using sas.exe with the correct options: -CONFIG, -SYSIN, -SASUSER, -NOSPLASH, -SASUSER, -ALTLOG , et cetera. I see SEGuide.exe, with no options. Is this approach dead in EG?
Kevin, I agree that batch processing is more elegant in "Base SAS" when you can drive SAS from the command line or via scripts. This blog post is meant to share a technique you can use from a client PC when you don't have access to the SAS command or operating system where SAS is installed. SAS Management Console offers different options for scheduling SAS jobs in a SAS metadata environment -- these might help.
I'm new to EG 7.1 as our desktop SAS is going away. I've created a schedule and vbs script but it throws an error (Object required Code:800A01A8) when I try to run. Any idea what am I missing?
Using 32-bit EG on a 64-bit machine? Make sure you're launching the 32-bit version of CSCRIPT.exe. If relying on Windows to launch a default "VBS" app, that's probably not going to work. Make sure the task command specifies the proper path to the CSCRIPT.exe for the bitness of EG. (example: "c:\Windows\SysWOW64\cscript.exe".
See also more tips and tests in this communities article.
Pingback: Doing more with SAS Enterprise Guide automation - The SAS Dummy
Hi Chris,
I have different requirement from my users to schedule sas code or EG Projects using SAS EG.
we have SAS 9.4 server on linux server and users do access server using SAS EG from windows PC(both are on different network). when users run code or schedule jobs like above if the run time is more than 60 minutes then those sessions gets disconnect due to network/PC policies.
My Users want to schedule their SAS Code/EG Projects to execute as batch process on remote SAS Servers(Linux server).
Is there a way in SAS 9.4 BI Server to allow Users using SAS EG from Windows PC to schedule SAS Code to get execute on remote SAS Server(Linux)?
Thanks,
Chenchu
There is not a way to schedule an EG project as a "job" natively on another platform. I usually export all of the code I need into a single program, and then schedule that with a system tool, like cron. This blog post describes a few other methods that use SAS scheduling tools, if you have them available.
Hi,
I'm having difficulties with temporary file location when running vbs script. I've set Environment variables in the SEGuide.exe.config and it's working fine when using SAS EG normally. But when I run code from VBS application log says:
DEBUG SAS.EG.Scripting.Application [(null)] - No Environment Section found in the app.config file
Where can I find this app.config file to set those env variables? How to set this?
Can someone help?
Thanks in advance
SEGuide.exe.config is not applied when running a script, as the executing process is CSCRIPT.exe or VBSCRIPT.exe.
To temporarily redirect the TEMP directory for your script, you will probably need to use a batch/cmd file to wrap the script. Example:
Save that to a BAT or CMD file, and then use that Windows batch file as the target for your scheduled job.
Hi Chris, is there any way (chance) to use method SaveAs with specific name of an Excel Worksheet, eg.
sasProgram.OutputDatasets.Item(n).SaveAs _
getCurrentDirectory & "\" & dataName & ".xls" + abstract definition - Sheet.Name,
using VBA ?
That would enable to save all the output datasets to one Excel file populating different worksheets.
Best
Not with the SaveAs method on the data sets object, no. But you could use VB Script to automate Excel and combine files into sheets. Or you could use SAS and PROC EXPORT to create the XLSX file you want (with multiple sheets) and then the Copy Files task in your project to download this.
Ok, thank you very much for reply. Appreciate
Hello,
We have run batch scripts for years on SAS Grid with 32 bit EG on 32 Bit Windows 7. When we migrated these jobs to run via batch as the same "sasbatch" user on WIndows 10 x64, we began to get occasional, apparently random cscript errors. Jobs will run ok, then fail one time, then run ok after that. We have a SAS support track but because this is not an obvious issue with code or an EG issue, it's hard to sort out.
Our question is: is if EG 7.15/8.2 a true 64-bit app when installed in “64 Bit Native Mode”? We want to know for sure which cscript.exe we “should” be using in 64-bit Windows 10.
I have reviewed the comments above and your linked, more recent references. I apologize if I have missed the answer elsewhere. There's so much I just hoped to get this one question definitively answered, if possible.
Thanks for any suggestions!
If you are using the 64-bit version of EG (should check the Help-About window in the app), then you should run:
32-bit EG users would use:
And no...I did not get those reversed by mistake.
Hi Chris,
When instantiating the “Application” object with the new version of the SASEGScripting.dll, I am getting the following error:
Method 'SendToMailRecipient' in type 'SAS.EG.ProjectElements.ProjectCollection' from assembly 'SAS.EG.ProjectElements, Version=7.100.5.0, Culture=neutral, PublicKeyToken=be58efc3b934219b' does not have an implementation.
I have never seen a reference to “SendToMailRecipient” so I do not know the newer version of the SASEGScripting.dll is referencing it.
I am using SAS Enterprise Guide 8.2 version and am running a Report Scheduler.
Error Encountered:
Method 'SendToMailRecipient' in type 'SAS.EG.ProjectElements.ProjectCollection' from assembly 'SAS.EG.ProjectElements, Version=7.100.5.0, Culture=neutral, PublicKeyToken=be58efc3b934219b' does not have an implementation.
Do you have any suggestions?
Regards,
Mahesh, by using SASEGScripting.dll in this way, you're a bit off of the supported path. The scripting support is limited to the "contract" of scripting methods you can access via VBScript or PowerShell, for example. When you delve into the .NET assemblies, you risk issues around version referencing and assembly resolving. (Saw a tech support track on this recently, I'm guessing it might have been from your team?)
Thanks for your reply, Chris.
Ok, that makes sense. Do we have other ways to schedule jobs if this method is not viable with the upgraded SAS EG version? Can we use the SAS EG client that is installed on the AWS BI Scheduling server and not having to use the SASEGScripting.dll? Are there any constraints or limitations to schedule with SAS EG client on Citrix?
Regards.
I can't speak to your specific environment, but:
Hi Chris
It's amazing to see this ten-year-old post, really great!
Unfortunately, I work on a cloud environment where I'm not even granted permission to CLI.
I tried EG scheduler and guess it's working via system scheduler(I'm happy to confirm this mechanism in your post), also failed...
I'll probably just run the batch *.sas as a whole through bare sas.exe, which would help a lot like semi-automation.
But the trouble is, I need the EG profile to connect to metadata server, before I actually plug in a GreenPlum engine, and send SQLs after that.
I still don't know how to finish this without EG, might not be the focus of this post though.
Thanks for any notes!
Regards
If you're driving this on a cloud-hosted SAS but by connecting from a local machine, you might be able to use a simpler mechanism like PowerShell or a custom app. Or maybe even SASPy, if you can connect a local Python environment to your remote SAS.