Using SAS to Send Bulk Emails With Attachments

[Pages:13]Paper TT09

Using SAS to Send Bulk Emails With Attachments

Wenjie Wang, Pro Unlimited, Bridgewater, NJ Simon Lin, Merck Research Labs, Merck & Co., Inc., Rahway, NJ

ABSTRACT

In the business world, using emails to communicate is a common practice, and more and more business communication relies on e-mails. When you need to communicate your analysis results or data checking findings to numerous recipients on a regular basis, sending bulk e-mails could be a daunting task. With the SAS DATA step combined with SAS EMAIL Access Method, sending bulk e-mails becomes a breeze. This paper presents an easy and automated approach to send out bulk e-mails with attachments on a scheduled basis (e.g. Weekly, Monthly, etc) using SAS EMAIL Access Method. SAS configuration and SAS programs will be discussed for the use of this capability. A hypothetical example on how to utilize the EMAIL Access Method will be presented with detailed step-by-step procedures. The scripts can be modified as necessary to fit your business needs and some discussions on generalizing the script will be explored. This paper is targeted at an intermediate and advanced audience.

SAS Version 8.2 or 9.1, Windows XP, Intermediate or Advanced Level Key Words: SAS, DATA step, FILENAME statement, EMAIL Access Method.

INTRODUCTION

Sending out bulk e-mails with attachments to multiple recipients on a regular basis from e-mail software such as Microsoft Outlook is not an easy task and it is error-prone, since you could easily attached the wrong file to a wrong recipient. With SAS's EMAIL Access Method of FILENAME statement, you can automate the task with careful planning and simple implementation of SAS code.

SAS EMAIL ACCESS METHOD BASICS

In order to use the EMAIL access method, certain system options need to be configured. The following is the list of Email related system options: EMAILSYS, EMAILHOST, EMAILPORT, EMAILID, and EMAILPW. The following are values that are supported by EMAIL Access Method for system option EMAILSYS:

? MAPI: Messaging Application Program Interface (interface supported by Windows and Windows NT, which is used by Microsoft Exchange. MAPI is the default.)

? VIM: Vendor Independent Mail such as Lotus or cc:Mail.

You can issue the following statement to find out the value for your system:

proc options option=emailsys; run;

The log:

6912 proc options option=emailsys; 6913 run;

SAS (r) Proprietary Software Release 8.2 TS2M0

EMAILSYS=MAPI

Used by E-mail Access Method and Send menu item to set the interface

type with underlying e-mail system.

NOTE: PROCEDURE OPTIONS used:

real time

0.01 seconds

cpu time

0.01 seconds

Once you find out that your system supports the Email Access Method, then you can send out e-mails from within a SAS session.

To configure your system to send out email automatically without being prompted, you can add the following to the SAS configuration file of SASV8.CFG or SASV9.CFG: -EMALSYS EMAILSYS VALUE -EMAILID "EMAILID VALUE" -EMAILPW "YOURPASSWORD"

The following are some concrete examples of what goes into the configuration file for different EMAIL system:

For Microsoft Outlook: -EMAILSYS MAPI -EMAILID "Wenjie.wang@sanofi-" -EMAILPW "emailpassword"

For Lotus Notes: -EMAILSYS VIM -EMAILID ""Stacy Li/X/PH/Novartis"" -EMAILPW "emailpassword"

SYNTAX:

FILENAME fileref EMAIL 'address' ;

where:

fileref is a valid fileref.

EMAIL is the device-type keyword that indicates that you want to use e-mail.

'address' is the valid destination e-mail address of the user you want to send mail to. You can specify 'nul' here, and then specify the destination addresses in the emailoptions.

email-options are options that specify the recipients email address, subject, and cc recipients email address and attachment files, also the email system related option such as EMAILSYS, EMALID and EMALPW can be specified in here instead of SAS configuration file.

You can also specify the email-options in the FILE statement inside the DATA step. Options that you specify in the FILE statement override any corresponding options that you specified in the FILENAME statement.

In your DATA step, after using the FILE statement to define your e-mail fileref as the output destination, use PUT statements to define the body of the message. In your PUT statement, you use directives to specify the message attributes. The following is the list of directives that will change your message attributes:

? !EM_TO! addresses ? !EM_CC! addresses ? !EM_SUBJECT! Subject ? !EM_ATTACH! filename.ext

Here are the directives that perform actions:

? !EM_SEND!: sends the message with the current attributes ? !EM_ABORT!: aborts the current message. ? !EM_NEWMSG!: clears all attributes of the current message that were set using

PUT statement directives.

EXAMPLES

1. This is an example to send out a very simple message with an attachment file using FILENAME options to show the usage:

