10 01 2011

On board and ready to take-off for Richmond…





10 01 2011

Waiting to board at Dallas/Ft Worth airport and hoping the weather holds up in Richmond today and tomorrow…





Sending Your SAS Results through Email – An Introduction

16 09 2009

An Introduction to Sending Emails through SAS

I often deal with large amounts of data, which, by its very nature, portends a large amount of run-time. As a result, it is often handy to send myself or others an email when executing these kind of processes to indicate either success or failure. Fortunately, there is a way to do this using SAS. Unfortunately, SAS does not always play nice with email applications (in my case Outlook) and requires some setup before emails can be sent from SAS without a hassle.

The primary concern is a dialog that Microsoft Outlook displays that prevents emails from being sent automatically and restricts access to your email client. In Microsoft’s push to provide better security throughout its software line-up, this dialog was added to prevent other applications from sending emails without the user knowing it. Unless you can suppress this dialog, you cannot send emails automatically, use HTML as your default delivery method, or attach files.

So how do you get around this? SAS has provided several options that can be used in your configuration file to directly specify your email server. With the correct setup in place, SAS will use SMTP (an email protocol) to send your email instead, which suppresses interaction with Outlook in favor of a more secure and more direct method.

Throughout the rest of this article, I will explore the following points:

  1. How to setup SAS to send email automatically
  2. How to send an email using SAS
  3. How to send a different email for success and failure
  4. How to send an email with a report in the message portion of the email
  5. How to send an email with an attachment

Properly Setting Up SAS for Email

Fortunately, the task of setting up SAS to send Emails is relatively simple. Just follow the steps below. In addition to editing the SAS configuration file, you will have to retrieve your server settings from outlook as well:

Retrieving Outlook Server Settings

  1. Open Outlook
  2. Navigate to ToolsàEmail Accounts…
  3. Select View or change existing e-mail accounts
  4. Click Next
  5. Select your primary email account from the listbox labeled Outlook processes e-mail for these accounts in the following order
  6. Click the button labeled Change…
  7. Copy or write down the path for your Exchange server from the textbox labeled Microsoft Exchange Server
  8. Click Cancel and exit out of the E-Mail Accounts dialog

Retrieving the Path to your SAS Configuration File

  1. Open SAS
  2. Navigate to ToolsàOptionsàSystem…
  3. You should see a tree structure similar to a directory structure that provides access to SAS options
  4. Click on the plus sign next to System Administration
  5. Click on Installation
  6. Look under the option labeled Config and copy down the path to your configuration file
  7. Click Cancel and exit out of SAS

NOTE: Alternatively, you can run the following code to retrieve the location of the config file:

proc options option=config;
run;
proc options option=config;run;

Setting Email Options in the Configuration File

  1. Click on StartàRun…
  2. Type Explorer into the textbox labeled Open
  3. Explorer will come up
  4. Paste or type the full path to the config file in the Address bar
  5. A dialog will probably come up labeled Caution. The dialog may warn you that you are opening a Microsoft Outlook Configuration file. Of course, this is not the case, so click Open With…
  6. Windows will tell you that it cannot open this file
  7. Click on Select the Program from a List
  8. A dialog box labeled Open With will appear. Select Notepad from the list and click Ok
  9. The configuration file will open in Notepad. At this point, it is not a bad idea to make a backup and I would encourage you to do so
  10. Scroll down to the bottom of the configuration file and add the lines at the end of this step, replacing <Email Host> with the host name that you copied down earlier from Outlook
  11. Save the file and exit out of Notepad
  12. You will need to restart SAS before the changes will take effect
/* Set Email Parameters */

-emailhost=<Email Host>
-emailsys=SMTP

Sending Email from SAS

SAS provides a full-featured set of options that you can use to send emails from a SAS program. We won’t touch on most of these options, but there are a few key options that you should become familiar with. Here is a brief summary of the key options, which you will see implemented later in the article:

  1. Subject – Subject line for the email
  2. From – The From address
  3. To – The To address or addresses. You can specify multiple addresses by enclosing the addresses in parentheses and delimiting each address with a space. Each address should also be enclosed in double quotation marks.
  4. CC – The CC option can be used to specify recipients to CC (Not shown in the article)
  5. CT – CT stands for content-type and is required to send email as HTML
  6. Type – Type is similar to CT and specifies the content-type. I would generally recommend that you set both values to the same type
  7. Attach – Attach can be used to attach files. You can attach multiple files using the same approach that is used to specify multiple To addresses

