Using Lotus Notes to Distribute SAS Data under Windows

Populating a Lotus Notes Database Using the DATA Step and SCL Code

Creating New Notes Documents

SAS Statements to Interact with Notes

DATA step and SCL code that interacts with a Notes database has the following components:

a FILENAME statement that includes the NOTESDB device-type keyword
PUT statements that contain data directives and the data to place in the Notes database
PUT statements that contain action directives to control when to send the data to the Notes database.

Syntax for Populating a Lotus Notes Database

FILENAME fileref NOTESDB;

where:

fileref: is a valid fileref.
NOTESDB: is the device-type keyword that indicates that you want to use a Lotus Notes database.

In your DATA step, use PUT statements that have data directives to define which database you want to use and the data you want to send.

Note: Although the directives that you specify to access a Notes database are not case-sensitive, the fields that you specify using those directives are. Also, only one directive per PUT statement is permitted. Each directive should be delimited with an exclamation point and surrounded with single quotes. [cautionend]

Use these data directives to specify the database location and the data to add to the database:

!NSF_SERVER! server-name

indicates the Notes server to access, where server-name represents a Lotus Notes server. If you do not specify this directive, SAS uses your local system as the source for the databases. If you specify this directive more than once, the server that was specified in the most recent PUT statement is used.

Note: If you attempt to access a Notes server through SAS, you will be prompted for your password to the server. [cautionend]

!NSF_DB! database-filename

indicates the Notes database file to access. When accessing a database locally, SAS looks for the database in the Notes data directory. If it is not found there, SAS searches the system path. Alternatively, you can specify the fully qualified path for the database. You must specify a Notes database file that has this directive before you can access a Notes database from SAS. If you specify this directive more than once, the database that was specified in the most recent PUT statement is used.

!NSF_FORM! form-name

specifies the form that Notes should use when displaying the added note. If this directive is not specified, Notes uses the default database form. If you specify this directive more than once, the form that was specified in the most recent PUT statement that has the !NSF_FORM! directive is used.

!NSF_ATTACH! filename

attaches a file to the added note. SAS looks for the file in the Notes data directory. If it is not found there, SAS searches the system path. Alternatively, you can specify the fully qualified path for the file. You can attach only one file in a single PUT statement that has the !NSF_ATTACH! directive. To attach multiple files, use separate PUT statements that have !NSF_ATTACH! directives for each file.

!NSF_FIELD! field-name!field value

adds the value to the field name specified. SAS detects the correct format for the field and formats the data accordingly. Note that SAS extracts all line feeds or carriage returns; you should not insert any of these control characters as they affect the proper display of the document in Notes. Multiple PUT statements that have the !NSF_FIELD! directive and the same field name will concatenate the information in that field. Also, PUT statements that have no directives are concatenated to the last field name submitted, or they are ignored if no PUT statements that have !NSF_FIELD! directives have previously been submitted.

You can populate fields, which can be edited, of the following types:

text
numeric
keywords.

You can add text which will be formatted by Lotus Notes. You can also add a bitmap (in Windows bitmap format) using the following form:

!NSF_FIELD! field-name <bitmap-filename>

Use these action directives to perform actions on the Notes database:

!NSF_ADD!: immediately adds a document to the Notes database within the DATA step program.
!NSF_ABORT!: indicates not to add the note when closing the data stream. By default, the driver attempted to add a note at the end of a SAS program for every FILE statement used.
!NSF_CLR_FIELDS!: clears all the field values that were specified by the !NSF_FIELD! directive. This directive in conjunction with !NSF_ADD! facilitates writing DATA step programs with loops that add multiple notes to multiple databases.
!NSF_CLR_ATTACHES!: clears all the field values that were specified by the !NSF_ATTACH! directive. This directive in conjunction with !NSF_ADD! facilitates writing DATA step programs with loops that add multiple notes to multiple databases.

Note: The contents of PUT statements that do not contain directives are concatenated to the data that is associated with the most recent field value. [cautionend]

Examples of Populating Lotus Notes Databases

The following example uses the Business Card Request database that is supplied by Lotus Notes. This DATA step creates a new document in the database and supplies values for all of its fields.

Using the Business Card Request Database

01 filename reqcard NOTESDB;
02 data _null_;
03  file reqcard;
04  put '!NSF_DB! examples\buscard.nsf';
05  put '!NSF_FIELD!Status! Order';
06  put '!NSF_FIELD!Quantity! 500';
07  put '!NSF_FIELD!RequestedBy! Systems';
08  put '!NSF_FIELD!RequestedBy_CN! Jane Doe';
09  put '!NSF_FIELD!NameLine_1! Jane Doe';
10  put '!NSF_FIELD!NameLine_2! Developer';
11  put '!NSF_FIELD!AddressLine_1! Software R Us';
12  put '!NSF_FIELD!AddressLine_2! 123 Silicon Lane';
13  put '!NSF_FIELD!AddressLine_3! Garner, NC 27123';
14  put '!NSF_FIELD!AddressLine_4! USA';
15  put '!NSF_FIELD!PhoneLine_1! (910) 777-3232';
16 run;

