SAS EG Automation with remote server connection using Powershell

[ramya4]

Hello,

I'm looking for ways to automate a SAS EG 7.1 project using PowerShell as the process that I currently execute is a monthly process and it is done manually as of now. I would want to automate the process so that the project runs thru every month automatically on the scheduled date and time. The project is currently exporting an excel spreadsheet on to the SharePoint site and would like that spreadsheet automatically generated every month based on the new source data. The SAS EG project connects to the remote server using login id and credentials. I have the below sample code in Powershell which executes the SAS EG project but unable to see the results anywhere- 

$objFactory = New-Object -ComObject SASObjectManager.ObjectFactoryMulti2
$objServerDef = New-Object -ComObject SASObjectManager.ServerDef
$objServerDef.MachineDNSName = "Workspace Server Name" # SAS Workspace node
$objServerDef.Port = xxxx # workspace server port
$objServerDef.Protocol = 2 # 2 = IOM protocol
# Class Identifier for SAS Workspace
$objServerDef.ClassIdentifier = "440196d4-90f0-11d0-9f41-00a024bb830c"

# create and connect to the SAS session
$objSAS = $objFactory.CreateObjectByServer(
"Server Name", # server name
$true,
$objServerDef, # built server definition
"XXXX", # user ID
"YYYYY" # password
)

# program to run
# could be read from external file
$program = Get-Content "C:\TestCode.egp";

# run the program
$objSAS.LanguageService.Submit($program);

# flush the output - could redirect to external file
Write-Output "Output:"
$list = ""
do {
$list = $objSAS.LanguageService.FlushList(1000)
Write-Output $list
} while ($list.Length -gt 0)

# flush the log - could redirect to external file
Write-Output "LOG:"
$log = ""
do {
$log = $objSAS.LanguageService.FlushLog(1000)
Write-Output $log
} while ($log.Length -gt 0)

# end the SAS session
$objSAS.Close()

Thanks!

[CaseySmith · Accepted solution]

Hi @ramya4,

The PowerShell code you shared is attempting to connect directly to a SAS Workspace Server and then submit SAS code to it (similar to as described in the blog post https://blogs.sas.com/content/sasdummy/2013/03/13/using-powershell-with-sas-workspace/).

The problem with your PowerShell code below is that "getting the content" of the .egp file ($program = Get-Content "C:\TestCode.egp";) is not going to return SAS code.  An EG project (.egp) file is a compressed proprietary store (ex. a zip archive).  If you get the contents of a text-based file (ex .sas file) instead, then the PowerShell code looks like it should work (it should submit the code in your .sas file to a remote SAS Workspace Server).

However, if your goal is to run the actual EG project (the .egp file) rather than just submitting some SAS code, then you really want to use the EG automation interface rather than connecting directly to a SAS Workspace Server and submitting code.  The EG automation interface will handle these things for you.  These blogs describe how it works and have some PowerShell examples:

https://blogs.sas.com/content/sasdummy/2012/04/17/doing-more-with-sas-enterprise-guide-automation/

https://communities.sas.com/t5/SAS-Communities-Library/Doing-More-with-SAS-Enterprise-Guide-Automati...

Or, you can click File->Schedule in EG to created an EGScript.vbs file to see an example of using the EG automation interface (running an EG project).  Then you could port that from VBScript to (or do something similar in) PowerShell.

Casey

[ramya4]

Thanks Casey! I tried using the EG scheduling option and looked like it worked. Appreciate for the quick response!

[isek]

Hello Casey,

I have a question.
Is it possible to connect, with the following code, SAS EG 7.1 in order to execute the project?

Thank you in advance

Isek

$objFactory = New-Object -ComObject SASObjectManager.ObjectFactoryMulti2
$objServerDef = New-Object -ComObject SASObjectManager.ServerDef
$objServerDef.MachineDNSName = "Workspace Server Name" # SAS Workspace node
$objServerDef.Port = xxxx # workspace server port
$objServerDef.Protocol = 2 # 2 = IOM protocol
# Class Identifier for SAS Workspace
$objServerDef.ClassIdentifier = "440196d4-90f0-11d0-9f41-00a024bb830c"

# create and connect to the SAS session
$objSAS = $objFactory.CreateObjectByServer(
"Server Name", # server name
$true,
$objServerDef, # built server definition
"XXXX", # user ID
"YYYYY" # password
)

# program to run
# could be read from external file
$program = Get-Content "C:\TestCode.egp";

 

# run the program
$objSAS.LanguageService.Submit($program);

# flush the output - could redirect to external file
Write-Output "Output:"
$list = ""
do {
$list = $objSAS.LanguageService.FlushList(1000)
Write-Output $list
} while ($list.Length -gt 0)


# flush the log - could redirect to external file
Write-Output "LOG:"
$log = ""
do {
$log = $objSAS.LanguageService.FlushLog(1000)
Write-Output $log
} while ($log.Length -gt 0)


# end the SAS session
$objSAS.Close()

[SASKiwi]

The answer to your question is in the post answer - you can't reference an EG project on the $program parameter, it has to be an actual SAS program like MyProgram.sas.

[CaseySmith]

To see an example of VBScript that can be used to execute an EG project (rather than SAS code directly as in your example), schedule a project in the EG interface, which will create an EGScript.vbs file you can inspect.

Casey