Setting Up Your Options

Generally, I like to setup options for any SAS programs I code using %let statements at the top of the program. This ensures that the program is as flexible as possible and that I can test multiple configurations easily. We will do the same for this exercise. To setup your environment, run the code below before executing any of the samples provided further down in the article. Be sure to replace the <email address> placeholder with an email address that you would like to use to send from and send to:

** Define input macro variables;

%let subject = %str(Email configuration test);
%let fromEmail = '<Email Address>';
%let toEmail = '<Email Address>';
%let successMsg = %str(Your code was successful! This is a test of your email configuration);
%let errorMsg = %str(Your code failed! This is a test of your email configuration);

** Define output path (DO NOT EDIT);

%let path = C:\documents and settings\&sysuserid.\my documents\test.htm;

The Basics of Sending Email from SAS

The cornerstone of sending email from SAS is the filename statement. This statement is typically used to define a shortcut to an external file, but can also be used to define a shortcut to email or FTP or another destination. The example shows a typical filename statement for email. Notice the use of the word EMAIL after the name of the destination:

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
                CT ='text/html';

This statement can be used in combination with a _Null_ data step (a data step that does not produce a SAS dataset) to send out an email as shown below:

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
                CT ='text/html';

** Email the report to the output filename destination;

data _NULL_;
	file output;
	put "&successMsg.";
run;

The file statement directs the specified output to an “external” file, in this case an email. In addition, put is used to output the body of the email. You could also have multiple put statements if you wanted to and use put to place breaks between lines. Complex formatting is possible.

You might also want to vary the message sent depending on the results of your program. The statement below simplifies things a little bit, as you would normally went to collect errors globally, but this still serves as a good example:

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
                CT ='text/html';

** Email the report to the output filename destination;

data _NULL_;
	_error_ >= 1;
	if _error_ = 1 then do;
		file output;
		put "&errorMsg.";
	end;
	else do;
		file output;
		put "&successMsg.";
	end;
run;

Sending an Email with an Embedded HTML Report

It is quite possible to send an email with an embedded HTML report. This could be the results from a PROC PRINT or a PROC REPORT or even the outputs from PROC LOGISTIC or other statistical procedures. The code below gives an example of how this can be done. Please note that some of the portions of the code are simply used to generate a sample dataset and are not required for use in your own code:

** Create sample data... Not necessary typically;

data temperatures;
   do centigrade = -40 to 100 by 10;
      fahrenheit = centigrade*9/5+32;
      output;
   end;
run;

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
		    type='text/html'
                CT ='text/html';

** Print out the report so you can see what is going to be sent;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

** Set the ods destination to HTML;
** The body option should be equal to the name of the filename statement;

ods html
  body=OUTPUT
  style=sasweb;

** Print the report to the ODS destination;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

ods html close;

There are two key portions of the code above that need to be pointed out. First of all, CT and Type are critical options for the Filename statement and must be included in order for the output to display correctly. Both options should always be set to text/html. Second of all, when using the ods statement to output HTML, the body option must be set to the name of the filename that you specified for your email output. Otherwise, you could replace the proc print with any other proc that produces output and have that output sent to the email addresses you specify.

Sending an Email with an Attached HTML Report

It is also possible to send an email with an attached HTML report. Of course, it is also possible to attach any other document type, such as an Excel spreadsheet or a PDF file, but, for now, we will use an example that is similar to the one used in the previous example. The example below demonstrates how to send an email from SAS with an attachment:

** Create sample data... Not necessary typically;

data temperatures;
   do centigrade = -40 to 100 by 10;
      fahrenheit = centigrade*9/5+32;
      output;
   end;
run;

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
				ATTACH = "&path."
				Type = 'text/html'
                CT = 'text/html';

** Print out the report so you can see what is going to be sent;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

** Set the ods destination to HTML;

ods html rs=none;

** Print the report to the ODS destination;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

ods html close;

** Email the report to the output filename destination;

data _NULL_;
	file output;
	put "&successMsg.";
run;

