Using SAS® as your Mail Room an introduction to the SAS – e-mail interface

Erik W. Tilanus the Netherlands

Using SAS as your Mail Room Copyright ©2015: E.W. Tilanus

All rights reserved, No part of this book may be reproduced, stored in a database or retrieval system, or published, in any form or in any way, electronically, mechanically, by print, photoprint, microfilm or any other means without the written permission of the publisher.

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. SAS is a licensed user of M•CAM® technology. SAS is a licensed user of SmartTextTM. M•CAM®, SmartTextTM and CompassTM are registered trademarks or trademarks of M•CAM Inc. ® indicates USA registration. DataFlux and all other DataFlux Corporation product or service names are registered trademarks or trademarks of, or licensed to, DataFlux Corporation in the USA and other countries. Adobe and the Acrobat logo are trademarks of Adobe Systems Incorporated.

Table of Contents

iii

Table of Contents Abstract..................................................................................................1 Using SAS as your Mail Room an introduction to the SAS – e-mail interface.........................................1

Introduction...........................................................................................2 Setting up to send E-mail....................................................................3 Setting up System Options in the Configuration file.............................3 Setting up System Options in an Options statement ............................4 Setting up System Options in the SAS Windowing environment ......4 Setting up the MAPI interface...................................................................5 Setting up the SMTP interface...................................................................6 Connecting to GMAIL, HOTMAIL and other secured mail servers...7 OUTLOOK POP-UP....................................................................................9 Setting up the VIM interface....................................................................10

Sending a simple mail message from a DATA step....................11 Including attachments..............................................................................14 Overview of FILENAME … MAIL options...........................................14

Sending output...................................................................................17 Creating an output file and sending it as an attachment....................17 Sending output directly from ODS.........................................................18 Sending graphic output...........................................................................22

Sending a message under program control...................................24 Personalized bulk mail.....................................................................27 Example bulk mail....................................................................................27

Using the DATA step as a POP3 mail client.................................33 Summary of the POP3 protocol...............................................................33 The SOCKET access method....................................................................34 The full program.......................................................................................35 Test run.......................................................................................................38 Secure mail server access.........................................................................40

Table of Contents

iv

Protocol analysis........................................................................................41

Handling e-mail attachments...........................................................44 E-mail Attachments...................................................................................44 Separating and filing attachments..........................................................46 Converting binary attachments with SAS.............................................48 The conversion routine.............................................................................49 Integrated decoding..................................................................................51

How to run a program repeatedly?.................................................55 Solution 1: Using the operating system ................................................55 Solution 2: Repeated tasks within SAS .................................................59

Recommended Reading....................................................................61 Index.....................................................................................................62 Contact Information...........................................................................64

Abstract

1

Abstract Using SAS as your Mail Room an introduction to the SAS – e-mail interface You can send e-mail directly from your SAS programs. For instance to send output to remote customers, to alert system managers about irregularities in the program or to send a newsletter to a long list of addressees. You can also read incoming mail: it is relatively easy to make the DATA step function as a POP3 mail client. With this functionality you can receive and process input from remote locations. This presentation will discuss the basics of sending and receiving mails using SAS, including mass-mailings and handling of incoming or outgoing attachments. Note: the revenue management system in my other presentation uses both outgoing and incoming mails to interact with the revenue management staff of the airline.

2

Introduction

Introduction The DATA Step is the core of the data processing facilities in SAS. It is used to read information from virtually any source. It is used to process the data any way you like and eventually it is used to write the processed data to virtually any destination. One of the less obvious sources or destinations are email servers. With the right set-up you can send emails directly from the DATA Step and also use the DATA step as a mail client to receive mail. This document will tell you how. It starts with setting up the email environment that SAS should use to send mails. This environment is defined in a number of system options. Once the environment is set up, sending email is like writing information to any other external destination, using FILENAME, FILE and PUT statements. It can be as simple as sending a mail with a fixed content to a fixed mail address, but it is also possible to send personalized mails to a whole list of addressees. Next is to receive mail messages in a DATA step and file and possibly decode attachments in those mails. Depending on the mail server this can be done entirely within SAS or by making use of an external program that is called from SAS. The last chapter is not specific for the email applications but addresses a more generic issue: how to make a SAS program run repeatedly. It's relevance here is that you do not want to retrieve incoming mail messages only once, but rather periodically, e.g. every 10 minutes or every hour. SAS 9.4 has added some new options, especially in the field of secure links to email servers. Situations that are specific for either SAS 9.3 or 9.4 are clearly marked.

Setting up to send E-mail

3

Setting up to send E-mail SAS supports three interface methods to send mail: MAPI: 'Messaging Application Program Interface'. This is the default and makes use of the mail client on your computer, e.g. Microsoft Exchange or Outlook or Mozilla Thunderbird. SMTP: 'Simple Mail Transfer Protocol'. With SMTP you bypass the mail client and you have to specify outgoing mail account settings, comparable to specifying the outgoing mail settings in Outlook or Thunderbird. VIM: 'Vendor Independent Mail'. VIM can be used in combination with Lotus Notes or cc:Mail. The method is selected with the EMAILSYS option: EMAILSYS=MAPI|SMTP|VIM

You can set this option and the other email-system options in several ways:

–

modify the SAS configuration file

–

set them in the System Options window

–

set them using an options statement.

Either way: the options you have to set are the same. By the way, if you are sending mails from the Display Manager menu (File  Send Mail…) SAS will use either the MAPI or the VIM interface and not the SMTP interface.

Setting up System Options in the Configuration file The SAS configuration file is normally located in the same directory where the SAS software is installed. For SAS 9.3 the default location is C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg . For SAS 9.4 change the \9.3\ part into \9.4\. You can add the email lines in the upper part of the configuration file, for instance: /* Set Mail options */ -EMAILSYS SMTP -EMAILHOST "smtp.mailserver.nl" -EMAILID "[email protected]"

4

Setting up System Options in an Options statement

Setting up System Options in an Options statement The equivalent line in a SAS Options statement would be: OPTIONS EMAILSYS=SMTP EMAILHOST="smtp.mailserver.nl" EMAILID="[email protected]";

Setting up System Options in the SAS Windowing environment When you are working with in the SAS Windowing Environment (also called the “Display Manager”) then you can access the system options via the menu: Tools  Options  System. From there choose the Communications group  E-mail to see the current settings (Figure 1). To modify the settings, right-click on the option and open the modify window (Figure 2).

Figure 1: The System Options window with the EMAIL options.

Setting up to send E-mail

5

Figure 2: Right-Click on an opton to modify it.

Setting up the MAPI interface MAPI is the default method to be used by SAS. The MAPI interface works with the mail system as installed on the computer where SAS runs. It retrieves the necessary information from that system, so you hardly need to specify anything. Just verify that the system option EMAILSYS has the value MAPI and you are almost ready to go. The only thing that is still missing is the EMAILID option in which you specify the ID that you want to use for sending the messages, which is normally your mail address up to the @ sign. Be aware: this ID is case sensitive, as can be seen in the partial log presented in Listing 1.

6

Setting up the MAPI interface

1 2 3 4 5 6

options emailsys=MAPI emailid='erik_tilanus'; filename mailbox email; data _null_; file mailbox to='[email protected]' subject='MAPI test'; put "Hi"; run;

NOTE: The file MAILBOX is: E-Mail Access Device Message sent To: [email protected] Cc: Bcc: Subject: MAPI test Attachments: NOTE: 1 record was written to the file MAILBOX. The minimum record length was 2. The maximum record length was 2. NOTE: DATA statement used (Total process time): real time 0.06 seconds cpu time 0.04 seconds 7 8 9 10 11 12

options emailsys=MAPI emailid='Erik_tilanus'; filename mailbox email; data _null_; file mailbox to='[email protected]' subject='MAPI test'; put "Hi"; run;

ERROR: Login failure. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds

Listing 1: SAS LOG messages when using the MAPI interface.

Setting up the SMTP interface SMTP bypasses the mail client that is present on the computer and contacts the SMTP server directly. Therefore you have to specify information similar to the information you specify for your outgoing mail account in mail clients like Outlook or Thunderbird. So look under "preferences" or "outgoing mail settings" or a similar heading in your mail client to find the right settings. The options you have to set are EMAILSYS

SMTP

Setting up to send E-mail EMAILHOST EMAILID EMAILPW

7 "name of outgoing mail server" "sender information" "password to access mail server"

The sender information and password have to be enclosed in double quotes if they contain blanks. For example: OPTIONS EMAILSYS=SMTP EMAILHOST=mail.planet.nl EMAILID="Erik Tilanus " EMAILPW=etila999;

There are more options that can be set, but their default values are often acceptable. Table 1 shows the complete list.

Connecting to GMAIL, HOTMAIL and other secured mail servers GMAIL and HOTMAIL and quite a few other mail servers require a secure link between the mail client and the SMTP server. SAS 9.3 does not support that in the SMTP interface. If you want to send mails via those mail networks in SAS 9.3, you have to use the MAPI interface and let the mail client on your computer take care of the secure link. In SAS 9.4 the secure connection is added to the EMAILHOST option. Following the server name you can specify SSL or STARTTLS to indicate that it is a secure link. In addition you can include port, authentication and user ID information that will take preference over those specified in their respective independent options. The settings for the HOTMAIL server are: OPTIONS EMAILHOST = ('smtp.live.com' auth=plain port=587 starttls [email protected] pwd=mypassword);

The settings for GMAIL are: OPTIONS EMAILHOST = ('smtp.googlemail.com' auth=plain port=465 ssl [email protected] pwd=mypassword);

The sub-options follow the rule of their independent counterparts like EMAILPORT, EMAILID and EMAILAUTHPROTOCOL.

8

Connecting to GMAIL, HOTMAIL and other secured mail servers

Option name

Default value Description

EMAILACKWAIT=

30

Time-out period for SAS to wait for acknowledgment from the SMTP server. For slow connections try EMAILACKWAIT=60

EMAILAUTHPROTOCOL=

NONE

