topic Re: Powershell to SAS EG in SAS Enterprise Guide

Powershell to SAS EG

Aves9019 — Thu, 25 Aug 2016 22:53:53 GMT

Hi all,

I have succesfully used the following program to invoke remote based SAS EG in windows using powershell and retrieve the log and listing to the powershell console. However I would like to take this a step further and create a delimited txt file to save on my local or a network drive. Can I alter the following program to do so? I would like to alter the powershell comands as I am unable to simply include a proc export due to restrictions on my local and network drives.

$objFactory = New-Object -ComObject SASObjectManager.ObjectFactoryMulti2
$objServerDef = New-Object -ComObject SASObjectManager.ServerDef
$objServerDef.MachineDNSName = "server.mycompany.com" # SAS Workspace node
$objServerDef.Port = 8591 # 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(
                "SASApp",      # server name
                $true,
                $objServerDef, # used server definition for Workspace
                "sasdemo",     # user ID
                "Password1"    # password
                )

$program = "proc sql; create table work.test as select* from perm.data; proc print data=work.test;run;"

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

# 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)

# 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)

# end the SAS session
$objSAS.Close()

Re: Powershell to SAS EG

LinusH — Fri, 26 Aug 2016 06:22:06 GMT

I'm not really following you, especially try to clarify the big picture and what the end product would be.

Re: Powershell to SAS EG

Aves9019 — Fri, 26 Aug 2016 11:21:56 GMT

The powershell logic connects my local based Windows to my remote SAS EG. I am then able to run the small sas program you see in the logic in Windows and powershell produces the listing output and log. By using proc print I am able to see the resulting data of the SQL query in powershell. Instead I would like to create a txt file of this resulting SQL quey using powershell. I am unable to simply include a proc export or any additional SAS logic as my SAS EG cannot access my local drive. I need to add to the powershell logic to create a text file of the resulting data from the SQL query. My main goal here is to move data from SAS EG to a local path through invoking SAS EG in powershell.

Re: Powershell to SAS EG

Kurt_Bremser — Fri, 26 Aug 2016 11:28:55 GMT

As far as I can read your code, you are not using a remote Enterprise Guide, but connecting directly to a Workspace Server on a remote SAS host.

Since @ChrisHemedinger has created a custom task for Enterprise Guide to transfer any type of file using the IOM connection, I guess he could help you out with how to write that in Powershell.

Re: Powershell to SAS EG

Aves9019 — Fri, 26 Aug 2016 11:39:55 GMT

Thank you Kurt I'm fairly new to EG and didn't fully understand the distinction

Re: Powershell to SAS EG

ChrisHemedinger — Fri, 26 Aug 2016 11:44:13 GMT

I think you're looking for the code to download a file using PowerShell, via SAS Integration Technologies. Read that blog post. Here's the full code on GitHub.

Note that even though you want to download a CSV file (text), I recommend using the binary file stream method. That will avoid transcoding issues that can occur with characters outside of your session encoding range. If the remote system is UNIX, you might need to fix line-endings on the Windows side after the download (as UNIX and Windows use different conventions for line ending characters).

Re: Powershell to SAS EG

Kurt_Bremser — Fri, 26 Aug 2016 11:48:19 GMT

Bingo!

Re: Powershell to SAS EG

Aves9019 — Fri, 26 Aug 2016 12:15:37 GMT

Thank you Chris I am working on UNIX. Would you happen to have an example or snippet of the binary file stream method as I am unfamiliar with this

Re: Powershell to SAS EG

ChrisHemedinger — Fri, 26 Aug 2016 12:25:28 GMT

The code in my example should work to retrieve your file -- just download the CSV file instead of the PNG.

To then convert the line endings in the CSV (which might not be important to you, depending what your next step is), some straight PowerShell code can help. Here's an example I modified from stackoverflow to convert all CSV files in a local directory.