There are only three differences between the code shown above and the previous example. Generally, once you get the hang of these various approaches to sending email from SAS, it should be relatively easy to adapt the code shown for most of your needs:

  1. The attach option was added to the filename statement. This option specifies the file to attach to the email
  2. The body option was removed from the ods statement
  3. A _NULL_ data step was added to the end of the code. This data step is the statement that actually sends the email

Additional Resources

While the article should serve as a good foundation for sending email from SAS, there are a wealth of other resources available. Here are a couple of good places to start:

  1. Emailing from SAS Powerpoint –  An excellent overview with a lot of detail on how to send emails from SAS. Also includes some info on sending to non-Outlook email clients
  2. Automated Distribution of SAS Results – A SAS Global Forum paper on distributing SAS results through email. Includes some more advanced details

Full SAS Code for the Article

** Define input macro variables;

%let subject = %str(Email configuration test);
%let fromEmail = '<Email Address>';
%let toEmail = '<Email Address>';
%let successMsg = %str(Your code was successful! This is a test of your email configuration);
%let errorMsg = %str(Your code failed! This is a test of your email configuration);

** Define output path (DO NOT EDIT);

%let path = C:\documents and settings\&sysuserid.\my documents\test.htm;

/***************** Example 1 - Sending a simple email *******************/

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
                CT ='text/html';

** Email the report to the output filename destination;

data _NULL_;
	file output;
	put "&successMsg.";
run;

/***************** Example 2 - Sending an Email Based on the Error Value *******************/

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
                CT ='text/html';

** Email the report to the output filename destination;

data _NULL_;
	_error_ >= 1;
	if _error_ = 1 then do;
		file output;
		put "&errorMsg.";
	end;
	else do;
		file output;
		put "&successMsg.";
	end;
run;

/***************** Example 3 - Embedding an HTML Report within Your Email *******************/

** Create sample data... Not necessary typically;

data temperatures;
   do centigrade = -40 to 100 by 10;
      fahrenheit = centigrade*9/5+32;
      output;
   end;
run;

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
				type='text/html'
                CT ='text/html';

** Print out the report so you can see what is going to be sent;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

** Set the ods destination to HTML;
** The body option should be equal to the name of the filename statement;

ods html
  body=OUTPUT
  style=sasweb;

** Print the report to the ODS destination;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

ods html close;

/***************** Example 4 - Attaching an HTML Report within Your Email *******************/

** Create sample data... Not necessary typically;

data temperatures;
   do centigrade = -40 to 100 by 10;
      fahrenheit = centigrade*9/5+32;
      output;
   end;
run;

** Define your filename statement;

FILENAME OUTPUT EMAIL
                SUBJECT = "&subject."
                FROM = &fromEmail.
                TO = &toEmail.
				ATTACH = "&path."
				Type = 'text/html'
                CT = 'text/html';

** Print out the report so you can see what is going to be sent;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

** Set the ods destination to HTML;

ods html rs=none;

** Print the report to the ODS destination;

title 'Centigrade to Fahrenheit conversion table';
proc print;
  id  centigrade;
  var fahrenheit;
run;

ods html close;

** Email the report to the output filename destination;

data _NULL_;
	file output;
	put "&successMsg.";
run;






Using VBA to Execute a Remote Process

13 09 2009

It is not easy to connect to UNIX, execute a process, and return control to the calling program once the process is complete using VBA. There are more advanced ways to do this, but these techniques often involve using a third-party control or the winsock control, which is often limited by licensing concerns. Fortunately, there is a workaround for this.

The Problem

So what actually is the problem? Well, there are a few rather clunky ways to connect to a remote server from an Office application (in this case Excel), but there really isn’t a good way to effectively pause execution and wait for the remote process to complete.

The Workaround

A workaround for this is to utilize the Windows command line. This allows you to use the exec method of the WScript.Shell object to call a vbscript file from the command-line that connects to UNIX, while waiting for execution to complete.  What does this mean? Let’s layout the details step-by-step:

  1. Create the WScript.Shell object
  2. Call the exec method on the WScript.Shell object
  3. The exec method executes a vbscript file from the command-line that executes the remote process
  4. Cause Excel to sleep until the status of the WScript.Shell object changes to 1
  5. Return control to the calling application (Excel in this case)

So what does this actually look like in code? Here is a step-by-step overview:

Step 1 – Create the WScript.Shell Object

Set wshShell = CreateObject("WScript.Shell")

Step 2 – Call the exec method on the WScript.Shell Object