filename outmail email "wenjie_wang@" subject="Sales Report.doc" cc="simon_lin@" attach="c:\reports\Sales Report.doc";

data _null_; file outmail; put 'Dear team,'; put 'Attached is the sales report for last quarter.';

put 'If you have any question, please feel free to call'; put; put; put; put; put; put 'Regards,'; put; put; put; put 'Wenjie'; run;

Here is the log:

6934 filename outmail email "wenjie_wang@"

6935

subject="Sales Report.doc"

6936

cc="simon_lin@"

6937

attach="c:\reports\Sales Report.doc";

6938 data _null_;

6939

file outmail;

6940

put 'Dear team,';

6941

put 'Attached is the sales report for last quarter.';

6942

put 'If you have any question, please feel free to call';

6943

put;

6944

put;

6945

put;

6946

put;

6947

put;

6948

put 'Regards,';

6949

put;

6950

put;

6951

put;

6952

put 'Wenjie';

6953 run;

NOTE: The file OUTMAIL is: E-Mail Access Device

Message sent

To:

wenjie_wang@

Cc:

simon_lin@

Subject:

Sales Report.doc

Attachments: c:\reports\Sales Report.doc

NOTE: 13 records were written to the file OUTMAIL.

The minimum record length was 0.

The maximum record length was 50.

NOTE: DATA statement used:

real time

3.13 seconds

cpu time

0.16 seconds

The following is the screen shot from Outlook:

2. Another example will show that you can use email options in FILE statement to override the options in FILENAME statement.

filename outmail email "simon_lin@"; data _null_;

file outmail to="wenjie_wang@" subject="Sales Report" attach="c:\reports\Sales Report.doc";

put 'Dear team,'; put 'Attached is the sales report for last quarter.'; put 'If you have any question, please feel free to call'; put; put; put; put; put; put 'Regards,'; put; put; put; put 'Wenjie';

run;

Now the following screen shot showed that the option in FILE statement overrode the option in FILENAME statement.

3. A third example will show you how to use the directives in PUT statement:

filename outmail email ; data _null_;

file outmail; PUT '!EM_TO! wenjie_wang@'; PUT '!EM_SUBJECT! Sales Report '; PUT '!EM_CC! simon_lin@'; PUT '!EM_ATTACH! c:\reports\Sales Report.doc'; put 'Dear team,'; put 'Attached is the sales report for last quarter.'; put 'If you have any question, please feel free to call'; put; put; put; put; put; put 'Regards,'; put; put;

put; put 'Wenjie'; run;

Here is the screen shot:

SAS EMAIL ACCESS METHOD APPLICATION

Now that we understand the Email Access Method basics, we can put them into good use. Think about it, if we only want send one or more files to multiple recipients, we might just do it through Outlook, but situation arises when we need to send out different file to different people based on some results on regular basis, doing so through Outlook is a pain and it is easy to attach the wrong file to the wrong person. With SAS Email Access Method and careful planning, we can automate the whole process. Let's use a hypothetical example. In a hospital, there are SOPs regarding Residents' Notes Writing, which regulates that each attending resident physician needs to write a note for each admission with 24 hours; the notes could be written by his/her assistant or nurse. If he/she didn't write notes within 24 hours for certain admission(s) during the week, at the end of week, he/she will get an email with an attachment which shows the admission case with lack of the notes or delayed notes. We can automate the whole

process as the e-management tool to increase the SOP compliance rate. We will use macros to accomplish these.

In this hospital, the Data Management Department stores the notes file written by doctors and nurses, and patient admission files in Mainframe. The macro will have two parameters called FROM and TO, which denote the report period span, to extract data for the report period. The notes file will have the doctors or nurses' employee identification number(EIN), notes written time in the form of DDMONYYYY:HH:MM:SS, Admission CaseID, Patient ID and Patient Name; The admission file will have the Attending Physician's EIN, Admission CaseID, admission date and time in the form of DDMONYYYY:HH:MM:SS, and patient name, ward and room and bed information. A permanent dataset called EMP contains all employees' information, such as EIN(Employee Identification Number), first name, last name, hire date, email address, phone number, etc. The following macro shows the step-by-step implementation:

%macro notes_report(from=, to=); %let today=%sysfunc(today(),date9.); /* capture report running date*/ %let from=%upcase(&from); %let to=%upcase(&to);

%********************************************************************;

%* Step 1: get the admission file and notes file

*;

%********************************************************************;

filename admis "\\datam\admission\alladmission.dat"; data admission; informat admission_dt datetime20.; infile admis LRECL=1024 pad missover; input @31 admission_dt @ ; if "&from"d ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download