Valid values: NONE (no authentication needed) LOGIN (Log-in required, supply log-in credentials) PLAIN (Sends BASE64 encoded user ID en password)

EMAILFROM / NOEMAILFROM

NO-

Specifies whether the sender information has to be supplied in FILENAME or FILE statement.

EMAILHOST= (SAS 9.3)

No default value

SMTP server name, like “smtp.kpnmail.nl” (quotes not required) You can specify more servers, by enclosing them together between parentheses and quoting each of them, like in: ('smtp.kpnmail.com' 'smtp.kpnmail.nl'). The first available server will be used.

EMAILHOST= (SAS 9.4)

LOCALHOST For non-secured servers: as in SAS 9.3. For secured servers the options need to be within parentheses and start with the server name between quotes, followed by following options: AUTH= (as EMAILAUTHPROTOCOL), PWD= (as EMAILPW), PORT= (as EMAILPORT), SSL or STARTTLS, USERID= (as EMAILID). The options have preference over their independent system options.

EMAILID=

No default value

The logon ID or mail profile to be used in the MAPI interface or the sender mail address in case of the SMTP interface.

EMAILPORT=

25

The port number of the SMTP server. 25 is default for unsecured servers. Secured servers use different ports, e.g. GMAIL (server name: smtp.googlemail.com) uses port 465 and HOTMAIL (server name: smtp.live.com) uses port 587)

Table 1: System options for the EMAIL interface.

Setting up to send E-mail

9

Option name

Default value Description

EMAILPW=

No default value

The password that belongs to the user ID in the EMAILID option. Should be between double quotes if it contains a space.

EMAILSYS=

MAPI

Valid options are: MAPI, SMTP, VIM

EMAILUTCOFFSET=

No default value

This option can be used to provide an offset between local time and UTC. The format is “+hhmm” or “-hhmm”.

Table 1 (continued): System options for the EMAIL interface.

OUTLOOK POP-UP Sometimes Outlook is configured in such way that it does not take messages sent via the MAPI interface for granted and comes with a pop-up asking permission to send the mail, which can of course be quite annoying. This is something you cannot change within SAS, but you have to change the Windows registry for it. After taking the necessary back-ups, look for the following registry entry: HKEY_CURRENT_USER\Software\Policies\Microsoft\Office \14.0\Outlook\Security

If it does not yet exist, create it. If it exists look for the following elements: PromptSimpleMAPISend PromptSimpleMAPINameResolve PromptSimpleMAPIOpenMessage If they do not exist, go to the Edit menu and choose NEW - DWORD Value. Then create the elements. After locating or creating the elements right-click on them and modify their value to 2 (=auto-approve).

10

Setting up the VIM interface

Setting up the VIM interface For the VIM interface to work, you have to specify the EMAILSYS, EMAILID and EMAILPW options: -EMAILSYS -EMAILID -EMAILPW

VIM User log on code User password

Sending a simple mail message from a DATA step

11

Sending a simple mail message from a DATA step Once you have set up the system options, you use a FILENAME statement to define the output destination, a FILE statement to link your PUT statements to the right output destination and PUT statements to create the message. Program 1 shows the principle. It starts with a special format of the FILENAME statement, that includes the device name EMAIL after the fileref. After that you enter the recipient's address, subject and further options. A list of all options is included in Table 2. FILENAME Mailbox EMAIL '[email protected]' Subject='Test Mail message'; DATA _NULL_; FILE Mailbox; PUT "Hello"; PUT "This is a test message from the DATA step"; RUN;

Program 1: Basic DATA step to send mail. In Program 1 the addressee and the subject were included in the FILENAME statement. You don't need to do that. As an alternative you can specify them in the FILE statement as in Program 2. If they are present in both, the information in the FILE statement will prevail. Messages are sent upon completion of the DATA step, not per observation. But off course the PUT statements will be executed with each iteration of the DATA step and therefore continue to build the message. In a similar way you can enter copy-readers, by specifying CC=mail-address or BCC=mail-address and add attachments (ATTACH=). The latter will be discussed in a later paragraph. To include a display name you place the whole address between quotes and the address part between < and >, like in TO='Erik '. If you want to send a mail to more than one address, you list all addresses (each between quotes) within parentheses.

12

Sending a simple mail message from a DATA step

FILENAME Mailbox EMAIL; DATA _NULL_; FILE Mailbox TO='[email protected]' SUBJECT='Test Mail message'; PUT "Hello"; PUT "This is a message from the DATA step"; RUN;

Program 2: As Program 1, but with all mail data moved from FILENAME to FILE statement. If you are using the SMTP interface, you can overrule the default sender and specify a separate reply-to address. Use the FROM option and the REPLYTO option to accomplish this. Note that REPLYTO does not work with the MAPI interface, since it uses the information as programmed in the mail account. If that contains a separate reply-address, fine, it will be used, but you cannot overrule it. All these options are put together in the following FILE statement: FILE MailBox TO=('Erik ' 'Myself ') CC=('[email protected]' '[email protected]') BCC='[email protected]' FROM='My Business ' REPLYTO='My anonymous address ' SUBJECT='Test Mail message to group and other options';

Replacing the FILE statement in Program 2 with this one results in the message as displayed in Figure 3. If your outgoing mail server is not picky, you can send anonymous emails this way. To demonstrate this I set the EMAILID to "[email protected]". Then I used Program 2 to send an e-mail. Figure 3 shows the mail as received by me.

Sending a simple mail message from a DATA step

13

Figure 3: Email received from Program 2 with the extended FILE statement.

Figure 4: Anonymous email. If you want to build more than one message simultaneously for different addressees, you may specify more FILENAME statements and direct the PUT statement to either of them with different FILE statements. All messages will be sent at the end of the data step. Program 3 shows the principle.

14

Sending a simple mail message from a DATA step

FILENAME MailBox1 EMAIL; FILENAME MailBox2 EMAIL; FILENAME MailBox3 EMAIL; DATA _NULL_; FILE MailBox1 TO='Erik ' SUBJECT='First addressee'; PUT "Hello Mailbox1"; FILE MailBox2 TO='[email protected]' SUBJECT='Second addressee'; PUT "Hello Mailbox2 "; FILE MailBox3 TO='[email protected]' SUBJECT='Third addressee'; PUT "Hello Mailbox3 "; RUN;

Program 3: Sending more mails simultaneously.

Including attachments Including attachments is also rather straightforward: specify the attachment in either the FILENAME or FILE statement with the keyword ATTACH, like: FILENAME MailBox EMAIL ATTACH='C:\SASUtil\Tips.doc';

or FILE Mailbox ATTACH='C:\SASUtil\Tips.doc';

If you want to specify the attachment under program control, you can use the special option in the PUT statement: PUT '!EM_ATTACH!' AttachInfo;

where AttachInfo is either a character variable that contains the full path and name of the file that should be attached or the full path and name between quotes. More about this special PUT directive and other similar constructions are discussed in the chapter "Sending a message under program control".

Overview of FILENAME … MAIL options Table 2 contains the complete list of options that can be used in the FILENAME .. MAIL statement. The first five are commonly used, while the other options are only needed in special circumstances.

Sending a simple mail message from a DATA step

15

General rules for the arguments: if the specification argument contains a blank it should be enclosed between quotes. Some options require that the argument is between quotes, regardless whether it contains a blank or not. So it is a good habit to quote them all. If more values are to be included, the whole list should be between parentheses. The same options can be used in a FILE statement that refers to the FILENAME statement. In case the same option is specified in both, the value in the FILE statement prevails. Option

Description

Common options TO=

Recipient's email address. More mail addresses should be enclosed between brackets and quoted. When display name included the combination should be quoted and the mail address should be between . Example: TO='[email protected]' TO=('erik ' '[email protected]')

FROM=

Sender's email address

CC=

Copy reader's mail address. Rules as for TO=

BCC=

Blind copy reader's mail address. Rules as for TO=

SUBJECT=

Subject of the mail Example: SUBJECT='My Mail Message'

ATTACH=

Specifies full path and name of an attachment to be sent. More files: enclose them between brackets. Example: ATTACH='e:\reports\monthly sales.pdf'. ATTACH=('e:\reports\monthly sales.pdf' 'e:\reports\monthly revenue.pdf')

Suboptions of ATTACH= CONTENT_TYPE= Specifies the type of attachment. Normally not needed. SAS will derive it from the file name extension. EXTENSION=

Changes the file name extension of the attached file.

INLINED=

Reference name to embed elements in HTML mails.

Table 2: Possible options in the FILENAME .. EMAIL statement. Note: these options can also be used in the FILE statement that refers to the FILENAME statement.

16

Overview of FILENAME … MAIL options

Option

Description

Suboptions of ATTACH= (continued) LRECL=

Logical record length. Default in SAS9.3: 256, in SAS 9.4 32767. LRECL=3000

NAME=

Changes the file name

ENCODING=

Defines encoding of the attachment for foreign languages, only needed when there are differences in language character sets. Refer to: SAS Natural Language Support (NLS) – Reference Guide.

OUTENCODING=

Defines new encoding of the outgoing attachment for foreign languages, only needed when there are differences in language character sets. Refer to: SAS Natural Language Support (NLS) – Reference Guide.

Miscellaneous options CONTENT_TYPE=

Specifies the content type of the body of the mail between quotes. Default: 'text/plain'. Example: CONTENT_TYPE='text/html'

DELIVERYRECEIPT

Requests a notification when the mail is delivered.

ENCODING=

Defines encoding of the message body for foreign languages, only needed when there are differences in language character sets. Refer to: SAS Natural Language Support (NLS) – Reference Guide.

EXPIRES=

Expiration date. Beyond that date the message is not sent. Format: 'dd mmm yyy hh:mm'.

IMPORTANCE=

Specifies the importance of the message as 'LOW', 'NORMAL' or 'HIGH'

LRECL=

Logical record length. Default in SAS9.3: 256, in SAS 9.4 32767. Example: LRECL=3000

READRECEIPT

Requests a notification when the mail is opened on the recipient's screen

REPLYTO=

Mail address for replies (if different from sender)