Set oExec = wshShell.Exec(execStr)

Step 3 – The exec method executes a vbscript file from the command-line that executes the remote process

This step is a little more in-depth. In order to make everything self-contained, your program should create a temporary vbscript file, which avoids the need to distribute a separate file if you need to share the Office application. To deal with this first piece, the code below can be leveraged:

Public Sub Create_File(strLocation As String)
Public Sub Create_File(strLocation As String) Dim fileText As String Dim inFile As Integer fileText = Sheets(1).Cells(5, 1).Value MsgBox fileText MsgBox strLocation Open strLocation For Append As #1 Print #1, fileText Close #1 End Sub

A couple of items to highlight… Notice the following line:

fileText = Sheets(1).Cells(5, 1).Value

This line retrieves the file text from the cell at the fifth row and first column on sheet 1. This is the code that is used in the file. Of course, there are other, perhaps better ways to do this, so feel free to experiment.

The text that is output to the external vbscript file opens a telnet connection to the remote server and sends several commands. There is a little bit of artistry involved in this since the calling program has to wait for the remote server to execute certain commands. The best way to handle this is through a sleep command. There are some additional error checks you could put in place, but I have employed this technique before and found that users rarely experience a problem:

Main

Sub Execute_Unix(strUserName, strPassword, strAdditional)
    Set wshShell = CreateObject("WScript.Shell")
    wshShell.Run "C:\WINDOWS\system32\cmd.exe"
    wscript.Sleep 1500
    wshShell.SendKeys "telnet <server>"
    wshShell.SendKeys "{Enter}"
    wscript.Sleep 1500
    wshShell.SendKeys strUserName
    wshShell.SendKeys "{Enter}"
    wscript.Sleep 1500
    wshShell.SendKeys strPassword
    wshShell.SendKeys "{Enter}"
    wscript.Sleep 1500
    wshShell.SendKeys "{~}"
    wshShell.SendKeys "<remote process location>"
    wshShell.SendKeys "{Enter}"
    wshShell.SendKeys "exit"
    wshShell.SendKeys "{enter}"
    wscript.Sleep 1500
    wshShell.SendKeys "exit"
    wshShell.SendKeys "{enter}"
    Set wshShell = Nothing
End Sub

Sub Main()
    Set objArgs = wscript.arguments
    If wscript.arguments.Count < 3 Then
        Exit Sub
    End If
    Execute_Unix wscript.arguments(0), wscript.arguments(1), wscript.arguments(2)
End Sub

There are several things to consider about this vbscript program. Here are some details:

  1. wshShell.SendKeys – Used to send explicit commands to the remote server
  2. wscript.Sleep – Pause execution for the specified number of milliseconds
  3. wscript.arguments – Used to retrieve command-line arguments. In this case, when this program is called using VBA in Excel, the several values are passed to the script, including the user name and password for the remote server

Using these commands, the program executes the following steps:

  1. Call the Windows command-line at cmd.exe
  2. Open the telnet program and specify the server to connect to
  3. Send the user name to the remote server
  4. Send the password to the remote server
  5. Call the remote process
  6. Once completed, exit from the proram

Conclusions

There are still several issues with this approach, most importantly that you have to accurately estimate how long it takes to run each step. Also, while I have not added it here, you should include ample comments and error-checking in your code. There are also other alternative approaches with the best option probably the use of a database accessible by both platforms to indicate execution status. By using this method, you can develop sophisticated applications that utilize advanced job control to execute tasks. In a future post, I will probably take this up, but for now, here is the full VBA code needed for this process. Note the split between a regular module and a class module:

Paste Into a Code Module in the VBA Editor

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Sub Exec_Unix_Old()
    Dim wshShell
    Dim strCommand As String
    Dim strUserName As String
    Dim strPassword As String
    Dim strFileName As String
    Dim cRetrieveSpecialFolder As New clsRetrieveSpecialFolder

    Application.ScreenUpdating = False

    strUserName = "<username>"
    strPassword = "<password>"
    strTable = ""

    strFileName = cRetrieveSpecialFolder.GetDocuments & "\db_monitoring_" & Format(Now(), "mm_dd_yy") & ".vbs"

    MsgBox strFileName

    Create_File strFileName

 Application.Cursor = xlWait
    Set wshShell = CreateObject("WScript.Shell")

    Dim oExec As Object
    Dim execStr As String

    execStr = "cscript " & Chr(34) & strFileName & Chr(34) & " " & strUserName & " " & strPassword & " " & strTable

    MsgBox execStr

    Set oExec = wshShell.Exec(execStr)
    While oExec.Status <> 1 ' Wait for process
        Sleep 1000
    Wend

    Set oExec = Nothing

    Set wshShell = Nothing

    MsgBox ("Unix macro complete!")

    Application.ScreenUpdating = True
    Application.Cursor = xlDefault

