Using the SAS/ACCESS Interface to CA-IDMS

Using the CA-IDMS INPUT Statement

Definition of the CA-IDMS INPUT Statement

If you are unfamiliar with the INPUT statement, refer to SAS Language Reference: Dictionary for more information.

An INPUT statement reads from the file specified by the most recently executed INFILE statement. If the INFILE statement is a CA-IDMS INFILE statement, the INPUT statement issues a CA-IDMS function call as formatted by variables specified in the INFILE statement.

There are no special options for the CA-IDMS INPUT statement as there are for the CA-IDMS INFILE statement. The form of the CA-IDMS INPUT statement is the same as that of the standard INPUT statement:

INPUT <specification-1 > <...specification-n > <@|@@ >;

For example, suppose you issue an OBTAIN function call for the EMPLOYEE record. The CA-IDMS INPUT statement might be coded as follows:

input @1   employee_id      4.0
      @5   firstname        $char10.
      @15  lastname         $char15.
      @30  street           $char20.
      @50  city             $char15.
      @65  state            $char2.
      @67  zip              $char9.
      @76  phone            10.0
      @86  status           $char2.
      @88  ssnumber         $char9.
      @97  startdate        8.0
      @105 termdate         8.0
      @113 birthdate        8.0;

When this CA-IDMS INPUT statement executes, the DATA step interface generates and submits a function call from the options you entered on the CA-IDMS INFILE statement. If the FUNC= variable specified in the INFILE statement is assigned a value of GET or OBTAIN, an EMPLOYEE record is retrieved and placed in the input buffer. Data for the variables specified in the CA-IDMS INPUT statement are then moved from the input buffer to SAS variables in the program data vector.

Depending on which options you specify in the CA-IDMS INFILE statement and which form of the CA-IDMS INPUT statement you use, the INPUT statement will do one of the following:

retrieve a record from the database, place it into the input buffer without moving any variables into the program data vector, and possibly hold the record for the next INPUT statement. If the FUNC= variable specifies GET or OBTAIN, but the INPUT statement does not list any variables, then data is placed into the input buffer without being moved into the program data vector. If the INPUT statement specifies a trailing @ or @@, the record is held for processing by the next INPUT statement. See The Null INPUT Statement and Holding Records in the Input Buffer for more information.
retrieve a record from the database, place it into the input buffer, move data from the input buffer into variables in the program data vector, and possibly hold the record for the next INPUT statement. If the FUNC= variable specifies GET or OBTAIN, and the INPUT statement specifies one or more variables, then data is placed into the input buffer and mapped into variables in the program data vector. If the INPUT statement specifies a trailing @ or @@, the record is held for processing by the next INPUT statement. See Holding Records in the Input Buffer for more information.
submit a DBMS request without retrieving a record. If the FUNC= variable specifies BIND, FIND, ACCEPT or RETURN, then no record data is retrieved from the database. These functions are described in Specifying DML Function Calls. See The Null INPUT Statement for more information.
release a previously held record from the input buffer. If the previous INPUT statement specified a trailing @ or @@, and the current INPUT statement is a null INPUT statement (input; ), then the previously held record is released. See Holding Records in the Input Buffer for more information.

Note: Every time SAS encounters a CA-IDMS INPUT statement, it increments by one an internal counter that keeps track of how many function calls are issued from the input data set. The count is printed to the SAS log as a NOTE. Because you can code several CA-IDMS INPUT statements that do not retrieve data, this count might not accurately reflect the actual number of records retrieved from the database. [cautionend]

Although the syntax of the CA-IDMS INPUT statement and the standard INPUT statement are the same, your use of the CA-IDMS INPUT statement is often different. Suggested uses of the CA-IDMS INPUT statement are described in the following sections.

The Null INPUT Statement

When an INPUT statement does not specify any variable names or options, it is called a null INPUT statement:

input;

A null INPUT statement serves three purposes:

A null CA-IDMS INPUT statement generates and submits a CA-IDMS function call to the database. To issue a CA-IDMS function call that does not retrieve data (FIND, ACCEPT, RETURN, and BIND), use a null INPUT statement.
A null CA-IDMS INPUT statement retrieves a record from the database and places it in the input buffer, but does not move data values to the program data vector. When you want to issue an OBTAIN or GET function call, you can use the INPUT statement with a trailing '@' or '@@' to retrieve a record from the database, then check the status code returned from CA-IDMS before moving data values to the program data vector.
If the previous INPUT statement was input @; or input var1 var2 var3 @; , a null INPUT statement releases the previously held record. See Holding Records in the Input Buffer for information.

Holding Records in the Input Buffer

The trailing @ and @@ pointer controls tell SAS to hold the current record in the input buffer so that it can be processed by a subsequent INPUT statement. The trailing @ tells SAS to hold the record for the next INPUT statement in the same iteration of the DATA step. The double trailing @ tells SAS to hold the record for the next INPUT statement across iterations of the DATA step.

Assuming the FUNC= variable in your INFILE statement specifies GET or OBTAIN, the following INPUT statement submits a function call to the database, retrieves a record from the database, places it in the input buffer, and places a hold on the buffer:

input @;

The next INPUT statement that is executed does not issue another function call and does not place a new record in the input buffer. Instead, the second INPUT statement uses the data placed in the input buffer by the first INPUT statement.