Table 2 (continued): Possible options in the FILENAME .. EMAIL statement. Note: these options can also be used in the FILE statement that refers to the FILENAME statement.

Sending output

17

Sending output Output can be mailed in two ways: either by first creating an output file then mailing it as an attachment, or by specifying a MAIL destination in an ODS statement. The first method is more universal, so let us do that first.

Creating an output file and sending it as an attachment The principle is simple: write the (procedure-)output to a file and then specify that file as an attachment in a subsequent DATA _NULL_ step that sends the mail. If you don't need a permanent copy of the output, you can store it in a temporary file. Don't do that with a FILENAME … TEMP; since such file does not have a file extension and therefore the output file type is not recognized by the receiver of the mail, unless you also specify the CONTENT_TYPE option. The alternative is to create a permanent file at a temporary location. And we do have such temporary location available, called the WORK library. When terminating the SAS session that library is deleted including our 'temporary permanent' file. Program 4 shows how this works. The program consists of two parts: the first part creates the output, the second part sends it. In this example we used a DATA step to produce the output, but it works off course the same with procedure output. The enclosing ODS statements specify that the report is to be created in PDF format and filed at the address in the FILENAME statement. In the FILENAME statement you see a not too familiar construction: %SYSFUNC(GETOPTION(WORK)). The %SYSFUNC macro lets you use DATA step functions outside a DATA step. In this case we use the GETOPTION function to get the address of the current WORK library. In combination with the file name (testlist.pdf) we have the full path of the output file. The second DATA step picks up the file name again, by opening the file with the FOPEN function and getting the full name using the FINFO function. Thus it assigns the full file name to the variable Attachment and attaches the file to the mail with the special PUT statement that follows.

18

Creating an output file and sending it as an attachment

FILENAME MailBox EMAIL '[email protected]' SUBJECT='Mail message with PDF attachment'; FILENAME pdffile "%SYSFUNC(GETOPTION(WORK))\testlist.pdf"; ODS HTML CLOSE; ODS PDF FILE=pdffile; DATA _NULL_; FILE PRINT; PUT "This is my report"; TITLE 'Test report in PDF format'; RUN; ODS PDF CLOSE; ODS HTML; DATA _NULL_; attachment=FINFO(FOPEN('pdffile'),'FileName'); FILE MailBox; PUT "attached you find the report"; PUT '!EM_ATTACH!' attachment; RUN;

Program 4: Creating a report in a DATA step and sending it as attachment

Sending output directly from ODS You can send output also directly using ODS. What happens with that output depends on yet another option in the FILENAME ... MAIL statement: CONTENT_TYPE=. The default value of this option is CONTENT_TYPE='text/plain', meaning that all information received by the mail client is considered plain text. So if you specify the LISTING destination in ODS the output will be shown in the body of the mail. It may not look nice, since most mail clients use a proportional font, like Arial to display a message and listing output assumes a mono-spaced font like Courier. Would you send HTML output this way, it will show as the plain HTML source code. By specifying CONTENT_TYPE='text/html' the HTML output is displayed directly in the body of the message. If you specify CONTENT_TYPE='multipart/mixed', the HTML output will be sent with the message as an attachment and will open in a browser window.

Sending output

19

When sending RTF output without specifying the CONTENT_TYPE option the RTF output would display as a text message showing all the RTF commands. To display the output correctly, you will have to save the contents of the message as a text file with extension RTF and then reopen it as RTF document with for instance MS Word or OpenOffice Writer. However, if you specify CONTENT_TYPE='text/rtf', the RTF text is moved to an attachment and it will open correctly with an RTF capable application like Word or Writer. To send PDF output directly from ODS, you should specify CONTENT_TYPE='mixed'. It will create the PDF output and place it in an attachment. Program 5 shows an example, using HTML output from ODS. We prepare a PROC TABULATE report with sales data for two years and the index figures of the second year over the first year, including some traffic lighting. In the FILENAME statement you can see the CONTENT_TYPE option. The traffic lighting is defined in the format Index. Figure 5 shows the report as it is received by mail.

20

Figure 5: HTML output directly from ODS.

Sending output directly from ODS

Sending output

FILENAME mail EMAIL TO="[email protected]" SUBJECT="HTML TABULATE OUTPUT" CONTENT_TYPE="text/html"; PROC FORMAT; VALUE Index low-90 = RED 90-100=CYAN 100-110=ORANGE 110-high=GREEN; RUN; ODS HTML BODY=mail; TITLE 'Turnover 2009/10 over 2008/09'; PROC TABULATE DATA=SalesData STYLE=[background=black foreground=white fontsize=4]; CLASS ProductGroup Product/ STYLE=[background=black foreground=white fontsize=4]; CLASSLEV Product/ STYLE=[background=yellow foreground=black fontsize=4 fontstyle=italic]; CLASSLEV Productgroup/STYLE=[fontsize=4]; VAR Turnover_CY Turnover_PY/ STYLE=[background=brown foreground=yellow fontsize=3]; TABLE Productgroup*Product, Turnover_PY*SUM=' ' Turnover_CY*SUM=' ' Turnover_CY=' '* {STYLE=[background=index. Fontsize=3]}* PCTSUM/ RTS=32; LABEL Turnover_PY = 'Turnover 2013' Turnover_CY ='Turnover 2014' productgroup = 'Product Group'; KEYLABEL PCTSUM='Index 2014/13'; KEYWORD PCTSUM/ STYLE=[background=lightblue fontsize=4]; RUN; ODS HTML CLOSE;

Program 5: Sending procedure output directly by mail (HTML or RTF).

21

22

Sending graphic output

Sending graphic output Sending graphic output can be done in a similar way. Also in this case we have to specify the CONTENT_TYPE parameter to identify to the mail system that it is going to receive an image. The format is: CONTENT_TYPE='image/gif' for gif-type graphics, 'image/jpeg' for jpeg graphics or 'image/png' for png graphics. If the receiving mail client is programmed to show the information in line, the graphic output will show in the body of the mail, otherwise it will be treated as an attachment and can be opened by a separate application. In Program 6 we use PROC GCHART to create a pie chart of the value of the banknote circulation in the Netherlands by note face value in 2001 (the last year before the Netherlands switched from the guilder to the euro). In this example we use the GIF device driver. Note that we are setting ODS Listing. If you use ODS HTML, which is the default in SAS 9.3 the graphic information is not sent to the mail destination, but to the HTML destination. The result can be seen in Figure 6. In a similar way we could have used the other graphic formats. ODS listing; OPTIONS emailsys='smtp' emailhost='smtp.kpnmail.nl' emailid='[email protected]'; FILENAME mailbox email content_type='image/gif' to='[email protected]' subject='Graphic output with GIF'; GOPTIONS device=gif htext=1.5 gsfname=mailbox gsfmode=replace; TITLE f='Arial/bold' h=1.8 'Bank note circulation in the Nederlands 2001'; TITLE2 f=Arial h=1.5 'Circulation value share of each note'; PROC GCHART DATA=wms.banknotes(where=(year=2001)); PIE banknote/SUMVAR=value DISCRETE OTHER=0 EXPLODE=250 SLICE=arrow NOHEADING VALUE=none; RUN;QUIT;

Program 6: SAS/GRAPH output sent by mail.

Sending output

Figure 6: SAS GRAPH output in the body of a mail.

23

24

Sending a message under program control

Sending a message under program control In the previous chapter the addressing of e-mails and the setting of subject and so on were handled in either the FILENAME or the FILE statement. As an alternative you can bring these settings also under software control in the DATA step using special directives in the PUT statement. The general form is PUT '!EM_xxx!' specification;

where xxx stands for some directive. The !EM_xxx! directive should be the first element on any output line, so normally it is the first element in a PUT statement, but it can also be the first element after a 'new line' directive (/) in the PUT statement. There cannot be two directives on the same line. The second would be interpreted as text. Table 3 shows the possible directives. The examples in this table show quoted text as directive and specification. However that is not required. You could as well use character variables containing the appropriate information. It's a PUT statement, remember! The arguments to the directive follow basically the same rules as their corresponding options in the FILENAME statement. So, for instance, when specifying more addresses, each address should be between quotes and the total string should be between quotes, as you can see in the !EM_CC! example. The !EM_NEWMSG! directive clears all previous information and lets you start a new message. If you leave it out, every new message will build upon the previous one, just adding the information rather than replacing it. The !EM_ABORT! prevents sending a message at termination of the DATA step. If you leave it out and you issued a !EM_NEWMSG! directive before, an empty message will be sent to the address in the FILENAME or FILE statement. Program 7 shows an example how the directives can be used and Figure 7 shows the result.

Sending a message under program control

Directive

25

Corresponding FILENAME option

Example

!EM_TO!

TO=

PUT '!EM_TO!' "[email protected]";

!EM_FROM!

FROM=

PUT '!EM_FROM!' "[email protected]";

!EM_CC!

CC=

PUT '!EM_CC!' "'[email protected]' '[email protected]'";

!EM_BCC!

BCC=

PUT '!EM_BCC!' "[email protected]";

!EM_SUBJECT!

SUBJECT=

PUT '!EM_SUBJECT!' "My SAS tips"";

!EM_ATTACH!

ATTACH=

PUT '!EM_ATTACH!' "C:\SASUtil\Tips.doc";

Common directives

Directives that control sending messages !EM_SEND!

Send the message.

PUT '!EM_SEND!';

!EM_NEWMSG!

Clear the current message buffer and start over to make a new one.

PUT '!EM_NEWMSG!';

!EM_ABORT!

Abort building the message

PUT '!EM_ABORT!';

Miscellaneous directives !EM_DELIVERYRECEIPT!

DELIVERYRECEIPT

!EM_EXPIRES!

EXPIRES=

!EM_IMPORTANCE!

IMPORTANCE=

!EM_READRECEIPT!

READRECEIPT

!EM_REPLYTO!

REPLYTO=

PUT '!EM_EXPIRES!' '15 MAY 2015 12:50'

PUT '!EM_REPLYTO!' "[email protected]";

Table 3: The special PUT statements to manage sending of mail messages.

26

