Windows PowerShell is one of my favorite tools to use for gathering information from my PC and network, and also for automating processes. If you are familiar with UNIX shells (such as Korn shell or its variants), you will probably be very comfortable with Windows PowerShell. Just like its UNIX predecessors, Windows PowerShell allows you to run commands and combinations of commands from an interactive console window. You can also write PowerShell scripts (saved as PS1 files), which allows you to combine the commands and programming logic to run more sophisticated operations.
I have used Windows PowerShell to automate some of my SAS-related processes, such as batch processing with SAS Enterprise Guide. I've also used it within my development work to gather metrics about files, computers on the network, and running processes -- all of which are interesting activities for a system administrator. Because Windows PowerShell allows you to gather different types of information and easily save it in CSV files, it's a convenient way to generate data sources for further analysis using SAS.
In the near future, I intend to share with you a few examples that combine SAS with Windows PowerShell. But before I do that, I need to share some basic information for getting started with PowerShell, because there are a few "gotchas" you might experience as a new user.
Where to find Windows PowerShell
If you are running Windows 7 or Windows Server 2008, Windows PowerShell is built in. Earlier versions of Windows Vista or Windows XP might not yet have it, so you would need to download it from Microsoft's Scripting Center.
Once downloaded, you should be able to find various shortcuts in Start->All Programs->Accessories->Windows PowerShell. On a 64-bit system you will find 4 shortcuts, as shown here:
In my experience, I almost always use the x86 versions of the PowerShell engine. These allow me to work with most aspects of the system, plus my favorite 32-bit applications such as SAS Enterprise Guide or Microsoft Office. I can still drive other 64-bit applications as long as they are launched out-of-process.
The Windows PowerShell (x86) shortcut invokes a command prompt environment, reminiscent of the familiar DOS shell (but a little fancier). The Windows PowerShell ISE (x86) shortcut opens the Integrated Scripting Environment, which presents a program window, command window, and an output/log window (gee, why does that seem so familiar?).
Enable Windows PowerShell scripts to run
Here's the most baffling part about getting started with PowerShell: by default, you cannot run PowerShell scripts on your system. It's a capability that comes as disabled out-of-the-box. You can run script commands from the console, but you won't be able to execute scripts that are saved as PS1 files. If you try, you will see an error message with text similar to this:
File C:\Test\TestScript.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. At line:1 char:23+ .\Test\TestScript.ps1 <<<< + CategoryInfo : NotSpecified: (:) [], PSSecurityException + FullyQualifiedErrorId : RuntimeException
This default policy setting is presumably for your own safety, though I must admit I don't understand it. Fortunately, you can easily change the policy by using the Set-ExecutionPolicy command:
Set-ExecutionPolicy RemoteSigned
Run this command from the PowerShell console, select 'Y' to confirm, and you'll now be able to run local PS1 files on your PC. ("RemoteSigned" indicates that local scripts will run, but scripts that you download from the internet will run only if they are signed by a trusted party.) You can read more about setting these policies for running scripts in the Windows PowerShell Owner's Manual.
A quick PowerShell example
Earlier in this post, I promised that Windows PowerShell makes it easy to collect system information and save it as CSV. Once we have it in CSV format, it's easy to import and analyze in SAS. Here is a simple example that you can run from the PowerShell console:
Get-Process | Export-CSV -Path ./processes.csv -NoTypeInformation
The Get-Process command generates a detailed list of the running processes on your machine. The output is piped to the Export-CSV command, which drops the result into a file named processes.csv in the current directory. That file is then easy to import into SAS using SAS Enterprise Guide (or by writing your own SAS program).
28 Comments
Great post -- thanks a lot. Is that possible to schedule a PC SAS batch task by PowerShell Script?
Charlie,
This question has two answers, both YES, but depending on your meaning.
If you want to use Windows Scheduler to run a PowerShell script, you can do that easily by saving the script as a PS1 file, then scheduling a task such as:
%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe -Command "c:\myscripts\myScript.ps1"The PS1 file can contain the necessary commands to run a SAS job, such as SAS.EXE with a SYSIN command-line option to run a program in batch, plus commands to prep whatever files are needed for the SAS program to run and to surface results where you want them.
If your objective is to write a PowerShell script to actually query/add/modify scheduled tasks on a system, you can do this by writing a script to interact with the Schedule.Service, as discussed here:
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/dc607f76-02c1-489f-b519-340b0faf8fcd/
Pingback: Calling Windows PowerShell from SAS: a simple example - The SAS Dummy
Pingback: Build your own SAS data set viewer using PowerShell - The SAS Dummy
Pingback: Using Windows PowerShell to view your SAS data dictionary
Pingback: My favorite SAS tips and tools from 2012 - The SAS Dummy
Pingback: Using Windows PowerShell to connect to a SAS Metadata Server - The SAS Dummy
Pingback: Using Windows PowerShell to find registered tables and columns in SAS metadata - The SAS Dummy
Pingback: Using Windows PowerShell to connect to a SAS Workspace server - The SAS Dummy
Pingback: Using PowerShell to download a file from a SAS Workspace
Chris,
Thanks a lot for this informtion. I am using EG4.0 on AIX, and I wont be able to use crontab or shell scripting in my environment. I tried to schedule my project in EG scheduler and also tried to schedule the VB script that EG has created using windowns scheduler. I was able to run my programs fine. but, job wont run some times, and I am thinking to look for other alternatives, and I was wondering if I can use windos powershell to schdule my eg projectsm and run the programs in AIX environment. Is it possible go this path or Should I be in PC SAS environment? Please provide some exmples of power shell if the jobs can be schedules this way. Thanks in advance for your help.
Jeeth,
I have several PowerShell examples within this paper about SAS Enterprise Guide automation. Also, if you don't need the full SAS Enterprise Guide features for a job but instead simply need to run a SAS program, you can use PowerShell with SAS Integration Technologies.
We have a SQL job that runs a ps1 and then emails the output to us. It runs 100 times a day no problem but then fails like 2x a day with the PSSecurityexception. Any idea why this is happening? We have tried everything we can think of...
If your job runs on a cluster of machines, make sure they are all configured the same with the Set-ExecutionPolicy. Also, is it possible that the account that runs the script occasionally runs into a permission problem when trying to read or write something? The PSSecurityException can trigger when the Authorization Manager fails something, and that can happen if your account is locked out for permissions. For example -- if the file or database that your script has to access has an exclusive lock?
Hi Chris - The WIKI page states: Working examples in Microsoft .NET (including C# and Visual Basic .NET) as well as Windows PowerShell are also provided..
I am attempting to create a VB.Net App for Windows 7 that will allow a non-SAS user to click a button that will run a precreated SAS Program on a Workspace Server object.
I am unable to find a VB.Net working example. Could you perhaps steer me in the right direction please?
The plan I have is to pass a CustomerID into a SAS Program using a %Let CustID = 12345; (Picked up from the VB.Net Text Box)
followed by an Include statement that points to a SAS Program somewhere on the SAS Grid (Linux) folder : %include '/mnt/myfolder/mysasprogram.sas';
The idea is to wait for the program to finish running, and then once I receive some notification that the program is done running, to close the Workspace Object and the VB.Net app.
Is this do-able from your perspective? Is there a working example in VB.net I can study please?
Len van den Berg
Len,
Yes, it's doable. The closest example that I have is in C#, and you can find that source code on my GitHub repository for SASHarness.
I hear that you would prefer a VB.NET example, but here's a tip: you can use a tool like ILSpy to examine the SasHarness.exe and "de-compile" into VB. I haven't had much luck converting a project completely this way, but you can use the code view to examine particular classes and see how things might work in VB.NET. The decompiled code isn't the same as a person would write, but if you're struggling with C# this might be enough to get you over the hump.
Hi, I am trying to write a powershell script to kick off a SAS Enterprise Guide project for users who don't have SAS or Enterprise Guide installed. I want them to be able to click on a Powershell file or batch file and run the job. Is this possible with Powershell?
Scott, these PowerShell scripts automate SAS or SAS Enterprise Guide, so something has to be installed in order to make them work.
You could install SAS Integration Technologies client (free download) and automate that way -- here is an example. Or maybe you're thinking that you can have the user's script Remote into another machine that has the SAS software installed, and run the process that way?
Hi, yes. My thought is that a powershell script would point to the remote machine to kick off a particular process flow of a EG project. EG would still be on that machine but they wouldn't have to open it up and use it. Also, I could use scheduler to run the script if I ever wanted to schedule it that way.
I think it might be possible, but the script and user would need remote-execution privileges on that remote machine. The process would need to be running on the remote machine, since that's where EG is installed/running. It would be sort of like logging into the machine with a remote shell and running a script.
Chris, yes... they will be logging into the remote machine. All they will need to do is run the powershell file to kick off the proper project and process flow.
Then that should work! As long as they have the privilege to run PowerShell scripts, of course.
Hi Chris:
As always, very useful.
I need an advice: we are developing with Enterprise Guide, and now we have a big number of embeded SAS programs. What would be the best option to put all this work to run in batch mode, in UNIX? Export SAS code from projects? Save all code nodes externally?
Thanks,
I also run many programs in batch, scheduled via cron, on SAS for Linux. I use EG to develop and maintain these programs and test them interactively. I save the SAS programs as external files on a shared network space that my account can access via Windows or Linux. (However, if I didn't have that shared space I could use the Copy Files task to move files as needed.)
That's what I recommend -- use EG to prototype the process, iterate as much as needed to get it right, then combine into one or more SAS programs that you can deploy as batch jobs.
Hi Chris:
Thanks for your advice, which I will definitely follow.
When you say "combine into one or more SAS programs ", may be you use, in some cases, some VB or PowerShell scripting to get the code of a particular process flow or group of tasks? (I´m thinking on several of your papers about SAS Enterprise Guide Automation).
Thanks...
Jaime, yes, you can definitely do that as long as those tasks/programs all have SAS code behind them that represent all of the work that needs to happen. Beware those few tasks (like Import Data or E-mail as a Step) that actually rely on the EG application to do some of the work, and don't have instructions in SAS code.
Pingback: Using Windows PowerShell to connect to a SAS Metadata Server - The SAS Dummy
Pingback: Build your own SAS data set viewer using PowerShell - The SAS Dummy