Chapter Contents
Previous
Next
SAS Companion for the OS/2 Environment

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

Client and Server Implementations

SAS provides the 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 and later (32-bit only).

Version 8 supports Notes Server Version 4.x or higher (32-bit only). However, because the NOTESDB access engine communicates directly with the client version of Notes, there are no restrictions on the types of platforms on which the Notes Server can run.

Creating New Notes Documents

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. To create a Notes document, make sure that a client version of Lotus Notes and a valid user ID certification are installed on the machine that will be using the NOTESDB engine.

Note:   Be sure that the Notes directory is in the system path.  [cautionend]

Lotus Notes does not have to be running in order for the SAS System to access it. You 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 that 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 that is 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 you access 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 that is 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 is 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 folder. 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 that is specified. SAS detects the correct format for the field and formats the data accordingly. Note that SAS deletes all line feeds or carriage returns; you should not insert any of these control characters since they affect the proper display of the document in Notes. Multiple PUT statements with the !NSF_FIELD! directive and the same field name concatenate the information in that field. Also, PUT statements with no directives are concatenated to the last field name that was 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 within the DATA step program.

!NSF_ABORT!
indicates not to add the note when SAS closes the data stream. By default, the driver attempts to add a note at the end of a SAS program for every FILE statement that is used. This directive negates this behavior.

!NSF_CLR_FIELDS!
clears all the field values that were held and that use 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 held and that use 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 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 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;

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 Quarterly Sales 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 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 that is 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 must 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:   Not all directives are case-sensitive. However, the values that follow 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. Follow these steps:

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';
     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 < rather than the 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.

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 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;
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 ODBC driver and the NotesSQL ODBC driver by using the 3Dbuscard data source. Lines 3, 4, and 5 create a table and the permanent SQL table, sasuser.buscard, from the data that are retrieved from the Notes Business Card Request database table. The table 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 the SQL procedure, see SAS Procedures Guide.


Chapter Contents
Previous
Next
Top of Page

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