Sending a message under program control

FILENAME mailbox email; DATA _null_; FILE MailBox; My_CC = "'[email protected]' '[email protected]'"; PUT '!EM_TO!' 'Erik '; PUT '!EM_CC!' My_CC / '!EM_SUBJECT!' 'Test message with !EM_xxx! ' 'directives' / "Hello" / "This time everything is managed within the " "DATA step"; run;

Program 7: Sending a message under software control.

Figure 7: The mail that is created with the !EM_xxx! directives.

Personalized bulk mail

27

Personalized bulk mail The !EM_xxx! directives open up a totally different use of the mail functionality as well. Any time you process the !EM_SEND! directive a message is sent. So if you want to send a newsletter to a whole mailing list that is in a SAS data set, you simply read that mailing list with a SET statement, then you build the message in the DATA step and finalize with an !EM_SEND! directive. However there are two things you have to worry about: the speed of your outgoing connection and the policies of the outgoing mail server. Your computer can probably spit out more messages than your line can handle and the mail server may conclude that you are spamming if you are spitting out too many messages too fast! (I have had that once when I was using a HOTMAIL address to send a PDF-newsletter to a 500 addresses mailing list!)

Example bulk mail Let us take the example of the newsletter. You want to address every person individually and let us assume that you want to include a reminder to renew the subscription if they did not do so for the last 12 months. The address list and the last renewal date are in a SAS data set (Figure 8).

Obs

Given_ name

Surname

MailAddress

1 2 3 4

John Peter Mary Georg

Smith North Sunface Amesbuy

[email protected] [email protected] [email protected] [email protected]

Member Since

Renewal Date

12DEC2005 01APR2007 15JUN2012 01JAN2012

21DEC2012 01JAN2012 . .

Figure 8: First four observations in the mailing data set. Preferably the text of the mail is not hard-coded in the mailing program itself, as it would require changing the program each time you want to send a new message to the list. So the text of the message is in a separate text file that will be read in. This text file can contain place holders where you have to include names and so on. Figure 9 shows the mail message we want to send. The paragraph starting with ####3 should be included only when a renewal is overdue, which is for the second and fourth observation in Figure 8.

28

Example bulk mail Program 8 shows the related program. The program starts with reading the message text into an temporary array. That way it is readily available whenever we build a message. Then follows a speed throttle. To avoid overload situations and spamming suspicion we build a pause after every three messages and wait for 5 seconds. Depending on your line speed and the size of possible attachments you may need to adjust these values. In practice bursts of 1 to 5 messages with sleeping periods of 3 – 10 seconds work fine. It never hurts when you are slowing down, except that it takes longer to send a long list of addresses.

Dear ####1 ####2, Attached you will find this month's newsletter. ####3It has been ####4 months since you ####5 for our newsletter. We hope you still enjoy it. To make sure that we only send the newsletter to those who want to receive it, we request you to renew you subscription by sending us an email. ####3 We wish you pleasant reading hours. The publisher

Figure 9: The text of the mail message with place holders. After the throttle starts the construction of the message. First we fill the addressee, the sender, the subject and the link to the attachment. Then follows the compilation of the message body. We read a line from the text array and look for place holders. If they are found they are replaced by the real contents. Once all place holders are solved the loop is left and the resulting line is sent to the mail destination with a PUT statement. Place holder 3 is a special one as outlined above: if renewal is not needed, that paragraph should not be included. To accomplish this we replace the line by $$$$ in case a renewal is not needed . Thus all other place holders are eliminated and the line is skipped at the PUT statement. Listing 2 shows the messages in the SAS Log and Figure 10 shows two of the mails as they are sent.

Personalized bulk mail

29

FILENAME MailBox EMAIL LRECL=3200 CONTENT_TYPE='text/plain'; FILENAME MailMsg 'E:\Newsletters\mailtext.txt'; DATA _NULL_; RETAIN paragraph_count; LENGTH subject $100 line $3200 substitute $100; * read the message text into a temporary array; ARRAY mailtext {100} $3200 _TEMPORARY_; IF _n_=1 THEN DO paragraph_count = 1 TO 100; INFILE MailMsg LENGTH=l END=getout ; INPUT paragraph $varying3200. l; mailtext{paragraph_count} = paragraph; IF getout THEN LEAVE; END; * Read the addresses; SET newsletter_admin END=eof ; * Speed throttle. Tune messages per burst and wait time; IF _n_/3 = INT(_n_/3) OR eof THEN DO; * Pause after every 3 messages; delay=SLEEP(5); * Wait 5 seconds; END; subject = 'Newsletter of '||PUT(TODAY(),MONYY5.); FILE mail; PUT '!EM_TO!' mailaddress; PUT '!EM_SUBJECT!' subject; PUT "!EM_FROM! [email protected]"; PUT '!EM_ATTACH!' "E:\Newsletters\newsletter.pdf"; * Following loop fills the body of the mail and substitutes the placeholders; DO n=1 TO paragraph_count; Line= mailtext{n}; DO WHILE (INDEX(Line,'####')); flag = INDEX(Line,'####'); IF flag THEN DO; Code = SUBSTR(Line,flag+4,1); IF Code = '3' THEN DO; IF today()-max(Membersince,Renewaldate) GT 365 THEN line = substr(line,6); ELSE Line = '$$$$'; END;

Program 8: Mass-mailing a newsletter.

30

Example bulk mail

IF CODE = '3' THEN CONTINUE; SELECT (Code); WHEN ('1') Substitute = Given_name; WHEN ('2') Substitute = Surname; WHEN ('3') ; WHEN ('4') Substitute=put( intck('month',max(Membersince, Renewaldate),today()),best12.); WHEN ('5') DO; if Renewaldate ne . then Substitute = 'renewed your subscription'; else Substitute = 'registered'; END; OTHERWISE Substitute = '????'; END; Line = COMPBL(SUBSTR(Line,1,flag-1)|| Substitute||SUBSTR(Line,flag+5)); END; END; IF NOT INDEX(line,'$$$$') THEN PUT Line ; END; PUT '!EM_SEND!' / '!EM_NEWMSG!'; IF eof THEN PUT '!EM_ABORT!'; RUN;

Program 8 (continued): Mass-mailing a newsletter.

Personalized bulk mail

Figure 10: Personalized mass-mailing result.

31

32

NOTE: The infile MAILMSG is: Filename=E:\newsletters\mailtext.txt, RECFM=V,LRECL=256,File Size (bytes)=373, Last Modified=19Feb2013:20:50:19, Create Time=19Feb2013:15:37:28 NOTE: The file MAIL is: E-Mail Access Device Message sent To: '[email protected]' Cc: Bcc: Subject: Newsletter of FEB13 Attachments: "E:\newsletters\newsletter.pdf" Message sent To: '[email protected]' Cc: Bcc: Subject: Newsletter of FEB13 Attachments: "E:\newsletters\newsletter.pdf" Message sent To: '[email protected]' Cc: Bcc: Subject: Newsletter of FEB13 Attachments: "E:\newsletters\newsletter.pdf" Message sent To: '[email protected]' Cc: Bcc: Subject: Newsletter of FEB13 Attachments: "E:\newsletters\newsletter.pdf" NOTE: 9 records were read from the infile MAILMSG. The minimum record length was 0. The maximum record length was 237. NOTE: 57 records were written to the file MAIL. The minimum record length was 1. The maximum record length was 249. NOTE: There were 4 observations read from the data set WORK.NEWSLETTER_ADMIN. NOTE: DATA statement used (Total process time): real time 11.17 seconds cpu time 0.54 seconds

Listing 2: SAS Log messages from the mailing.

Example bulk mail

Using the DATA step as a POP3 mail client

33

Using the DATA step as a POP3 mail client The previous chapters were about sending mails directly from your SAS program. But we can also do the opposite: receive mails in a SAS program and then do something with them. Mail client programs like Thunderbird or Outlook access the mail server periodically to ask whether there are new messages and if so, they will be downloaded and displayed. The common protocol used in the communication between the client program and the server is called the POP protocol (Post Office Protocol), currently in its third incarnation so POP3.

Summary of the POP3 protocol A POP3 mail server is typically a TCP/IP based server. So it 'listens' to a TCP/IP 'socket' to start communication with a client, in this case a mail client like Thunderbird or Outlook. The POP3 protocol is in fact a very simple protocol. Each message you send to the server starts with a keyword, possibly followed by some data and ending with a carriage control – line feed combination. Each response that you receive from the server starts with +OK if your message was handled successfully. Table 4 shows you the typical sequence of messages between the client and the server. You log on to the server, check whether there are mails available and if so, you download them and delete them from the server. Finally you log off and you are done. To start the communication with the server you issue a read request (INPUT). This is a kind of 'knock on the door'. The server will respond with +OK. After that the communication sequence as outlined in Table 4 can start. If the response from the server consists of multiple lines you have to read them one by one using the INPUT statement repeatedly, until the server responds with a termination line, which is a line with a single period.

34

Summary of the POP3 protocol

Action

Data

Comment

Receive

+OK

May be followed by server identification

Send

USER &myuserid

&myuserid stands for the user ID

Receive

+OK

Send

PASS &password

Receive

+OK User successfully logged on

Send

STAT

Asks for the status

Receive

+OK 2 1780

2 messages present, 1780 bytes in total

Send

LIST

Asks for details of the messages

Receive

+OK 2 1780 1 881

First message length 881 byte (repeat receive to get info on all messages)

Send

RETR n

Retrieve n-th message

Receive

+OK followed by message Retrieve n-th message (repeat receive text until complete message received)

Send

DELE n

Receive

+OK

Send

QUIT

Terminates the communication with the server

Receive

+OK

May be followed by server identification

&password stands for the password

Delete n-th message from the server

Table 4: Typical dialog with POP3 server

The SOCKET access method The SOCKET access method which is implemented in the FILENAME statement implements TCP/IP communications and thus enables communication with POP3 mail servers. The basic format for this FILENAME statement is: FILENAME pop3 SOCKET 'mail.mailserver.com:110' ;