If your INPUT statement also specifies variable names, then that statement issues a function call to the database, retrieves a record, places the record into the input buffer, and moves data values for the named variables into the program data vector:

input ssnumber $char11. @;

SAS holds the record in the input buffer for use with the next INPUT statement.

If you have used an INPUT statement with a trailing @ or @@, and you now want to release the record from the input buffer, use a null INPUT statement as described in The Null INPUT Statement.

Checking Call Status Codes

For each function call issued, CA-IDMS returns a call status code that indicates whether the function call was successful. Because the success of a function call can affect the remainder of the program, you should check call status codes after every call to CA-IDMS. SAS provides the automatic SAS variable _ERROR_, whose values indicate the success of a function call.

The following table shows the _ERROR_ values and their meaning.

Summary of _ERROR_ Values
Value of _ERROR_	Possible Corresponding Status Codes	Description
0	CA-IDMS 0000	Function call executed successfully.
1	All CA-IDMS status codes except 0000	CA-IDMS error code returned. Contents of the input buffer and the program data vector are printed in the SAS log with the next INPUT statement or when control returns to the beginning of the DATA step, whichever comes first.
	SAS status 9999	Program cannot perform function call from options specified.

Obtaining the Value of _ERROR_

Check the SAS log to see the value of _ERROR_. If _ERROR_=1, it is printed in the SAS log along with the contents of the input buffer and the program data vector.

Obtaining the CA-IDMS Error Codes

You can obtain the status code returned by CA-IDMS by specifying a variable name with the ERRSTAT= option of the CA-IDMS INFILE statement. This variable will be assigned the CA-IDMS status after each function call to the database.

Refer to your CA-IDMS documentation for explanations of CA-IDMS error status codes.

Checking for Non-Error Conditions and Resetting _ERROR_

Some of the CA-IDMS status codes that set _ERROR_ to 1 might not represent errors in your SAS program. When this happens in your application, you should check the actual error status code returned by CA-IDMS as well as the value of _ERROR_ by the methods stated in the above sections, and possibly reset _ERROR_ to 0.

For example, suppose you are writing a program that accesses all the DEPARTMENT and EMPLOYEE records from all the DEPT-EMPLOYEE set occurrences. When an end-of-set condition (CA-IDMS status code 0307) occurs on the EMPLOYEE record, _ERROR_ is set to 1; however, you do not consider the end-of-set condition to be an error. Instead, you want your application to obtain the next owner record or DEPARTMENT record from the next DEPT-EMPLOYEE set occurrence.

If a status code sets _ERROR_ but you do not consider the condition to be an error, you should reset _ERROR_ to 0 before executing another INPUT statement or returning to the beginning of the DATA step. Otherwise, the contents of the input buffer and program data vector are printed on the SAS log. See in Example: Traversing a Set for an example of how to reset _ERROR_ to 0.

Catching Errors Before Moving Data

In all programs it is important to check the values of either the _ERROR_ or ERRSTAT= variables before moving data from the input buffer into the program data vector. For example, if a GET or OBTAIN function call fails to retrieve the expected record, the input buffer might still contain data from a previous GET or OBTAIN call or be filled with missing values. You might not want to move these values to SAS variables. By checking either the ERRSTAT= or _ERROR_ variable, you can determine whether the function call was successful and decide whether to move the input buffer data to SAS variables.

When you need to issue a retrieval call but you want to check either _ERROR_ or ERRSTAT= values before moving data to SAS variables, use a CA-IDMS INPUT statement with no variables specified, but with a trailing @, to issue the call:

input @;

Because no variables are specified, no data is moved to the program data vector. The statement contains a trailing @, so the record remains in the input buffer, and your application can check the values in one of both of _ERROR_ and ERRSTAT= before determining what action to take. For more information, see Holding Records in the Input Buffer.

For example, suppose you have specified ERRSTAT=ERR and FUNC=FUNC1 on your INFILE statement, and you have assigned FUNC1= 'GET' or 'OBTAIN'. You can use the following code to check the error status before moving data:

1  input @;
2  if (err ne '0000' and err ne '0307') then
         go to staterr;
3  if err eq '0307' then do;
4     _error_ = 0;  
      /* No more DEPT records so STOP */
      stop;
   end;
5  input @1   department_id      4.0
         @5   department_name    $char45.
         @50  department_head    4.0;

	The INPUT statement retrieves a record from the database and places a hold on the input buffer but does not move data to the program data vector.
	A SAS IF statement checks to see if ERR is not equal to 0000 or 0307. If not, the program branches to the STATERR routine, which issues an error message and stops the DATA step.
	If the INPUT statement encountered the end-of-set, then the _ERROR_ variable is reset to 0 () to prevent the contents of the input buffer and program data vector from being printed on the SAS log, and the DATA step stops.
	If the first INPUT statement () was successful, then the second INPUT statement moves the data from the record being held in the input buffer to the program data vector and releases the hold.

Handling End of File

Because of the nature and design of a network database, the concept of an end of file does not exist. Consequently, the SAS option EOF= should not be used on a CA-IDMS INFILE statement. Instead you should either write your DATA step code to stop processing when you have retrieved all the records you need or set up your code to loop, stopping only when it reaches a desired condition.

Top of Page