automation

7月 172018
 

Automation for SAS Administrators - deleting old filesAttention SAS administrators! When running SAS batch jobs on schedule (or manually), they usually produce date-stamped SAS logs which are essential for automated system maintenance and troubleshooting. Similar log files have been created by various SAS infrastructure services (Metadata server, Mid-tier servers, etc.) However, as time goes on, the relevance of such logs diminishes while clutter stockpiles. In some cases, this may even lead to disk space problems.

There are multiple ways to solve this problem, either by deleting older log files or by stashing them away for auditing purposes (zipping and archiving). One solution would be using Unix/Linux or Windows scripts run on schedule. The other is much "SAS-sier."

Let SAS clean up its "mess"

We are going to write a SAS code that you can run manually or on schedule, which for a specified directory (folder) deletes all .log files that are older than 30 days.
First, we need to capture the contents of that directory, then select those file names with extension .log, and finally, subset that file selection to a sub-list where Date Modified is less than Today's Date minus 30 days.

Perhaps the easiest way to get the contents of a directory is by using the X statement (submitting DOS’ DIR command from within SAS with a pipe (>) option, e.g.

x 'dir > dirlist.txt';

or using pipe option in the filename statement:

filename DIRLIST pipe 'dir "C:\Documents and Settings"';

However, SAS administrators know that in many organizations, due to cyber-security concerns IT department policies do not allow enabling the X statement by setting SAS XCMD system option to NOXCMD (XCMD system option for Unix). This is usually done system-wide for the whole SAS Enterprise client-server installation via SAS configuration. In this case, no operating system command can be executed from within SAS. Try running any X statement in your environment; if it is disabled you will get the following ERROR in the SAS log:

ERROR: Shell escape is not valid in this SAS session.

To avoid that potential roadblock, we’ll use a different technique of capturing the contents of a directory along with file date stamps.

Macro to delete old log files in a directory/folder

The following SAS macro cleans up a Unix directory or a Windows folder removing old .log files. I must admit that this statement is a little misleading. The macro is much more powerful. Not only it can delete old .log files, it can remove ANY file types specified by their extension.

%macro mr_clean(dirpath=,dayskeep=30,ext=.log);
   data _null_;
      length memname $256;
      deldate = today() - &dayskeep;
      rc = filename('indir',"&dirpath");
      did = dopen('indir');
      if did then
      do i=1 to dnum(did);
         memname = dread(did,i);
         if reverse(trim(memname)) ^=: reverse("&ext") then continue;
         rc = filename('inmem',"&dirpath/"!!memname);
         fid = fopen('inmem');
         if fid then 
         do;
            moddate = input(finfo(fid,'Last Modified'),date9.);
            rc = fclose(fid);
            if . < moddate <= deldate then rc = fdelete('inmem');
         end;
      end; 
      rc = dclose(did);
      rc = filename('inmem');
      rc = filename('indir');
   run;
%mend mr_clean;

This macro has 3 parameters:

  • dirpath - directory path (required);
  • dayskeep - days to keep (optional, default 30);
  • ext - file extension (optional, default .log).

This macro works in both Windows and Linux/Unix environments. Please note that dirpath and ext parameter values are case-sensitive.

Here are examples of the macro invocation:

1. Using defaults

%let dir_to_clean = C:\PROJECTS\Automatically deleting old SAS logs\Logs;
%mr_clean(dirpath=&dir_to_clean)

With this macro call, all files with extension .log (default) which are older than 30 days (default) will be deleted from the specified directory.

2. Using default extension

%let dir_to_clean = C:\PROJECTS\Automatically deleting old SAS logs\Logs;
%mr_clean(dirpath=&dir_to_clean,dayskeep=20)

With this macro call, all files with extension .log (default) which are older than 20 days will be deleted from the specified directory.

3. Using explicit parameters

%let dir_to_clean = C:\PROJECTS\Automatically deleting old SAS logs\Logs;
%mr_clean(dirpath=&dir_to_clean,dayskeep=10,ext=.xls)

With this macro call, all files with extension .xls (Excel files) which are older than 10 days will be deleted from the specified directory.

Old file deletion SAS macro code explanation

The above SAS macro logic and actions are done within a single data _NULL_ step. First, we calculate the date from which file deletion starts (going back) deldate = today() - &dayskeep. Then we assign fileref indir to the specified directory &dirpath:

rc = filename('indir',"&dirpath");

Then we open that directory:

did = dopen('indir');

and if it opened successfully (did>0) we loop through its members which can be either files or directories:

do i=1 to dnum(did);

In that loop, first we grab the directory member name:

memname = dread(did,i);

and look for our candidates for deletion, i.e., determine if that name (memname) ends with "&ext". In order to do that we reverse both character strings and compare their first characters. If they don’t match (^=: operator) then we are not going to touch that member - the continue statement skips to the end of the loop. If they do match it means that the member name does end with "&ext" and it’s a candidate for deletion. We assign fileref inmem to that member:

rc = filename('inmem',"&dirpath/"!!memname);

Note that forward slash (/) Unix/Linux path separator in the above statement is also a valid path separator in Windows. Windows will convert it to back slash (\) for display purposes, but it interprets forward slash as a valid path separator along with back slash.
Then we open that file using fopen function:

fid = fopen('inmem');

If inmem is a directory, the opening will fail (fid=0) and we will skip the following do-group that is responsible for the file deletion. If it is file and is opened successfully (fid>0) then we go through the deletion do-group where we first grab the file Last Modified date as moddate, close the file, and if moddate <= deldate we delete that file:

rc = fdelete('inmem');

Then we close the directory and un-assign filerefs for the members and directory itself.

Deleting old files across multiple directories/folders

Macro %mr_clean is flexible enough to address various SAS administrators needs. You can use this macro to delete old files of various types across multiple directories/folders. First, let’s create a driver table as follows:

data delete_instructions;
   length days 8 extn $9 path $256;
   infile datalines truncover;
   input days 1-2 extn $ 4-12 path $ 14-270;
   datalines;
30 .log      C:\PROJECTS\Automatically deleting old files\Logs1
20 .log      C:\PROJECTS\Automatically deleting old files\Logs2
25 .txt      C:\PROJECTS\Automatically deleting old files\Texts
35 .xls      C:\PROJECTS\Automatically deleting old files\Excel
30 .sas7bdat C:\PROJECTS\Automatically deleting old files\SAS_Backups
;

This driver table specifies how many days to keep files of certain extensions in each directory. In this example, perhaps the most beneficial deletion applies to the SAS_Backups folder since it contains SAS data tables (extension .sas7bdat). Data files typically have much larger size than SAS log files, and therefore their deletion frees up much more of the valuable disk space.

Then we can use this driver table to loop through its observations and dynamically build macro invocations using CALL EXECUTE:

data _null_;
   set delete_instructions;
   s = cats('%nrstr(%mr_clean(dirpath=',path,',dayskeep=',days,',ext=',extn,'))');
   call execute(s);
run;

Alternatively, we can use DOSUBL() function to dynamically execute our macro at every iteration of the driver table:

data _null_;
   set delete_instructions;
   s = cats('%mr_clean(dirpath=',path,',dayskeep=',days,',ext=',extn,')');
   rc = dosubl(s);
run;

Put it on autopilot

When it comes to cleaning your old files (logs, backups, etc.), the best practice for SAS administrators is to schedule your cleaning job to automatically run on a regular basis. Then you can forget about this chore around your "SAS house" as %mr_clean macro will do it quietly for you without the noise and fuss of a Roomba.

Your turn, SAS administrators

Would you use this approach in your SAS environment? Any suggestions for improvement? How do you deal with old log files? Other old files? Please share below.

SAS administrators tip: Automatically deleting old SAS logs was published on SAS Users.

5月 312018
 

Called out as two common IT threads in my December blog post, how do artificial intelligence and automation connect with another prominent movement, the Internet of Things (IoT)? First, consider these 2017 predictions in the IDC FutureScape on IoT. By 2019, At least 40 percent of IoT-created data will be stored, processed, analyzed [...]

Toward the artificial intelligence of things was published on SAS Voices by Oliver Schabenberger

1月 192018
 

Technology is changing rapidly: autonomous vehicles, connected devices, digital transformation, the Internet of Things (IoT), machine learning, artificial intelligence (AI), automation. The list goes on. And it has only begun. I do not try to predict the future. Instead, I examine the trends in technology and look for disruptive forces [...]

Two tech trends shaping 2018 and beyond was published on SAS Voices by Oliver Schabenberger

9月 152017
 

Editor's note: Tiffany Carpenter, Head of Customer Intelligence, SAS UK & Ireland sizes up the benefits of the two technologies and offers up a solution to businesses wanting the best of both. With constant pressure on profit margins, organisations need to strike a balance between improving cost efficiencies and customer [...]

Measuring up: robotic process automation versus real-time decision making was published on Customer Intelligence Blog.

6月 082012
 

If you are like many SAS Enterprise Guide users, you've amassed a large collection of project files (EGP files) that contain important content: programs, logs, notes, results, and more.

However, to most tools and processes, the EGP file is opaque. That is, you can't see what's inside of it unless you actually open it within SAS Enterprise Guide. And then you might have to poke around for a bit, opening various items until you find the one you're looking for.

What if you had a way to search your collection of EGP files, without opening them, to find all occurrences of any text that you specify? Would that rock your world?

Introducing...the EG-Project-Search-inator! (That's my name for it, as I've been watching a lot of Phineas and Ferb lately and so I'm inspired by Dr. Doofenshmirtz's naming conventions. With this, you can search every project file in the Tri-State area!)

The more-mundane-and-less-cool name is EGPSearch, and I built it as an example for my SAS Global Forum paper. It shows how you can use Microsoft .NET to automate SAS Enterprise Guide. But it also turned out to be a very handy tool.

>> DOWNLOAD link for the EGPSearch tool (SAS Enterprise Guide 4.3 only)
The download is a ZIP archive with the EXE and the Microsoft .NET source code that I used to build it.

It's simple to use. You place the EGPSearch43.exe anywhere on a machine that already has SAS Enterprise Guide 4.3. Then run EGPSearch43.exe to start it. Here's a screenshot of the user interface:

You specify a project file to search (as a fully qualified path name) or a collection of projects in a folder (using wildcards, such as "C:\Projects\*.egp"). Then specify the text that you want to search for.

The EG-Project-Search-inator will use the SAS Enterprise Guide automation API to open each EGP file and search for the text. The search will include all programs (including task-generated code), log output, notes, and process flow labels. The search is case-insensitive, so searching for "libname", "LIBNAME", or "libNAME" will find all of the same content. All of the results ("hits") will be displayed as a list in the main window. The log area at the bottom will show you which project files were opened and searched successfully.

With a tool like this, you can begin to answer all sorts of questions, such as:

  • Where am I using a LIBNAME statement in my code? (Administrators would love to know!)
  • I say that I love PROC SGPLOT, but how much do I really use it?
  • What projects have ERROR lines in the logs? How about WARNING lines?

If you have burning questions that you want to pose to the Search-inator, download it today and give it a try.

Important notes about the EG-Project-Search-inator!

Before you download this tool, there are a few things you should know.

This tool is provided as an example, and isn't supported by SAS Technical Support. If you have questions or feedback about the tool (such as "Works great!" or "Curse you, Perry the Platypus!"), please tell me in the comments.

It works only with SAS Enterprise Guide 4.3. I'm working on a 5.1 version, but I'm not ready to release it yet. The 4.3 version might have some luck with project files from 4.1 or 4.2, but don't count on it. And because it uses the Version 4.3 automation APIs, you must have SAS Enterprise Guide 4.3 installed on your machine.

You might see a bunch of files/folders that are created during a search, but that don't get cleaned up. As a by-product of searching a project file, some of the project internal files are copied to a temporary folder for searching, and then are removed when the search is complete. However, some users have seen these files land in the current working folder (which not always a TEMP folder), and then remain even after closing the tool.

Even when you close the tool, the EGPSearch43.EXE process might not end immediately. This is most likely to happen when there is an error trying to open one or more EGP files from a folder. This doesn't hurt anything, but you might find that one of your EGP files is "locked for read" if you try to open it in the full SAS Enterprise Guide interface. Simply use Windows Task Manager to end the EGPSearch43.EXE process, which will free the file.

You should install the most recent SAS Enterprise Guide 4.3 hotfixes, as there are fixes that may help the tool to behave better, and might avoid the issues I just described.

tags: .net, automation, EG-Project-Search-inator, SAS Enterprise Guide
4月 172012
 

Have you ever selected File->Schedule Project or Schedule->Process Flow in SAS Enterprise Guide? Are you curious about what magic these actions will trigger?

Here's what happens:

  • SAS Enterprise Guide creates a VBScript program that contains the instructions to start SAS Enterprise Guide, load your project, run your project or flow, and save the results.
  • SAS Enterprise Guide launches the Windows Task Scheduler interface, so that you can specify when and how often to run your project/flow unattended.
  • The scheduled task contains the command (CSCRIPT.EXE) and a reference to the VBScript (VBS file), which Windows will run at the appointed time. (Learn more about this mechanism at "Using SAS Enterprise Guide to run programs in batch".)

The VBScript instructions drive the SAS Enterprise Guide automation API, and that API is capable of much more than simply loading and running your project. And you aren't stuck with VBScript -- you can access the automation API using Windows PowerShell and Microsoft .NET.

As the following diagram shows, the automation interface is a peer to the main user interface for SAS Enterprise Guide. Like the happy gentleman pictured at the top of the diagram, most users will interact with the main windows of SAS Enterprise Guide. Users gain access to this user interface by using the primary executable (SEGuide.exe), often by way of a desktop shortcut icon. With automation,you forgo the SAS Enterprise Guide user interface entirely, and you instead script every action using the automation API.

These are the concepts and examples that are the subject of my SAS Global Forum 2012 paper, Not Just for Scheduling: Doing More with SAS Enterprise Guide Automation. I will present this topic at the conference on Wednesday morning at 8am (with coffee in hand, most likely).

Check out my summary page on sasCommunity.org for links to the paper, blog posts, and several examples. The examples include:

  • A VBScript example that can extract all of the SAS programs and SAS logs from your project file.
  • A PowerShell example to create a simple listing of all of the tasks and input data within your project file.
  • A Microsoft .NET example (implemented with C#) that allows you to search for any text within your project file.

That last one is of special interest (and worthy of a separate blog post later). Even if the automation API isn't your thing, you might enjoy the EGPSearch example, which allows you to search a collection of SAS Enterprise Guide project files for any text within your SAS programs, logs, notes and more.

tags: .net, automation, PowerShell, SAS global forum, SAS GloFo, sasCommunity.org, scripting, vbscript
2月 032012
 

We call it the "metadata profile", but really it's like a telephone number that connects you to your SAS environment. Just as a telephone number has component parts (country code, area code, exchange), the metadata profile contains information that allow you to "dial in" to your SAS servers. This information includes:

  • a host name (node name or IP address of your SAS metadata server)
  • a port number (the TCP/IP port that the SAS metadata server uses to accept connections)
  • your credentials (the user ID and password that identify you when you connect)

All of this is captured under a friendly name, which you assign when you define the profile.  This is similar to the way that you would file a colleague's information in your Rolodex (still have that?) or contacts list.

When you're connected to a SAS environment in SAS Enterprise Guide, you'll see some of that profile information reflected in the status bar:

To activate a different profile or to define a new one, you can click on the Connections link in the status bar. This invokes the Connections window, showing all of your available profiles and their details.

The profile name is important, because you can make use of this name as a shortcut (similar to "speed dial") to direct SAS Enterprise Guide to a particular metadata environment automatically.  To learn more, read about how to use SAS Enterprise Guide with different SAS environments.

You can also use the profile name in SAS Enterprise Guide automation scripts, as shown in this example about running SAS programs in batch.

My objective in this blog post (and I'm taking a while to get to it) is to show how to use an automation script to list the available profiles that you have defined for your installation of SAS Enterprise Guide.  In this VBScript program, I use the Application.Profiles collection to obtain the details of each defined profile.  To set an active profile in script, use the Application.SetActiveProfile() method.  Note that if you want to run in "no profile" mode (not connected to metadata, simply using your local SAS installation), you specify "Null Provider" as the profile name.  (Aside: "Null Provider" would make a great name for a rock band, or maybe for a blog.)

Here's the VBScript program:

' force declaration of variables in VB Script
Option Explicit
Dim Application
' Create a new SAS Enterprise Guide automation session
Set Application = WScript.CreateObject("SASEGObjectModel.Application.4.3")
WScript.Echo Application.Name & ", Version: " & Application.Version
 
' Discover the available profiles that are defined for the current user
Dim i 
Dim oShell
Set oShell = CreateObject( "WScript.Shell" )
WScript.Echo "Metadata profiles available for " _
   & oShell.ExpandEnvironmentStrings("%UserName%")
WScript.Echo "----------------------------------------"
For i = 1 to Application.Profiles.Count-1
  WScript.Echo "Profile available: " _
    & Application.Profiles.Item(i).Name _
    & ", Host: " & Application.Profiles.Item(i).HostName _
    & ", Port: " & Application.Profiles.Item(i).Port
Next
Application.Quit

And here's an example of the output:

C:\Examples>cscript ShowProfiles.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

Enterprise Guide, Version: 4.3.0.0
Metadata profiles available for sascrh
----------------------------------------
Profile available: Null Provider, Host: , Port:
Profile available: t2817, Host: t2817, Port: 8562
Profile available: uitsrv02, Host: uitsrv02.na.sas.com, Port: 8561
Profile available: uitsrv04, Host: uitsrv04.na.sas.com, Port: 8561

 
tags: automation, SAS Enterprise Guide, scripting, vbscript
11月 022011
 

This blog post is a "mashup" of a couple of my previous posts, combining the lessons to create something brand new that I hope you will find useful.  First, let's review what we know:

It's not a big leap to realize something that we scripting geeks will find very cool: we can use Windows PowerShell to control SAS Enterprise Guide.

In this example, we'll look at a script that can process one or more project files (.EGP files) and report on what's in them.  That's right: there is no need to open SAS Enterprise Guide to peek inside the project.  We can write a script that pulls the information out of the project for us.

Here is a copy of the script that you can download.  To follow along with the instructions in the rest of this post, you should download/copy the file and name it "eg43ProjectContents.ps1" (drop the TXT extension, which I used to make it safe for download).  The example is designed with work with SAS Enterprise Guide 4.3.

To run the script, you must invoke the powershell command or run the script from within a PowerShell console window.  (Remember, before you can run PowerShell scripts you must enable your Windows environment to allow it.)

IMPORTANT: Because SAS Enterprise Guide is a 32-bit application, you must run the 32-bit version of PowerShell with this script. See this post for more information about how to locate the 32-bit version even when running on 64-bit Windows.

Here is an example command that will report on a single EGP file:

powershell -command "ls c:\projects\DonorsChoose.egp | c:\Scripts\eg43ProjectContents.ps1"

To report on an entire directory of EGP files, simply change the command to:

powershell -command "ls c:\projects\*.egp | c:\Scripts\eg43ProjectContents.ps1"

Here is a partial example of the output for a particular project.  The output is emitted to the console, or STDOUT, but you can easily capture it in a file by redirecting the output.

Examining project: C:\Projects\DonorsChoose.egp
Process flows within project:
   Data Descriptors
     SASHELP VTABLE  ( SAS.EG.Scripting.Data )
     SASHELP VCOLUMN  ( SAS.EG.Scripting.Data )
     Data Description  ( SAS.EG.Scripting.Report )
     Data Imported from donorschoose-org-1may2011-v1-donations.csv  ( SAS.EG.Scripting.Data )
       Characterize Data  ( SAS.EG.Scripting.Task )
     Data Imported from donorschoose-org-1may2011-v1-projects.csv  ( SAS.EG.Scripting.Data )
       Characterize Data  ( SAS.EG.Scripting.Task )
     Data Imported from donorschoose-org-1may2011-v1-resources.csv  ( SAS.EG.Scripting.Data )
       Characterize Data  ( SAS.EG.Scripting.Task )
     Data Description  ( SAS.EG.Scripting.Query )
     Column descriptions  ( SAS.EG.Scripting.Query )
     Link to Data Imported from donorschoose-org-1may2011-v1-donations.csv  ( SAS.EG.Scripting.Link )
     Link to Data Imported from donorschoose-org-1may2011-v1-projects.csv  ( SAS.EG.Scripting.Link )
     Link to Data Imported from donorschoose-org-1may2011-v1-resources.csv  ( SAS.EG.Scripting.Link )
     Link to SASHELP VTABLE  ( SAS.EG.Scripting.Link )
     Link to SASHELP VCOLUMN  ( SAS.EG.Scripting.Link )
   About the data
     Normalized Data Schema  ( SAS.EG.Scripting.File )
     Data Schema  ( SAS.EG.Scripting.File )

You can view the full output for this example here.

The example script contains many code comments to help you to understand what's going on, but here are a few highlights. To "invoke" the SAS Enterprise Guide scripting object, you use PowerShell commands to create a COM object that represents the application:

# Launch the application using the registered prog ID
$eguideApp = New-Object -comObject SASEGObjectModel.Application.4.3 

To load a project into the application, use the Open() method (which accepts two arguments: a file name and optionally, a password -- for protected project files). Because this script accepts a list of files piped in from the command invocation, the candidate files are stored in the special $input variable:

foreach ($projName in $input)
{
 # Open the project file
 Write-Host " --------------------------------------------- "
 Write-Host "Examining project:" $projName
 $project = $eguideApp.Open("$projName", "")

 # all other processing for this project
 # ....
 # Close the project file
 $project.Close()
}

To iterate through each process flow within a project, use the ContainerCollection property on the Project object. Note that there are different types of top-level "containers" in the object model. The Process Flow is the most common, but there are also containers for Reports, Distribution (such as Send E-mail steps) and Ordered Lists. The Process Flow has a ContainerType of 0.

# Show all of the process flows in the project
$pfCollection = $project.ContainerCollection
Write-Host "Process flows within project:"
foreach ($pf in $pfCollection)
{
  if ($pf.ContainerType -eq 0)
  {
    Write-Host "  " $pf.Name
    # To RUN a process flow, simply use $pf.Run()
  }
}

Note: As you review the output from the scripting model, you might notice that the objects are not reported in the same sequence that you expect to find them visually when you view the project in SAS Enterprise Guide. For example, the Process Flow objects are not always reported in the same order that you see them in the project tree. Alas, the SAS Enterprise Guide automation model does not offer a completely accurate representation of the object arrangement in your project and process flows, only the content.

To write your own scripts, you will need the reference documentation for the SAS Enterprise Guide object model. That's available here, in a CHM help file contained within a ZIP file. (Note that this is the version 4.2 documentation, but the object model is the same in 4.3. To view the contents of the CHM file, you may need to "unblock" it within your Windows environment.)

tags: automation, batch processing, PowerShell, SAS Enterprise Guide, scripting
9月 122011
 

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).
tags: automation, PowerShell, sas administration
5月 032011
 
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.

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