Chapter Contents
Previous
Next
SAS Companion for the Microsoft Windows Environment

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

SAS provides an access engine NOTESDB to enable client users to add new Notes documents to an existing Notes database. This engine is compatible with Lotus Notes release 4.x (32-bit only) for the SAS System Version 8.

Creating New Notes Documents

The NOTESDB access engine enables users to add data to an existing Notes database. However, the NOTESDB access engine does not provide a way for retrieving information from existing Notes documents. The NotesSQL ODBC driver allows for retrieval of data from Notes databases. See Retrieving Information from Pre-Existing Notes Documents for these details.

Using the NOTESDB access engine and the DATA step or SCL code, you can create new Notes documents. However, the engine cannot create a new database. A client version of Lotus Notes and a valid Notes user ID certification must be installed on the machine that will be using the NOTESDB engine.

Note:   The Notes directory must be in the system path.  [cautionend]

However, Lotus Notes does not have to be running in order for the SAS System to access it. The user will be prompted for a password to access the Notes server through the SAS System.

In general, DATA step or SCL code that interacts with a Notes database has the following components:


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 with 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]

The data directives you can use are:

!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 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 with this directive before you can access a Notes database from SAS. If you specify this directive more than once, the database specified in the most recent PUT statement is used.

!NSF_FORM! form-name
specifies the form 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 specified in the most recent PUT statement with 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 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 with the !NSF_ATTACH! directive. To attach multiple files, use separate PUT statements with !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 with the !NSF_FIELD! directive and the same field name will concatenate the information in that field. Also, PUT statements with no directives are concatenated to the last field name submitted, or they are ignored if no PUT statements with !NSF_FIELD! directives have previously been submitted.

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

Use these directives to perform actions on the Notes database:

!NSF_ADD!
adds a document to the Notes database at that point in time within the DATA step program.

!NSF_ABORT!
indicates to abort from adding 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. This directive negates this behavior.

!NSF_CLR_FIELDS!
clears all the field values that were held using the !NSF_FIELD! directive. This directive in conjunction with !NSF_ADD! is used to facilitate writing DATA step programs with loops that add multiple notes to multiple databases.

!NSF_CLR_ATTACHES!
clears all the field values that were held using the !NSF_ATTACH! directive. This directive in conjunction with !NSF_ADD! is used to facilitate 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 associated with the most recent field value.  [cautionend]

Examples of Populating Lotus Notes Databases

Using the Business Card Request Database uses the Business Card Request database 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;

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 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 that is placed in the Status field for the particular document. Line 16 executes these SAS statements. A new Notes document is created in the Business Card Request database.

Creating a New Document from a Data Set 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 cards;
06 123472 342 Strong Increase
07 423257 33  Just enough
08 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 Business Card Request database.

Expanding on the Business Card Request database example, you can create multiple Notes documents within a single DATA step or SCL code by using action directives as well as data directives. Creating Multiple Notes Documents within a Single DATA Step 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-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-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 that 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 prior to the execution of 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:

Exporting SAS/GRAPH Output into a Notes Document uses the modified syntax.

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


Exporting SAS/GRAPH Output into a Notes Document


01   title1 'US Energy Consumption for 1955-1988';
02   proc gplot data=3Dsampsio.energy1;03;
03   plot consumed*year / des=3D'D0319U01-
04         1';04  run; 
05   dm 'graph1; export "c:\usenergy.bmp" "format=3DBMP"';
06   quit; 
07 
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-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-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 < rather than ! 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 allows users to retrieve information about existing Notes documents in a Notes database.

Note:   This assumes that a Notes ODBC driver and datasource have been configured.   [cautionend]

Both Windows and Windows NT 4.0 with Service Pack 3 require the following software:

Note:   Installation of Service Pack 3 for Windows NT 4.0 upgrades the ODBC drivers to version 3.0 (3.0.23.1 driver manager). This may cause other ODBC 2.0 drivers to fail. Consult your third-party vendor's ODBC reference.   [cautionend]

After you have the software, you must:

  1. Set up the NotesSQL ODBC driver.

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

  2. Configure the ODBC datasource.

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

    Note:   Examples are in parentheses.  [cautionend]

Retrieving Information from Pre-Existing Notes Documents

The SAS/ACCESS to ODBC pass-through engine allows users to retrieve information about existing Notes documents in a Notes database. Using ODBC to Retrieve Information from Pre-existing 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 Pre-existing 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;
07  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 which 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 may 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 allows you to easily build queries without being familiar with SQL. You can invoke it by issuing the QUERY command from the command line.

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


Chapter Contents
Previous
Next
Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.