Get-ChildItem * -Include *.csv | ForEach-Object {
    ## If contains UNIX line endings, replace with Windows line endings
    if (Get-Content $_.FullName -Delimiter "`0" | Select-String "[^`r]`n")
    {
    	$content = Get-Content $_.FullName
    	$content | Set-Content $_.FullName
    }
}

If you're just going to use the CSV file in Excel, I think you can skip this step. But if you think people will open these CSV files in a local text viewer on Windows, it's probably a good idea to convert.

Re: Powershell to SAS EG

Aves9019 — Fri, 26 Aug 2016 13:05:00 GMT

Thank you Chris this is great. I was able to create the file however the contents are a repetition of System.Byte[ ]System.Byte[ ] and my date is not present. In addition I can only open the file in read only mode and can seem how to kill the connection between powershell and excel to fix this. I attempted to export as pipe delimited text file which is what I would like to do but the file again only contained a repetition of System.Byte[ ]System.Byte[ ] . Thank you again for all of your help

Re: Powershell to SAS EG

Aves9019 — Fri, 26 Aug 2016 14:29:32 GMT

Just to clarify Chris am I replacing the Proc sgplot in the example with a proc export or is another method better suited

Re: Powershell to SAS EG

SASKiwi — Sat, 27 Aug 2016 02:29:22 GMT

If your objective is simply to write logs / text files / output from your SAS server session to a file share then I'd suggest it would be a lot easier just to do this directly from SAS to a server-accessible file share.

Re: Powershell to SAS EG

Aves9019 — Tue, 30 Aug 2016 15:44:04 GMT

Hi guys,
I'm really struggling here. My file looks fine in UNIX but when I open it on my network drive it just contains "System.Byte[ ]". If someone could shed any light on why this may be that would be much appreciated. This is what I have so far @ChrisHemedinger

# Example of how to use PowerShell to script the
# SAS Integration Technologies client
# You can connect to a remote SAS Workspace
# and run a program, retrieve the SAS log and download a file

# To use: change this script to reference your SAS Workspace
# node name, port (if different), and user credentials

# create the Integration Technologies objects
$objFactory = New-Object -ComObject SASObjectManager.ObjectFactoryMulti2
$objServerDef = New-Object -ComObject SASObjectManager.ServerDef
$objServerDef.MachineDNSName = "yournode.company.com" # SAS Workspace node
$objServerDef.Port = 8591 # workspace server port
$objServerDef.Protocol = 2 # 2 = IOM protocol
# Class Identifier for SAS Workspace
$objServerDef.ClassIdentifier = "440196d4-90f0-11d0-9f41-00a024bb830c"

try
{
# create and connect to the SAS session
$objSAS = $objFactory.CreateObjectByServer(
"SASApp", # server name
$true,
$objServerDef, # built server definition
"sasdemo", # user ID
"Password1" # password
)
}
catch [system.exception]
{
Write-Host "Could not connect to SAS session: " $_.Exception.Message
}

# change these to your own SAS-session-based
# file path and file name
# Note that $destImg can't be > 7 chars
$destPath = "/servedir/aves"
$destFile= "Test"

# local directory for downloaded file
$localPath = "\network\drive"

# program to run
# could be read from external file
$program =
"Libname aves '/serverdir/aves';
Filename csv '$destPath/$destFile.csv';
Data _null_;
Set aves.test;
File csv dlm=',';
Put (_all_) (+0);
Run;

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

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

# now download the image file
$fileref = ""

# assign a Fileref so we can use FileService from IOM
$objFile = $objSAS.FileService.AssignFileref(
"img", "DISK", "$destPath/$destFile.csv",
"", [ref] $fileref);

$StreamOpenModeForReading = 1
$objStream = $objFile.OpenBinaryStream($StreamOpenModeForReading)

# define an array of bytes
[Byte[]] $bytes = 0x0