This version of the statement sets the SAS program in client mode and enables it to establish a two-way communication with a TCP/IP based server, listening to the same socket. In our case socket 110, which is the default socket for POP3 servers.

Using the DATA step as a POP3 mail client

35

The full program With the knowledge of the dialog it is relatively simple to write the program for receiving mails in the DATA step. Program 9 shows a fairly complete version of a program that can do this. The FILENAME statement has the structure as shown above, with one additional option: TERMSTR=CRLF. As mentioned the POP3 protocol requires that each message is terminated with a carriage control (CR) and a line feed (LF) character. By default SAS terminates only with the line feed character. You could add the carriage control by adding '0D'x at the end of each PUT statement, but adding the option in the FILENAME statement is easier. We need a two-way communication between SAS and the mail server. This means that the fileref of the FILENAME statement will be used both in INFILE and in FILE statements. The first reference should be in the INFILE statement. Together with the subsequent INPUT statement it opens the communication with the mail server. The mail server will respond with some greeting, starting with +OK and then normally the name of the server. Note the use of the MISSOVER option on the INFILE statement. Since we are working with variable length records it is easy to look for some information (e.g. the server acknowledgment "+OK") on a line which is too short. MISSOVER prevents that SAS will look in the next line to find the required information After successfully opening the communication (test for '+OK') you have to send the user ID and password information to the server. If the connection could not be established write an error message to the log file and stop the processing. If the logon has been established successfully we issue the STAT command. This will return information about the presence of mails. The second word in the response is the number of messages waiting to be retrieved. This is extracted from the response. If there are messages, we issue the LIST command, which will return a line for each message, containing the message number and it's size. The server will transmit a termination line, containing a single period, after all messages are reported. So we can use that to test whether we reached the end. The message length is stored in an array, to enable verification that we received the complete message. The array in the program can store information about 100 messages. That can be increased as needed.

36

The full program

FILENAME pop3 SOCKET 'pop3.planet.nl:110' TERMSTR=CRLF; DATA mailfile(KEEP=line); LENGTH line $1000; ARRAY msglength {100} _TEMPORARY_; INFILE pop3 LENGTH=len missover; INPUT; * Open communication; IF _INFILE_ =: '+OK' THEN DO; FILE pop3; PUT "USER &myuserid"; INPUT; END; ELSE DO; ErrorMessage = 'No connection with server'; GOTO error; END; IF _INFILE_ =: '+OK' THEN DO; FILE pop3; PUT "PASS &mypassword"; INPUT; END; ELSE DO; ErrorMessage = 'User ID not accepted'; GOTO error; END; IF _INFILE_ =: '+OK' THEN DO; FILE pop3; PUT "STAT"; INPUT; END; ELSE DO; ErrorMessage = 'Invalid UID or password'; GOTO error; END; IF _INFILE_ =: '+OK' THEN DO; n_msg = INPUT(SCAN(_INFILE_,2),BEST.); IF n_msg GT 0 THEN DO; FILE pop3; PUT "LIST"; INPUT; END; ELSE DO; ErrorMessage = 'No messages on server'; GOTO error; END; END; ELSE DO; ErrorMessage = 'Status not received'; GOTO error; END;

Program 9: Reading e-mails from a POP3 mail server.

Using the DATA step as a POP3 mail client

IF _INFILE_ =: '+OK' THEN DO UNTIL (a="."); INPUT @1 a $CHAR1. @; INPUT @1 msg_nr msg_length; IF a NE '.' THEN msglength{msg_nr}=msg_length; END; DO n=1 TO n_msg; contents_start=0; tot_length=-5; FILE pop3; PUT "RETR " n; INPUT; IF _INFILE_ =: '+OK' THEN DO UNTIL (a="." and len=1); INPUT @1 a $CHAR1. @; INPUT @1 line $VARYING1000. len; TOT_LENGTH + len +2; IF a='.' AND len GT 1 THEN DO; line=substr(line,2); tot_length +(-1); END; OUTPUT; END; IF tot_length ge msglength{n} THEN DO; FILE pop3; PUT "DELE " n; INPUT; END; ELSE PUTLOG 'Message ' n 'not received correctly' ; IF _INFILE_ =: '+OK' THEN; ELSE PUTLOG 'Unable to delete message ' n; END; FILE pop3; PUT "QUIT"; INPUT; STOP; RETURN; error: PUTLOG errormessage; STOP; RETURN; RUN;

Program 9 (continued): Reading e-mails from a POP3 mail server.

37

38

The full program Next we start a DO loop to retrieve all messages one by one, using the RETR command. Just as with the response to the LIST command, the message is terminated with a line with a single period. To avoid problems with messages that contain a period on the first position of a line, an extra period is included. So this must be stripped off. Once the termination line is reached, we test whether we have received the complete message, by comparing the calculated byte count with the stored byte count in the array. If it is OK, the message is deleted from the server. When all messages are retrieved we end the communication with the server, using the QUIT command, after which the DATA step stops. If any of the steps in the dialog resulted in an error response, we create an error message and branch to the error routine at the end of the program, which simply sends a message to the log and stops the DATA step. The program reads all header information and stores that with the rest of the message. If you are not interested in all the header information you could filter the relevant lines by adding a filter routine. That is left to the reader. The program does not cater explicitly for mails with attachments. In case an attachment is present there are header lines that report that the message has multiple parts. These are sent in a continuous stream, with separator lines. Separating the body text from the attachment is handled in the next chapter.

Test run To see the progress of the dialog during testing it makes sense to include a PUTLOG statement after each INPUT statement, like: PUTLOG

"location:"

_INFILE_;

where “location” is a label to recognize after which INPUT the PUTLOG was issued. Thus we can see what is happening in the SAS Log (these statements are not included in Program 9). To test the program two messages were sent from two different senders and retrieved with the program. Listing 3 contains some relevant parts of the SAS log after running the program. Most of the header lines and almost everything of the second message are left out as it would become rather boring…

Using the DATA step as a POP3 mail client

NOTE: The file/infile POP3 is: Local Host Name=notebook, Local Host IP addr=127.0.0.123.129, Peer Hostname Name=pop.wxs.nl, Peer IP addr=213.75.3.24,Peer Name=N/A, Peer Portno=110,Lrecl=256,Recfm=Variable open communication: +OK kpnxchange.com after sending user ID: +OK after sending password: +OK User successfully logged on. after STAT command: +OK User successfully logged on. after LIST command: +OK 2 1728 Scanning end of input: 1 819 Message line: 1 819 Scanning end of input: 2 909 Message line: 2 909 Scanning end of input: . Message line: . after retrieve: +OK Scanning end of message: Received: from cpsmtp-eml105.kpnxchange.com ([10.94.168.105]) by CPEXBE-EML16.kpnsp.local with Microsoft SMTPSVC(6.0.3790.3959); Getting message line: Received: from cpsmtp-eml105.kpnxchange.com ([10.94.168.105]) by CPEXBE-EML16.kpnsp.local with Microsoft SMTPSVC(6.0.3790.3959); Scanning end of message: Wed, 26 Nov 2008 22:31:04 +0100 Getting message line: Wed, 26 Nov 2008 22:31:04 +0100 (...) Scanning end of message: From: Synchrona Getting message line: From: Synchrona (...) Subject: Test message 1: Scanning end of message Content-Type: text/plain; charset=ISO-8859-1; format=flowed Getting message line (...) Getting message line: Read it in the DATA step Scanning end of message (...)

Scanning end of message: . Getting message line: . After DELE command: +OK After retrieve: +OK (...)

Listing 3: Partial SAS log of the test run.

39

40

Test run

Scanning end of message: . Getting message line: . After DELE command: +OK After QUIT command: +OK kpnxchange.com NOTE: 54 records were read from the infile POP3. The minimum record length was 0. The maximum record length was 128. NOTE: 9 records were written to the file POP3. The minimum record length was 4. The maximum record length was 13. NOTE: The data set WORK.MAILFILE has 41 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.39 seconds cpu time 0.20 seconds

Listing 3 (continued): Partial SAS log of the test run. Right after the general information regarding the connection information you recognize the sequence of the log on commands, followed by the STAT command to know whether there are messages. The LIST command is followed by two message lines providing the length of each message. Then starts the section to retrieve the message. First all header lines and finally the body. After reading the termination line the DELE command is issued and the next message is started. After the last message you see the log-off response.

Secure mail server access Unfortunately the SOCKET access method does not support secure access to mail servers. If the mail server from which you want to retrieve the messages requires to connect using SSL/TLS then you have to rely on a third party command-line mail client. One of those is MPOP1. We will use MPOP to show the principle. If you plan to use another program, the configuration file and the commands may be different, but the basic principles are the same. MPOP requires a configuration file with the server settings and log in information. In this configuration file you also specify where the output 1

MOPO is written by Martin Lambers and can be downloaded from http://mpop.sourceforge.net/.

Using the DATA step as a POP3 mail client

41

should be saved. MPOP can place files in a predefined directory, from where it can be processed further. Program 10 provides the framework. As we did earlier we use the SAS WORK library as folder to store our temporary files. Since we need the path to the WORK library several times we store the path in the macro variable work_path. The options to be set in the configuration file look familiar and hardly need further explanation. Except may be 'tls_certcheck off'. This bypasses the check on the security certificate. For more security you should turn this on, but then you will also have provide the fingerprint of the server or information about the security certificate. More information about how to deal with the certificates is given in the documentation of MPOP. As output method we specify 'maildir' and add the location of that mail directory. The mail directory should have 3 sub-folders: tmp, cur, new. These are created with the batch file that will also call MPOP. Next is to build the *.bat file. It starts with the creation of the three required mail folders. Then follows the MPOP command. Note that the MPOP.exe module is not in it's 'natural' location (Program folder on the C-drive). That is no problem. It can be anywhere, just make sure that Windows can find it. In the second part of the program, the created files are listed in the SASLOG to help locate any problems. Finally the command is executed using the CALL SYSTEM routine. The received mails will be in the 'new' folder as text files, each mail with it's own name. From there you can do any further processing.

