BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aves9019
Obsidian | Level 7

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()
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

13 REPLIES 13
LinusH
Tourmaline | Level 20
I'm not really following you, especially try to clarify the big picture and what the end product would be.
Data never sleeps
Aves9019
Obsidian | Level 7
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.
Kurt_Bremser
Super User

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.

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Aves9019
Obsidian | Level 7
Thank you Kurt I'm fairly new to EG and didn't fully understand the distinction
Aves9019
Obsidian | Level 7
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
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Aves9019
Obsidian | Level 7
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
Aves9019
Obsidian | Level 7
Just to clarify Chris am I replacing the Proc sgplot in the example with a proc export or is another method better suited
SASKiwi
PROC Star

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. 

Aves9019
Obsidian | Level 7
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()
ChrisHemedinger
Community Manager

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
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 4030 views
  • 5 likes
  • 5 in conversation