The next example uses each observation in the SALES data set to create a new document in the qrtsales.nsf database and fills in the Sales , Change , and Comments fields for the documents.

Creating a New Document from a Data Set

01 data sasuser.sales;
02    length comment $20;
03    format comment $char20.;
04    input sales change comment $ 12-31;
05 datalines;
06 123472 342 Strong Increase
07 423257 33  Just enough
09 218649 4   Not high enough
09 ;
10 run;
11 filename sales NOTESDB;
12 data _null_;
13    file sales;
14    set sasuser.sales;
15    put '!NSF_DB! qrtsales.nsf';
16    put '!NSF_FORM! Jansales';
17    put '!NSF_ADD!';
18    put '!NSF_FIELD!Sales !' sales;
19    put '!NSF_FIELD!Change!' change;
20    put '!NSF_FIELD!Comments!' comment;
21    put '!NSF_CLR_FIELDS!';
22 run;

Line 11 assigns a fileref by using the FILENAME statement to point to Notes instead of to an ordinary file. NOTESDB is the device type for Lotus Notes. Line 13 uses the assigned fileref to direct the output from the PUT statement. In line 15, the NSF_DB data directive indicates which Notes database to open. Lines 18, 19, and 20 specify the field and its value for the new Notes document that is being created. Sales is the field name and sales is the value that is placed in the Status field for the particular document. Line 22 executes these SAS statements. A new Notes document is created in the Sales database.

Expanding on the Business Card Request database example, you can create multiple Notes documents within a single DATA step or within SCL code by using action directives as well as data directives. The following example shows how to create multiple Notes documents within a single DATA step.

Creating Multiple Notes Documents within a Single DATA Step

01 filename reqcard NOTESDB;
02 data _null_;
03   file reqcard;
04   put '!NSF_DB!Examples\buscard.nsf';
05   put '!NSF_FIELD!Status! Order';
06   put '!NSF_FIELD!Quantity! 500';
07   put '!NSF_FIELD!RequestedBy!Systems';
08   put '!NSF_FIELD!RequestedBy_CN! Jane Doe';
09   put '!NSF_FIELD!NameLine_1! Jane Doe';
10   put '!NSF_FIELD!NameLine_2! Developer';
11   put '!NSF_FIELD!AddressLine_1! Software R Us';
12   put '!NSF_FIELD!AddressLine_2! 123 Silicon Lane';
13   put '!NSF_FIELD!AddressLine_3! Garner, NC 27123';
14   put '!NSF_FIELD!AddressLine_4! USA';
15   put '!NSF_FIELD!PhoneLine_1! (910) 555-3232';
16   put '!NSF_ADD!';
17   put '!NSF_CLR_FIELDS!';
18   put '!NSF_FIELD!Status! Order';
19   put '!NSF_FIELD!Quantity! 10';
20   put '!NSF_FIELD!RequestedBy! Research and Development';
21   put '!NSF_FIELD!RequestedBy_CN! John Doe';
22   put '!NSF_FIELD!NameLine_1! John Doe';
23   put '!NSF_FIELD!NameLine_2! Analyst';
24 put '!NSF_FIELD!AddressLine_1! Games Inc';
25 put '!NSF_FIELD!AddressLine_2! 123 Software Drive';
26 put '!NSF_FIELD!AddressLine_3! Cary, NC 27511';
27 put '!NSF_FIELD!AddressLine_4! USA';
28 put '!NSF_FIELD!PhoneLine_1! (910) 555-3000';
29 run;

Line 1 assigns a fileref by using the FILENAME statement to point to Notes instead of to an ordinary file. NOTESDB is the device type for Lotus Notes. Line 3 uses the assigned fileref to direct the output from the PUT statement. Line 4 indicates which Notes database to open. Lines 5 to 15 specify the field and the value for that field for the new Notes document that is being created. Status is the field name and Order is the value placed in the Status field for this particular document. Line 16 forces the creation of a new Notes document. Line 17 clears the values for the fields that are used with the !NSF_FIELD! data directives in the previous lines. Lines 18 to 28 specify the field and the value for that field for the second Notes document that is being created. Status is the field name and Order is the value placed in the Status field for the second document. Line 29 executes these SAS statements. A second Notes document is created in the Business Card Request database.

Only one !NSF_DB! data directive is issued in the preceding example. By default, the second Notes document is created in the same database as the one referenced in the !NSF_DB! data directive on line 4. In order to create the second Notes document in another database, you would have to issue another !NSF_DB! data directive with the new database filename before executing line 18. The key additions to this example are the action directives on lines 16 and 17.

Note: All directives are not case sensitive. However, the values following the data directives, such as form name and field name, are case sensitive. [cautionend]

Preparing SAS/GRAPH Output for a Notes Document

SAS/GRAPH output can be passed to a Notes document through the NOTESDB access engine. A slight variation of the syntax for the !NSF_FIELD! data directive enables SAS/GRAPH output to be directed to a rich text format field in a Notes document. The procedure is:

Export the SAS/GRAPH output to a bitmap file format.
Use the modified !NSF_FIELD! data directive syntax to assign the value of the bitmap filename to an RTF field. Syntax is:

!NSF_FIELD! RTF-field-name < bitmap-filename

The following example uses the modified syntax.

Note: This example uses the Electronic Library sample database. [cautionend]

Exporting SAS/GRAPH Output into a Notes Document

   01 filename myfile 'test1.bmp';
   02  goptions device=bmp gsfname=myfile gsfmode=replace;
   03  title1 'US Energy Consumption for 1955-1988';
   04  proc gplot data=3Dsampsio.energy1;
   05  plot consumed*year / des=3D'D0319U01-1';
   06 run; 
   07 quit; 
 
   08 filename newdoc NOTESDB;
   09 data _null_;
   10 file newdoc;
   11  put '!NSF_DB!Examples\hrdocs.nsf';
   12  put '!NSF_FIELD!Subject! US Energy Consumption';
   13  put '!NSF_FIELD!Categories! Office Services';
   14  put '!NSF_FIELD!Body! US Energy Consumption for 1955-1988';
   15  put '!NSF_FIELD!Body<c:\usenergy.bmp';
   16 run;

Lines 1 to 6 contain code that is taken from the SAS/GRAPH samples by using a sample data set to generate SAS/GRAPH output. Line 8 assigns a fileref by using the FILENAME statement to point to Notes instead of to an ordinary file. NOTESDB is the device type for Lotus Notes. Line 10 uses the assigned fileref to direct the output from the PUT statement. Line 11 indicates which Notes database to open. Lines 12 to 14 specify the field and the value for that field for the new Notes document that is being created. Subject is the field name and US Energy Consumption is the value that is placed in the Subject field for this particular document. Line 15 indicates a display of usenergy.bmp bitmap file in the Body field because the less than symbol (<) rather than exclamation point (!) is used to separate the field value from the field name. Line 16 executes these SAS statements. A new Notes document is created in the Electronic Library database.

In the preceding example, the Detailed field is an RTF field. When using RTF fields, you can intersperse data and bitmaps.

Using SAS with the NotesSQL ODBC Driver

SAS also provides a SAS/ACCESS to ODBC pass-through engine that enables you to retrieve information about existing Notes documents in a Notes database. You can retrieve text fields only. Graphical data cannot be retrieved.

Note: You must have configured a Notes ODBC driver and data source. [cautionend]

The following software is required:

Lotus Notes Client Version 7.0.2 or later (32-bit only)
ODBC Driver NotesSQL 3.02 (32-bit).

NotesSQL is an ODBC driver that is provided by Lotus. It can be downloaded free of charge from http://www.lotus.com .

After you have the software, you must

Set up the NotesSQL ODBC driver.

Lotus provides a file (.nfs) that explains how to set up the driver.
Configure the ODBC data source.

You must complete the Lotus Notes ODBC 2.0 Setup screen. Add the appropriate information to these fields:

Note: Examples are in parentheses.
- Data Source name (buscard )
- Description (Test Notes Access )
- Server or Database name (c:\notes\data\buscard.nsf )
- NotesSQL Options Setup, which contains these fields:
  - Max Length of Text Fields (254)
  - Max Number of Tables (20)
  - Max Number of Subqueries (20)
  - Max Length of Rich Text Fields (512)
  - SQL Statement Length (4096)
  - Thread Timeout in seconds (60).
    
    Click OK after you have completed the Setup screen.

Retrieving Information from Preexisting Notes Documents

The SAS/ACCESS to ODBC pass-through engine enables you to retrieve information about existing Notes documents in a Notes database. Using ODBC to Retrieve Information from Preexisting Notes Documents shows an example of how to use the DATA step to retrieve information from the Business Card Request database.

Using ODBC to Retrieve Information from Preexisting Notes Documents

01  proc sql;
     02  connect to ODBC ("dsn=3Dbuscard");
     03  create table sasuser.buscard as
     04  select * from connection to
     05  ODBC (select * from All_Requests_By_Organization);
     06  disconnect from ODBC;
  run;

Line 1 processes SQL statements to manipulate SQL views and tables. Line 2 connects to ODBC, which establishes a connection to Notes through the SAS/ACCESS to ODBC driver and the NotesSQL ODBC driver by using the 3Dbuscard data source. Lines 3, 4, and 5 create a table and sasuser.buscard from the data that is retrieved from the Notes Business Card Request database table that is called All_Requests_By_Organization . This is the default view that is assigned to the Business Card Request database. Line 6 disconnects from ODBC and closes the connection to the Notes database. Line 7 executes these SAS statements. A new data set named buscard is created in the Sasuser library.

As another alternative, you can view the available tables within Notes databases by using the SQL Query Window. The SQL Query Window, a component of SAS, is an interactive interface that enables you to easily build queries without writing programming statements. You can invoke it by issuing the QUERY command from the command line.

For more information about PROC SQL, see Base SAS Procedures Guide.

Top of Page