Protocol analysis While testing programs based on the SOCKET access method it is very handy to have a program that can display exactly what is sent over the TCP/IP line in either direction. A good option for this is Wireshark, which can be downloaded from www.wireshark.org. It has a lot of options, but frankly the defaults are normally enough. Just start the logging, send a message or something similar, stop the logging and analyze.

42

Protocol analysis

%let work_path = %sysfunc(getoption(work)); %let userid = erik.t.sasgf; %let password = XXXXXX; FILENAME config "&work_path\mpopconfig.txt"; FILENAME batfile "&work_path\batfile.bat" lrecl=1024; * Create MPOP config file ; DATA _null_; LENGTH scriptfile bat_file bat_log $100 ; script_file = '"'||"&work_path\script.txt"||'"'; bat_file = '"'||"&work_path\batfile.bat"||'"'; bat_log = '"'||"&work_path\mpop\batlog.txt"||'"'; * Generate config file; FILE config; PUT 'defaults' / 'pipelining off' / 'tls on' / 'tls_certcheck off' / 'tls_starttls off' / 'delivery maildir ' "&work_path\mpop" / 'auth on' / 'only_new on' / 'account popit' / 'host pop.googlemail.com' / 'port 995' / "user &userid" / "password &password" ; * generate a *.bat file to call the mail program; FILE batfile; * create the directory to store the mails; PUT 'mkdir "' "&work_path\mpop\tmp" '"'; PUT 'mkdir "' "&work_path\mpop\cur" '"'; PUT 'mkdir "' "&work_path\mpop\new" '"'; * call the MPOP program; PUT 'f:\mpop\mpop.exe --file="' "&work_path\mpopconfig.txt" '" -- popit > ' bat_log; RUN;

Program 10: Using an external POP3 utility to read mail from a secure server.

Using the DATA step as a POP3 mail client

* Send generated files to log; data _null_; infile config; input; put _infile_; run; data _null_; infile batfile; input; put _infile_; run; * Pick up the mails; data _null_; options noxwait xsync; bat_file = '"'||"&work_path\batfile.bat"||'"'; putlog bat_file; call system(bat_file); run; * Send retrieved information to log; data _null_; infile "&work_path\batlog.txt"; input; put _infile_; run;

Program 10 (continued): Using an external POP3 utility to read mail from a secure server.

43

44

Handling e-mail attachments

Handling e-mail attachments The previous chapter discussed using the DATA step as a POP3 e-mail client. It described the general process to receive and file e-mails, recognize header and contents parts. In this chapter we will focus on how to handle e-mails with attachments.

E-mail Attachments Attachments can be of a text nature or a binary nature. Text attachments include HTML files, comma separated files and so on. Binary attachments can be anything: MS Word documents, spreadsheets, picture files and anything else. Handling an e-mail with attachments is a two step approach. The first step, which is the same for all attachments, is to separate the attachments from the main e-mail body. The second step is handling and filing the attachment itself, The basic handling is similar for both types of attachments, but binary attachments require additional decoding before they can be used. If the e-mail contains attachments, the mail is considered to be a "multipart" message. This information is included in the header line "ContentType: multipart". If you see such header line the next line will be "boundary= "a boundary string" ", a unique string which identifies how the parts are separated. There is no standard layout for the boundary string. Normally it consists of a combination of a series of hyphens and a series of randomly selected characters. Figure 11 shows the record stream you may expect in a multipart message. It starts with all the addressing records and then at some point the boundary definition. Then follow the body of the mail and the attachments, each separated by the boundary line. So to separate the various parts, you just have to scan for a line that contains the boundary string. Each attachment starts with some header lines that are specific for that particular attachment. These include the type, name and coding method of the attachment. Text attachments are relatively easy to handle. Just read the records from the mail and file them as the file type which is included in the header lines of the attachment. Binary attachments are more complicated.

Handling e-mail attachments

45

Received: from cpsmtpi-eml05.kpnxchange.com ([10.94.77.75]) by CPEXBEEML16.kpnsp.local with Microsoft SMTPSVC(6.0.3790.3959); Tue, 5 May 2009 21:22:42 +0200 ... (more header lines) Message-ID: Date: Tue, 05 May 2009 21:22:55 +0200From: Synchrona User-Agent: Thunderbird 2.0.0.21 (Windows/20090302) MIME-Version: 1.0 To: Erik Tilanus Subject: testmail with attachment Content-Type: multipart/mixed; boundary="------------080808080501060405030609" Return-Path: [email protected] X-OriginalArrivalTime: 05 May 2009 19:22:41.0490 (UTC) FILETIME=[DBF6DB20:01C9CDB6] This is a multi-part message in MIME format. --------------080808080501060405030609 (here starts the body of the mail) Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit There are two attachments to this mail. The first is a spreadsheet in BASE64 and the second is a text file Let us get it! --------------080808080501060405030609 (here starts the first attachment) Content-Type: application/x-msexcel; name="test1.xls" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="test1.xls" 0M8R4KGxGuEAAAAAAAAAAAAAAAAAAAAAPgADAP7/CQAGAAAAAAAAAAAAAAABAAAAHwAAAAAA AAAAEAAA/v///wAAAAD+////AAAAAB4AAAD///////////////////////////////////// (this is the BASE64 encoded contents) YQByAHkASQBuAGYAbwByAG0AYQB0AGkAbwBuAAAAAAAAAAAAAAA4AAIB//////////////// AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFgAAAAAQAAAAAAAA --------------080808080501060405030609 (here starts the second attachment) Content-Type: text/plain; name="Example data.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="Example data.txt" A,876543 B,Smith,,John,adt,m,19800515,777777555555,[email protected],456456456,1371 37137,,56 Main Street,,KK11,Princetown,Aruba C,AUA,BON,3P,100,15MAY2009,Y,1,HK, D,Sun Tours,20090210,1010 E,Y3M,100,USD,50/10TX/20AP/20VA F,cc,115,50,USD,,Visa,**********1234,0615,56 Main Street,KK11,Princetown,Aruba G,4561234567899,1 H,BIKE,,10 I,Check the Credit Card on departure

Figure 11: Structure and boundary information of multipart messages.

46

Separating and filing attachments

Separating and filing attachments Program 11 shows a DATA step that reads and interprets the incoming message. For simplicity sake it has been assumed that the message has been read before and stored in a SAS data set: each line in a separate observation. This could have been accomplished with Program 9. The program should be able to write several output files. For that we make use of the FILEVAR= option in the FILE statement. Each time the variable in the FILEVAR= option changes its value, the current output file is closed and de-allocated and a new file is opened. The program scans the relevant tags in the message to locate and separate the attachments. The program assumes that the attachments have to be stored at some predefined location under their original names. Base64 encoded attachments however are first stored in the SAS temporary (work-)directory. A decoding step will put the restored attachment into the defined location. When the boundary information is encountered it is saved in order to be able to recognize the various parts of the message. At the beginning of a new message part we first examine whether the previous part contained a base64 encoded file. If so, we will have to close it before we can start to decode it. Therefore we change the filename in the path_file variable and execute a FILE statement. Then we prepare a call to an external program "base64.exe"2 and call the program to convert the base64 file into its original binary format. As an alternative you can write the decoding routine entirely in SAS. This will be discussed in a later paragraph. Each section of the message contains again several header lines. The "Content-Transfer" line contains the encoding method and the filename= line contains the original name of the attached file. Just before the actual start of the attachment contents there is an empty line. But empty lines could also be part of the contents of the attachment. Therefore we first test whether we are already reading the body of the attachment and if so we link to the output routine. Otherwise the blank line signals the start of the body of the new attachment. If we are dealing with a base64 attachment, it is written to the SAS Work library, otherwise it is written directly to the determined destination location.

2

Base64.exe is written by Fatih Kodak and can be downloaded from www.f2ko.de.

Handling e-mail attachments

47

%LET destination_path = e:\testmail\; DATA MultiPart; LENGTH content_type $30 file_name $255 transfer_encoding $30 path_file $255 boundary $80; RETAIN bodystart 0 content_type " " section_start 0 boundary file_name path_file transfer_encoding ; SET mailfile; SELECT; WHEN (line =: ' boundary=') DO; boundary = SCAN(line,2,'"'); END; WHEN (INDEX(line,TRIM(boundary))) DO; IF transfer_encoding = 'BASE64' THEN DO; path_file="%SYSFUNC(GETOPTION(WORK))\dum"; FILE atchfile FILEVAR=path_file; cmd= 'c:\base64.exe –d "'|| "%SYSFUNC(GETOPTION(WORK))\tmp"|| '" "'||"&destination_path"|| TRIM(file_name)||'"'; CALL SYSTEM(cmd); END; * reset variables for the new part; section_start=1; bodystart=0; file_name=' '; content_type=' '; transfer_encoding=' '; END; WHEN (line =: 'Content-Type:') DO; IF section_start THEN content_type=SCAN(line,2,' '); END; WHEN (line =: 'Content-Transfer') DO; IF section_start THEN DO; transfer_encoding= UPCASE(SCAN(line,2,' ')); END; END; WHEN (line =: ' filename') DO; file_name=SCAN(line,2,'="'),2); END;

Program 11: Analyze a multipart message.

48

Separating and filing attachments

WHEN (line =: " ") DO; IF bodystart THEN LINK write_rtn; ELSE DO; IF section_start THEN bodystart=1; IF file_name NE ' ' THEN DO; IF transfer_encoding='BASE64' THEN path_file= "%SYSFUNC(GETOPTION(WORK))\tmp"; ELSE path_file= "&destination_path"||file_name; END; END; END; OTHERWISE DO; * these are contents lines; IF bodystart = 1 THEN LINK write_rtn; END; END; RETURN; write_rtn: IF file_name = ' ' THEN OUTPUT; ELSE DO; FILE atchfile FILEVAR=path_file; PUT line; END; RETURN; RUN;

Program 11 (Continued): Analyze a multipart message.