skipupass:

End Sub

Public Sub Create_File(strLocation As String)

    Dim fileText As String
    Dim inFile As Integer

    fileText = Sheets(1).Cells(5, 1).Value

    MsgBox fileText

    MsgBox strLocation

    Open strLocation For Append As #1

    Print #1, fileText

    Close #1

End Sub

Paste Into a Class Module – Note that the Code Below Retrieves the My Documents Folder for the User

Const CSIDL_DESKTOP = &H0
Const CSIDL_PROGRAMS = &H2
Const CSIDL_CONTROLS = &H3
Const CSIDL_PRINTERS = &H4
Const CSIDL_PERSONAL = &H5
Const CSIDL_FAVORITES = &H6
Const CSIDL_STARTUP = &H7
Const CSIDL_RECENT = &H8
Const CSIDL_SENDTO = &H9
Const CSIDL_BITBUCKET = &HA
Const CSIDL_STARTMENU = &HB
Const CSIDL_DESKTOPDIRECTORY = &H10
Const CSIDL_DRIVES = &H11
Const CSIDL_NETWORK = &H12
Const CSIDL_NETHOOD = &H13
Const CSIDL_FONTS = &H14
Const CSIDL_TEMPLATES = &H15
Const MAX_PATH = 260
Private Type SHITEMID
    cb As Long
    abID As Byte
End Type
Private Type ITEMIDLIST
    mkid As SHITEMID
End Type
Private Declare Function ShellAbout Lib "shell32.dll" Alias "ShellAboutA" (ByVal hwnd As Long, ByVal szApp As String, ByVal szOtherStuff As String, ByVal hIcon As Long) As Long
Private Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" (ByVal hwndOwner As Long, ByVal nFolder As Long, pidl As ITEMIDLIST) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Function GetSpecialfolder(CSIDL As Long) As String
    Dim r As Long
    Dim IDL As ITEMIDLIST
    'Get the special folder
    r = SHGetSpecialFolderLocation(100, CSIDL, IDL)
    If r = NOERROR Then
        'Create a buffer
        Path$ = Space$(512)
        'Get the path from the IDList
        r = SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal Path$)
        'Remove the unnecessary chr$(0)'s
        GetSpecialfolder = Left$(Path, InStr(Path, Chr$(0)) - 1)
        Exit Function
    End If
    GetSpecialfolder = ""
End Function

Public Function GetDocuments() As String
    GetDocuments = GetSpecialfolder(CSIDL_PERSONAL)
End Function




Introducing Coding Zen

12 09 2009

While this is my first post, I am not going to spend a lot of time on introductions. However, let me go ahead and set the stage for what this blog is all about. I spend a lot of time coding, not generally in traditional hardcore languages, such as C++ and Java, but in business support languages, such as VBA, VB and C# .net (maybe a little hardcore), SQL, SAS, and web programming using HTML, CSS, and the often under-rated language of the web, javascript. When it comes down to it, it isn’t the coding itself that is exciting or the language that I am coding in, but the problem solving process itself and the sense of accomplishment that comes from solving problems and creating a solution that overcomes an obstacle of somekind. I am also in a unique situation as what essentially amounts to a data analyst that works closely with the business everyday. This allows me to drive change in a way that our colleagues in IT often cannot.

So that brings me to what this blog is all about. In this space, I will share the lessons I have learned, the techniques and solutions that have helped me overcome the inevitable problems that crop up when using any kind of development platform, and, occasionally, I will discuss the business skills that lead to success. I hope you will respond to my posts and share your own experiences and even request help with your own problems. When I can I will respond and perhaps post on the issues that most often crop up.

In addition, if you are interested in more frequent updates, please feel free to check out my Twitter page at http://twitter.com/codeZenGuru . Thanks for reading and check back for updates!








Follow

Get every new post delivered to your Inbox.