$endOfFile = $false
$byteCount = 0
$outStream = [System.IO.StreamWriter] "$localPath\$destFile.csv"
do
{
# read bytes from source file, 1K at a time
$objStream.Read(1024, [ref]$bytes)

# write bytes to destination file
$outStream.Write($bytes)
# if less than requested bytes, we're at EOF
$endOfFile = $bytes.Length -lt 1024

# add to byte count for tally
$byteCount = $byteCount + $bytes.Length

} while (-not $endOfFile)

# close input and output files
$objStream.Close()
$outStream.Close()

# free the SAS fileref
$objSAS.FileService.DeassignFileref($objFile.FilerefName)

Write-Output "Downloaded $localPath\$destFile.csv:SIZE = $byteCount bytes"

$objSAS.Close()

Re: Powershell to SAS EG

ChrisHemedinger — Tue, 30 Aug 2016 19:43:30 GMT

Something was amiss in that script, the way the binary stream was assembled for the target file. Try this example -- instead of writing the destination file in pieces, this one assembles the bytes in memory and uses WriteAllBytes.

# Example of how to use PowerShell to script the
# SAS Integration Technologies client
# You can connect to a remote SAS Workspace
# and run a program, retrieve the SAS log and download a file

# To use: change this script to reference your SAS Workspace
# node name, port (if different), and user credentials

# create the Integration Technologies objects
$objFactory   = New-Object -ComObject SASObjectManager.ObjectFactoryMulti2
$objServerDef = New-Object -ComObject SASObjectManager.ServerDef
$objServerDef.MachineDNSName = "yourhost.company.com" # SAS Workspace node
$objServerDef.Port           = 8591  # workspace server port
$objServerDef.Protocol       = 2     # 2 = IOM protocol
# Class Identifier for SAS Workspace
$objServerDef.ClassIdentifier = "440196d4-90f0-11d0-9f41-00a024bb830c"

try
{
    # create and connect to the SAS session 
    $objSAS = $objFactory.CreateObjectByServer(
                    "SASApp",      # server name
                    $true, 
                    $objServerDef, # built server definition
                    "sasdemo",     # user ID
                    "Password"    # password
                    )
}
catch [system.exception]
{
  Write-Host "Could not connect to SAS session: " $_.Exception.Message
}

# change these to your own SAS-session-based
# file path and file name
$destPath = "/u/userid/temp"
$destFile = "class"

# local directory for downloaded file
$localPath = "c:\temp"

# program to run
# could be read from external file
$program =  
       "Filename csv '$destPath/$destFile.csv';
        Data _null_;
        Set sashelp.class;
        File csv dlm=',';
        Put (_all_) (+0);
        Run;"

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

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

# now download the image file
$fileref = ""

# assign a Fileref so we can use FileService from IOM
$objFile = $objSAS.FileService.AssignFileref(
     "csv", "DISK", "$destPath/$destFile.csv", 
     "", [ref] $fileref);

$StreamOpenModeForReading = 1
$objStream = $objFile.OpenBinaryStream($StreamOpenModeForReading)

# define an array of bytes
[Byte[]] $bytes = 0x0
$allbytes = @()
$endOfFile = $false
$byteCount = 0

do
{
  # read bytes from source file, 1K at a time
  $objStream.Read(1024, [ref]$bytes)
  $allbytes += $bytes

  # if less than requested bytes, we're at EOF
  $endOfFile = $bytes.Length -lt 1024
  
  # add to byte count for tally
  $byteCount = $byteCount + $bytes.Length
  
} while (-not $endOfFile)

[io.file]::WriteAllBytes("$localPath\$destFile.csv",$allbytes)

# close input and output files
$objStream.Close()


# free the SAS fileref
$objSAS.FileService.DeassignFileref($objFile.FilerefName)

Write-Output "Downloaded $localPath\$destFile.csv: SIZE = $byteCount bytes" 

$objSAS.Close()

Output for me:

Downloaded c:\temp\class.csv: SIZE = 384 bytes