Converting binary attachments with SAS The SMTP protocol is based on the 7-bit ASCII character set and limits records to 1000 byte, including the terminating CRLF (carriage controlline feed). Binary files by nature are not compliant with that. Therefore these are converted to text files, normally using the 'Base64' coding methodology (Figure 12). Base64 first expands every three 8-bit bytes into four 6-bit bytes. Next these new bytes are translated into characters using a translation table. If at the end of the input there is no multiple of three bytes left, the base64 encoded file will contain '=' characters in the last one or two positions.

Handling e-mail attachments

49

To translate back from the base64 encoded text file you have to do the opposite. Now comes the caveat. Writing a decoding routine in SAS is not very complicated. So writing out the original binary data to a file should be no problem either, but... The DATA step FILE and PUT statements are not designed to write binary data. You have to write in a continuous stream, rather than in separate records, which is the normal way for FILE and PUT statements. The key to the solution is to define an extremely large logical record length, large enough to contain the complete decoded file.

3 8-bit bytes

Split into 4 6-bit bytes

Expanded to 4 8-bit ASCII characters

Figure 12: The Base64 coding principle.

The conversion routine Program 12 shows the basic routine to convert a Base64 file into its original. It reads a line from the input file and then steps through it, getting chunks of 4 bytes at the time. Then it inspects whether the chunk contains any "filler" characters ('='). The TRANSLATE function is used to translate back from the characters to the 4 x 6 bit binary codes. If there are one or two filler characters, those bytes are set to '00'X. Next follows the manipulation to shift the binary information to its proper location in the 3 x 8 bit bytes. The bit manipulation functions require a numeric input. So we first read each byte into a numeric variable with the IB2. informat. From Figure 12 it can be seen that the contents of the first byte just needs to be shifted to the left, to occupy the high order bits in the result. The two highest significant bits in the second byte should be shifted to the two low order bits in the result.

50

The conversion routine

DATA _NULL_; LENGTH line $1000 block $4 decoded $4 binary $256; RETAIN binptr 1 binary; INFILE 'e:\test.b64' MISSOVER LENGTH=linelength; INPUT line $varying. linelength; DO lineptr = 0 TO INT((linelength-1)/4) ; block = SUBSTR(line,lineptr*4+1,4); filler = INDEX(block,'='); decoded = TRANSLATE(block, '000102030405060708090A0B0C0D0E0F'x, 'ABCDEFGHIJKLMNOP', '101112131415161718191A1B1C1D1E1F'x, 'QRSTUVWXYZabcdef', '202122232425262728292A2B2C2D2E2F'x, 'ghijklmnopqrstuv', '303132333435363738393A3B3C3D3E3F'x, 'wxyz0123456789+/'); IF filler GT 0 THEN SUBSTR(decoded,filler,5-filler)='0000'x; byte1 = INPUT(SUBSTR(decoded,1,1),IB1.); byte2 = INPUT(SUBSTR(decoded,2,1),IB1.); byte3 = INPUT(SUBSTR(decoded,3,1),IB1.); byte4 = INPUT(SUBSTR(decoded,4,1),IB1.); byte1 = BLSHIFT(byte1,2); Highorder = BRSHIFT(byte2,4); byte1 = BOR(byte1,highorder); byte2 = BLSHIFT(byte2,4); Highorder = BRSHIFT(byte3,2); byte2 = BOR(byte2,highorder); byte3 = BLSHIFT(byte3,6); byte3 = BOR(byte3,byte4); SUBSTR(binary,binptr,1)=PUT(byte1,IB2.); binptr+1; IF filler NE 3 THEN DO; SUBSTR(binary,binptr,1)=PUT(byte2,IB2.); binptr+1; END; IF filler EQ 0 THEN DO; SUBSTR(binary,binptr,1)=PUT(byte3,IB2.); binptr+1; END; END; FILE 'e:\test.xls' LRECL=1048575; binptr=binptr-1; PUT binary $VARYING. binptr @; binptr=1; RUN;

Program 12: A base64 decoding routine.

Handling e-mail attachments

51

This is achieved by first shifting that second byte 4 bits to the right (i.e. removing the lower 4 bits and move the highest two significant bits tothe lower bit positions) and then OR-ing them with the first resulting byte. In a similar method the other bits are shifted to their right position in the result. Then we write the resulting one, two or three bytes (depending on the presence of the filler characters) to the output buffer. When a whole input line has been processed we write the converted bytes to the output file. Note the trailing @ in the PUT statement. It means that the next PUT will write to the same output record. That is how we achieve a continuous data stream. Only the last PUT statement will actually write out the file in one big chunk. As mentioned in the introduction of this paragraph you need to define a very large logical record length, larger than the largest attachment you want to handle.

Integrated decoding Now that we have a base64 decoding routing in SAS the logical next step is to integrate it with the program that analyses the mail and files the attachments. (Program 11). The result is Program 13. Comparing Program 13 with Program 11 you see a few differences. When encountering a boundary, we are not checking whether to decode the last attachment, but simply reset the flags. In the routine where the path_file variable is set the routine to route the encoded attachment to a temporary file is removed. It is in the subroutine where we write out the attachment to file is where we check whether this is a base64 encoded record. If so, the decoding subroutine (decoder) is called rather than writing the result out. The decoder subroutine is basically the same as Program 12.

52

Integrated decoding

%LET destination_path = e:\sasbook\; options xwait xsync; filename base64 "&destination_path"; DATA MultiPart; LENGTH content_type $30 file_name $255 transfer_encoding $30 path_file $255 boundary $80 block $4 decoded $4 binary $256; RETAIN bodystart 0 content_type " " section_start 0 boundary file_name path_file transfer_encoding binptr 1 binary;; SET mailfile; SELECT; WHEN (line =: ' boundary=') DO; boundary = SCAN(line,2,'"'); END; WHEN (INDEX(line,TRIM(boundary))) DO; section_start=1; bodystart=0; file_name=' '; content_type=' '; transfer_encoding=' '; END; WHEN (line =: 'Content-Type:') DO; IF section_start THEN content_type=SCAN(line,2,' '); END; WHEN (line =: 'Content-Transfer') DO; IF section_start THEN DO; transfer_encoding= UPCASE(SCAN(line,2,' ')); END; END; WHEN (line =: ' filename') DO; file_name=SCAN(line,2,'="'); END; WHEN (line =: " ") DO; IF bodystart THEN LINK write_rtn; ELSE DO; IF section_start THEN bodystart=1; IF file_name NE ' ' THEN; path_file="&destination_path"|| file_name; END; END;

Program 13: Base64 decoding integrated in Program 11.

Handling e-mail attachments

OTHERWISE DO; * these are contents lines; IF bodystart = 1 THEN LINK write_rtn; END; END; RETURN; write_rtn: IF file_name = ' ' THEN OUTPUT; ELSE DO; FILE atchfile FILEVAR=path_file; IF transfer_encoding='BASE64' THEN do; link decoder; end; else PUT line; END; RETURN; decoder: linelength=length(line); DO lineptr = 0 TO INT((linelength-1)/4) ; block = SUBSTR(line,lineptr*4+1,4); filler = INDEX(block,'='); decoded = TRANSLATE(block, '000102030405060708090A0B0C0D0E0F'x, 'ABCDEFGHIJKLMNOP', '101112131415161718191A1B1C1D1E1F'x, 'QRSTUVWXYZabcdef', '202122232425262728292A2B2C2D2E2F'x, 'ghijklmnopqrstuv', '303132333435363738393A3B3C3D3E3F'x, 'wxyz0123456789+/'); IF filler GT 0 THEN SUBSTR(decoded,filler,5-filler)='0000'x; byte1 = INPUT(SUBSTR(decoded,1,1),IB1.); byte2 = INPUT(SUBSTR(decoded,2,1),IB1.); byte3 = INPUT(SUBSTR(decoded,3,1),IB1.); byte4 = INPUT(SUBSTR(decoded,4,1),IB1.); byte1 = BLSHIFT(byte1,2); Highorder = BRSHIFT(byte2,4); byte1 = BOR(byte1,highorder); byte2 = BLSHIFT(byte2,4); Highorder = BRSHIFT(byte3,2); byte2 = BOR(byte2,highorder); byte3 = BLSHIFT(byte3,6); byte3 = BOR(byte3,byte4);

Program 13 (continued): Base64 decoding integrated in Program 11.

53

54

Integrated decoding

SUBSTR(binary,binptr,1)=PUT(byte1,IB2.); binptr+1; IF filler NE 3 THEN DO; SUBSTR(binary,binptr,1)=PUT(byte2,IB2.); binptr+1; END; IF filler EQ 0 THEN DO; SUBSTR(binary,binptr,1)=PUT(byte3,IB2.); binptr+1; END; END; path_file = "&destination_path"||file_name; FILE atchfile FILEVAR=path_file; binptr=binptr-1; PUT binary $VARYING. binptr @; binptr=1; RETURN; RUN;

Program 13 (continued): Base64 decoding integrated in Program 11.

How to run a program repeatedly?

55

How to run a program repeatedly? The program in the previous chapter (Program 9) just accessed the mail server once to retrieve any messages and stopped. How can we achieve that such program interrogates the mail server repeatedly, e.g. every 10 minutes or every hour? This is not something that is unique for the situation of the mail client, but is applicable in many situations where a program has to run periodically. There are two fundamentally different approaches to this. The first approach is to make use of a mechanism that is present in most operating systems: time initiated dispatch of programs. In Windows this mechanism is called the "task scheduler". The second approach is to implement it completely within SAS. An important difference between these two solutions is that using the operating system means that you start a SAS session when it is time to execute the program, while in the SAS solution a SAS session is always present, but in a dormant state until it is time to run.

Solution 1: Using the operating system A general discussion of how to create scheduled tasks in all available operating systems is beyond the scope of this paper. Let us just look at Windows. The menus and screenshots are based on Windows 7. In Program 9 we accessed the mail server only once and stored the received messages in a WORK data set. For the scheduled program we want to accumulate the incoming mails in a permanent data set. Let us write a little program for that purpose (Program 14). It just allocates the permanent data library, then includes the source of Program 9, runs the PROC APPEND to accumulate all mails over the runs. Let us assume that the program is saved as "MailClient.sas" in the folder "e:\SAS source\". LIBNAME maildata "e:\mails"; %INCLUDE "e:\SASsource\POP3client.sas"; PROC APPEND BASE=maildata.mailfile DATA=mailfile; RUN;

Program 14: Program for scheduled task.

56

Solution 1: Using the operating system In the Control Panel you will find a folder named "Administrative Tools". One of the tools is the Task Scheduler. After opening the Task Scheduler and clicking on the Task Scheduler Libray you see in the center pane an overview of tasks that are already created. In the right pane you find a number of action buttons. Choose 'Create Task' (Figure 13).

Figure 13: The Task Scheduler main window. Create Task opens a new window, where you can specify the task you want to create. On the General tab you fill in some general information, making it easy to recognize the task (Figure 14). On the Triggers tab you define when the program should run. Choose “New” to open the trigger window (Figure 15). For the program at hand we choose to run it daily and repeat it every hour during the day from 7AM to 7PM. The next thing (on the Actions tab) is to define the action that should be performed, which is in our case to run the program MailClient.sas. To start the program we have two choices: invoke SAS directly or create a so called *.BAT file that in turn will invoke SAS. To start SAS directly you have to specify (SAS 9.3): "C:\Program Files\SAShome\SASFoundation\9.3\sas.exe" -SYSIN "E:\SASsource\MailClient.sas" -LOG "E:\Logfiles\mail.log" -PRINT "E:\Logfiles\mail.lst"

How to run a program repeatedly?

57

For SAS 9.4 you have to replace 9.3 by 9.4. The rest are arguments that SAS needs to run the right program: -SYSIN specifies the program to run and -LOG and -PRINT define where the SAS Log and the SAS List are stored.

Figure 14: Creating a new task for the POP3 Client program.

Figure 15: Create a new trigger.

58

Solution 1: Using the operating system When you paste the command string to run the program, Windows will give a warning that you are including extra arguments to the program to run (Figure 16). In the program line it wants only the program and no further arguments. No problem. When confirming this warning Windows will move the arguments to designated field (Figure 17). The Conditions and Settings tabs (Figure 14) allow to specify more options, however for our purpose the default settings will be sufficient.

Figure 16: Warning about the arguments added to the SAS invocation.

Figure 17: The completed Action panel.

How to run a program repeatedly?

59

Solution 2: Repeated tasks within SAS The SLEEP function pauses the processing of a DATA step the specified time and then resumes processing. By wrapping it in the %SYSFUNC macro function, you can use it within a macro. That is the basis for this solution. We create a macro that runs "forever" (Program 15), at least as long as macro variable Quit is not equal to 1. Each iteration of the DO-loop in the macro starts with the sleep period. In the example it is 3600 seconds. Then it fires off another macro, called %getmail, which is just Program 14 with a %MACRO statement in front and a %MEND statement at the end. It will be obvious that the counter and the %PUT statements are only there to send some information to the log, but are not relevant for the result. How do you change Quit to 1 while the macro is running? That is accomplished via the %INCLUDE of the file "setexit.sas". This file contains just one line: %LET Quit = 0; When you open that file outside SAS, e.g. with Notepad and change the statement into %LET Quit = 1; while the macro execution is in it's sleep phase then the next %INCLUDE sets Quit to 1 and the loop is terminated and the macro ends. %macro timer; %let quit=0; %let counter=0; %do %until (&quit = 1); %let counter = %eval(&counter +1); %put sleep start &counter; %let x = %sysfunc(sleep(3600,1)); %put sleep end; %getmail; %include 'e:\SASsource\setexit.sas'; %end; %mend; %timer

Program 15: Driver to run a program repeatedly The sleep period in the SLEEP function is in seconds. It is specified using two arguments. The first argument specifies the quantity and the second is a multiplication factor. Sleep(30,1) sleeps for 30 seconds. Sleep(15,0.1) sleeps for 1.5 second. Sleep(0.001,3600) sleeps for 3.6 seconds. The default multiplication in the Windows environment is 1 (one second) and in other system environments 0.001 (milliseconds). The maximum period is 47 days.

60

Solution 2: Repeated tasks within SAS When you are just busy updating the setexit.sas file at the moment that the %INCLUDE is executed, SAS will issue an error indicating that it cannot include the file and continue with the macro. Once you have saved and closed the file, the macro will pick up the new value during its next round.

Recommended Reading

61

Recommended Reading More on the POP3 protocol can be found in the document on the web site www.faqs.org/rfcs/. Look for the pages RFC 1939, RFC 2449 and RFC 2595. More about the CONTENT_TYPE possibilities can be found in the specification papers RFC 2025 and RFC 1123. The bit-shifting functions and the translate function are documented in the SAS 9.3 (9.4) Functions and CALL routines Reference. A general description of several of the FILENAME options can be found in the paper: Michael Davis: Reading From Alternate Sources: What To Do When The Input Is Not a Flat File, SUGI 27 Proceedings p-006-27 The SAS 9.3 (9.4) Statements Reference contains the formal description of the FILENAME – SOCKET statement. The FILENAME, FILE, PUT statements and options in these statements that are not handled here as they are rarely necessary, are documented in SAS® 9.3 (9.4) Statements Reference. Some system-specific details can be found in SAS® Companion for Windows. The FOPEN and FINFO functions are documented in SAS® 9.3 (9.4) Functions and CALL routines Reference. A general description of the email interface can be found in SAS® 9.3 (9.4) Language Reference Concepts. The %SYSFUNC macro function is documented in SAS® 9.3 (9.4) Macro Language Reference The information about the OUTLOOK pop-up message can be found in the Microsoft Knowledge base under article ID: 2723336 For more information about using ODS, including options to customize the layout you are referred to the SAS® 9.3 (9.4) Output Delivery System: User’s Guide.

62

Index

Index A

EXPIRES= (FILENAME option)..............16

ATTACH= (FILENAME option).............15 Attachments...............................................14

F

B Base64 coding principle............................48 Base64.exe..................................................46 BCC= (FILENAME option)......................15 Binary data output....................................49 Boundary= (mail header line)..................44

C CC= (FILENAME option).........................15 CONTENT_TYPE= (FILENAME option) ...............................................................16, 18

D DELIVERYRECEIPT (FILENAME option) .....................................................................16 Display name.............................................11

E EMAILACKWAIT= (System Option).......8 EMAILAUTHPROTOCOL= (System Option).........................................................8 EMAILFROM (System Option).................8 EMAILHOST (SMTP).................................7 EMAILHOST= (System Option)................8 EMAILID (MAPI)........................................5 EMAILID (SMTP)........................................7 EMAILID= (System Option)......................8 EMAILPORT= (System Option)................8 EMAILPW (SMTP)......................................7 EMAILPW= (System Option)....................9 EMAILSYS (MAPI).....................................5 EMAILSYS (SMTP).....................................6 EMAILSYS= (System Option)....................9 EMAILUTCOFFSET= (System Option)....9 ENCODING= (FILENAME option)........16 Endless macro............................................59

FILE statement...........................................11 FILENAME .. MAIL options....................15 FILENAME statement..............................11 FILEVAR= option (FILE statement)........46 FINFO function..........................................17 FOPEN function........................................17 FROM= (FILENAME option)...................15

G GMAIL..........................................................7 Graphic output..........................................22

H HOTMAIL....................................................7 HTML output.............................................19

I IB2. informat..............................................49 IMPORTANCE= (FILENAME option)...16 Invoke SAS.................................................56

L LRECL= (FILENAME option)..................16

M Macro..........................................................59 MAPI.............................................................3 MAPI interface.............................................5 MPOP..........................................................40 Multipart message.....................................44 Multiple messages.....................................13

N NOEMAILFROM (System Option)...........8

Index

O ODS output in mail...................................18 Options statement.......................................4 OUTLOOK POP-UP....................................9

P Personalized bulk mail.............................27 POP3 mail client........................................33 POP3 protocol............................................33 PROC APPEND.........................................55 PROC TABULATE....................................19 Protocol analysis........................................41 PUTLOG statement...................................38

63

T Task Scheduler (MS Windows)...............56 Temporary files.........................................41 TERMSTR=CRLF (FILENAME option). .35 Time initiated dispatch of programs.......55 TO= (FILENAME option).........................15 Trailing @ (PUT statement)......................51 TRANSLATE function..............................49

V VIM...............................................................3 VIM interface.............................................10

R

W

READRECEIPT (FILENAME option).....16 REPLYTO= (FILENAME option)............16 RTF output.................................................19

Wireshark (packet analyzer)....................41

S

!EM_ABORT! (PUT statement)................25 !EM_ATTACH! (PUT statement).............25 !EM_BCC! (PUT statement)......................25 !EM_CC! (PUT statement)........................25 !EM_DELIVERYRECEIPT! (PUT statement)...................................................25 !EM_EXPIRES! (PUT statement)..............25 !EM_FROM! (PUT statement)..................25 !EM_IMPORTANCE! (PUT statement)...25 !EM_NEWMSG! (PUT statement)...........25 !EM_REPLYTO! (PUT statement)............25 !EM_SEND! (PUT statement)...................25 !EM_SUBJECT! (PUT statement).............25 !EM_TO! (PUT statement)........................25 !EM_xxx! directives (PUT statement)......24

SAS configuration file.................................3 Secure connection........................................7 Secure mail server access..........................40 SLEEP function..........................................59 SMTP............................................................3 SMTP interface............................................6 SMTP server.................................................6 SOCKET access method...........................34 Speed throttle.............................................28 SSL................................................................7 SSL/TLS.....................................................40 STARTTLS....................................................7 SUBJECT= (FILENAME option)..............15 System Options............................................3

Other

64

Contact Information

Contact Information Your comments and questions are valued and encouraged. Contact the author at: Name: Address: Postal code: City: Country: Phone: Fax: Email: Website:

Erik Tilanus Horstlaan 51 3971 LB Driebergen the Netherlands +31 343 517 007 +31 842 152 107 [email protected] www.synchrona.nl

At the website you can also find other presentations by the author, held at previous SUGI and SAS Global Forum meetings.