SAS/ACCESS Interface to CA-IDMS Software: Reference

Re-establishing Currency on a Record

This example illustrates how a program can re-establish currency on a record to complete set navigation after accessing a record that is not contained in the current set occurrence.

In this example, a transaction SAS data set, WORK.EMPLOYEE, supplies a CALC key value for the OBTAIN of an EMPLOYEE record. COVERAGE records are then obtained within the current EMP-COVERAGE set occurrence. PLANCODE values from employee COVERAGE records provide links to INSURANCE-PLAN records through a CALC key. Once current on INSURANCE-PLAN, the program gathers data and uses a stored database key to return to the current COVERAGE record. At that point, the next COVERAGE record in the current set occurrence of EMP-COVERAGE can be obtained. The output data set consists of observations which contain employee, coverage, and related insurance plan data. The numbers in the program correspond to the numbered comments following the program.

[1] *options $idmdbug;

[2] data work.employee;
      input empnum $4.;
   datalines;
   0007
   0471
   0000
   0301
   0004
   ;

   data work.empplan;
      drop covdbkey empnum;

[3]   infile empss01 idms func=func record=recname 
            ikey=ckey ikeylen=keyl errstat=stat 
            sequence=seq set=inset area=subarea 
            dbkey=dkey;


     /* BIND records to be accessed */

[4]   if _n_ = 1 then do;
        func       = 'BIND';
        recname    = 'EMPLOYEE';
        input;
        if stat ne '0000' then go to staterr;

        recname    = 'INSURANCE-PLAN';
        input;
        if stat ne '0000' then go to staterr;

        recname    = 'COVERAGE ;
        input;
        if stat ne '0000' then go to staterr;
     end;


     /* OBTAIN EMPLOYEE record using CALC key */
     /* value */

[5]   set work.employee;
     func       = 'OBTAIN';
     seq        = ' ';
     inset      = ' ';
     ckey       = empnum;
     keyl       = 4;
     recname    = 'EMPLOYEE';
     input @;
     if stat not in ('0000', '0326') then go to 
          staterr;
     if stat = '0000' then do;
        input @1   employee_id    4.0
              @5   firstname      $char10.
              @15  lastname       $char15.;


        /* OBTAIN COVERAGE records for EMPLOYEE */

[6]      seq        = 'FIRST';
        do while (stat = '0000');
          func       = 'OBTAIN';
          keyl       = 0;
          ckey       = ' ';
          dkey       = ' ';
          recname    = 'COVERAGE';
          inset      = 'EMP-COVERAGE';
          input @;
          if stat not in ('0000', '0307') then go 
               to staterr;
          if stat = '0000' then do;
             input @13  type     $1.
                   @14  plancode $3.;


             /* ACCEPT CURRENT database key */

[7]           func       = 'ACCEPT';
             seq        = 'CURRENT';
             dkey       = ' ';
             input;
             if stat ne '0000' then go to staterr;
             covdbkey   = dkey;


             /* FIND INSURANCE-PLAN using CALC */

[8]           func       = 'FIND';
             ckey       = plancode;
             keyl       = 3;
             seq        = '       ';
             recname    = 'INSURANCE-PLAN';
             inset      = ' ';
             dkey       = ' ';
             input;
             if stat ne '0000' then go to 
                 staterr;


             /* OBTAIN CURRENT INSURANCE-PLAN */
             /* record                        */

[9]           func       = 'OBTAIN';
             seq        = 'CURRENT';
             ckey       = ' ';
             keyl       = 0;
             recname    = ' ';
             subarea    = ' ';
             input @;
             if stat ne '0000' then go to staterr;
             input @4   company_name  $45.
                   @105 group_number  6.0
                   @111 plndeduc      PD5.2
                   @116 maxlfcst      PD5.2
                   @121 famlycst      PD5.2
                   @126 depcost       PD5.2;
             output;


             /* FIND COVERAGE using stored  */
             /* database key                */

[10]           func       = 'FIND';
             seq        = ' ';
             recname    = 'COVERAGE';
             dkey       = covdbkey;
             input;
             if stat ne '0000' then go to staterr;
             seq = 'NEXT';
          end;
        end;
     end;


[11]   else do;
        put 'WARNING: No EMPLOYEE record for CALC= 
               'ckey;
        put 'WARNING: Execution continues with next
                EMPLOYEE.';
        _error_ = 0;
     end;

[12]   _error_ = 0;
   return;

[13] staterr:
        put @1 'ERROR: ' @10 func @17 'RETURNED 
                  STATUS =' @37 stat;
        put @1 'ERROR: INFILE parameter values are: ';
        put @1 'ERROR: ' recname= ckey= keyl= seq= 
                  inset= subarea= dkey=;
        put @1 'ERROR: DATA step execution 
                  terminating.';
        _error_ = 0;
        stop;
   run;

   proc print data=work.empplan;
      title 'Employee Coverage and Plan Record 
               Information';
   run;

[1] See Statements Common to All Examples for a description of the OPTIONS statement.

[2] This DATA step execution creates the transaction data set WORK.EMPLOYEE. The 4-byte character variable EMPNUM contains CALC key values that will be used to access EMPLOYEE records directly by employee id.

[3] See Statements Common to All Examples for a description of the INFILE statement.

[4] See Statements Common to All Examples for a description of the BIND RECORD statement.

[5] The current EMPNUM value from WORK.EMPLOYEE is used as a CALC key to obtain an EMPLOYEE record from the database. KEYL specifies the length of the CALC key. The INPUT @; statement submits the call and places a hold on the input buffer so that the status code can be checked. For any unexpected status code, execution branches to the STATERR label. If the status code is 0000, the INPUT statement maps data from the input buffer to the PDV and then releases the buffer.

[6] The DO WHILE loop obtains COVERAGE records for the current employee in the EMP-COVERAGE set. When all COVERAGE records in the set have been obtained, the status code is set to 0307, and the loop terminates. At that point, the DATA step obtains the next EMPLOYEE as specified by the CALC value read from WORK.EMPLOYEE. The INPUT @; statement submits the OBTAIN FIRST/NEXT call and places a hold on the input buffer while the status code is checked. For any unexpected status codes, execution branches to the STATERR label. For a successful OBTAIN call, the INPUT statement maps coverage information from the input buffer to the specified variables in the PDV and releases the input buffer. The PLANCODE variable now contains a CALC key value that can be used to directly access related INSURANCE-PLAN record information.

[7] The next DML call generated is an ACCEPT CURRENT, which takes the current database key of the COVERAGE record and stores it in the variable defined by the DBKEY= INFILE parameter, DKEY. The null INPUT statement submits the ACCEPT call but does not place a hold on the input buffer because ACCEPT returns no data. For any status code other than 0000, execution branches to the STATERR label. For a successful ACCEPT call, the value returned to DKEY is moved into variable COVDBKEY to be used in a later call. By storing the database key of this record for later use, the program can regain currency on the record.

[8] Now that the database key of the COVERAGE record is stored, a FIND call is generated to locate and establish currency on the related INSURANCE-PLAN record. The FIND call uses the CALC value stored in PLANCODE. To issue this call, the DKEY field is set to blank. The null INPUT statement submits the call to CA-IDMS but no hold is placed on the input buffer because FIND does not return data. For any status code other than 0000, execution branches to the STATERR label.

[9] After the INSURANCE-PLAN record has been successfully located, an OBTAIN CURRENT call is generated to request that the record be retrieved. The INPUT @; statement submits the generated call and places a hold on the input buffer so that the returned status code can be checked. For any status code other than 0000, execution branches to the STATERR label. For a successful OBTAIN, the INPUT statement maps INSURANCE-PLAN data from the input buffer to the specified variables in the PDV. At this point, an observation is written to output data set WORK.EMPPLAN that contains related EMPLOYEE, COVERAGE, and INSURANCE-PLAN information.

[10] Currency must be re-established on the COVERAGE record so that the DO WHILE loop can obtain the NEXT COVERAGE record in the current set occurrence of EMP-COVERAGE. A FIND call is generated using the stored database key in COVDBKEY. This call locates the correct COVERAGE record occurrence. The null INPUT statement submits the generated call, but no hold is placed on the input buffer since FIND establishes a position in the database rather than returning data. For any status code other than 0000, execution branches to the STATERR label. If the FIND is successful, currency has been re-established, and SEQ is assigned a value of NEXT to generate OBTAIN NEXT COVERAGE.

[11] This group of statements allows execution to continue when no EMPLOYEE record exists for the CALC value specified in the transaction data set. In this case, an informative WARNING message is written to the SAS log and _ERROR_ is reset to 0, which prevents the contents of the PDV from being written to the SAS log.

[12] At this point, the STAT variable must have a value of 0307, which indicates that all COVERAGE records for the specified EMPLOYEE have been accessed. Since this code is non-zero, _ERROR_ is reset to 0, which prevents the contents of the PDV from being written to the SAS log.

[13] See Statements Common to All Examples for a description of the STATERR statements.

Re-establishing Currency on a Record shows a portion of the output from this program.

Re-establishing Currency on a Record

Employee Coverage and Plan Record Information employee_ Obs id firstname lastname type plancode 1 7 MONTE BANK F 004 2 471 THEMIS PAPAZEUS F 003 3 471 THEMIS PAPAZEUS F 002 4 471 THEMIS PAPAZEUS M 001 5 301 BURT LANCHESTER D 004 6 301 BURT LANCHESTER F 003 7 301 BURT LANCHESTER F 002 8 301 BURT LANCHESTER M 001 9 4 HERBERT CRANE F 004 10 4 HERBERT CRANE F 003 11 4 HERBERT CRANE M 001 group_ Obs company_name number 1 TEETH R US 545598 2 HOLISTIC GROUP HEALTH ASSOCIATION 329471 3 HOMOSTASIS HEALTH MAINTENANCE PROGRAM 952867 4 PROVIDENTIAL LIFE INSURANCE 347815 5 TEETH R US 545598 6 HOLISTIC GROUP HEALTH ASSOCIATION 329471 7 HOMOSTASIS HEALTH MAINTENANCE PROGRAM 952867 8 PROVIDENTIAL LIFE INSURANCE 347815 9 TEETH R US 545598 10 HOLISTIC GROUP HEALTH ASSOCIATION 329471 11 PROVIDENTIAL LIFE INSURANCE 347815 Obs plndeduc maxlfcst famlycst depcost 1 50 0 5000 1000 2 200 0 200 200 3 0 0 900000 100000 4 0 100000 0 0 5 50 0 5000 1000 6 200 0 200 200 7 0 0 900000 100000 8 0 100000 0 0 9 50 0 5000 1000 10 200 0 200 200 11 0 100000 0 0

Chapter Contents
Previous
Next
Top of Page

	See Statements Common to All Examples for a description of the OPTIONS statement.
	This DATA step execution creates the transaction data set WORK.EMPLOYEE. The 4-byte character variable EMPNUM contains CALC key values that will be used to access EMPLOYEE records directly by employee id.
	See Statements Common to All Examples for a description of the INFILE statement.
	See Statements Common to All Examples for a description of the BIND RECORD statement.
	The current EMPNUM value from WORK.EMPLOYEE is used as a CALC key to obtain an EMPLOYEE record from the database. KEYL specifies the length of the CALC key. The `INPUT @;` statement submits the call and places a hold on the input buffer so that the status code can be checked. For any unexpected status code, execution branches to the STATERR label. If the status code is 0000, the INPUT statement maps data from the input buffer to the PDV and then releases the buffer.
	The DO WHILE loop obtains COVERAGE records for the current employee in the EMP-COVERAGE set. When all COVERAGE records in the set have been obtained, the status code is set to 0307, and the loop terminates. At that point, the DATA step obtains the next EMPLOYEE as specified by the CALC value read from WORK.EMPLOYEE. The `INPUT @;` statement submits the OBTAIN FIRST/NEXT call and places a hold on the input buffer while the status code is checked. For any unexpected status codes, execution branches to the STATERR label. For a successful OBTAIN call, the INPUT statement maps coverage information from the input buffer to the specified variables in the PDV and releases the input buffer. The PLANCODE variable now contains a CALC key value that can be used to directly access related INSURANCE-PLAN record information.
	The next DML call generated is an ACCEPT CURRENT, which takes the current database key of the COVERAGE record and stores it in the variable defined by the DBKEY= INFILE parameter, DKEY. The null INPUT statement submits the ACCEPT call but does not place a hold on the input buffer because ACCEPT returns no data. For any status code other than 0000, execution branches to the STATERR label. For a successful ACCEPT call, the value returned to DKEY is moved into variable COVDBKEY to be used in a later call. By storing the database key of this record for later use, the program can regain currency on the record.
	Now that the database key of the COVERAGE record is stored, a FIND call is generated to locate and establish currency on the related INSURANCE-PLAN record. The FIND call uses the CALC value stored in PLANCODE. To issue this call, the DKEY field is set to blank. The null INPUT statement submits the call to CA-IDMS but no hold is placed on the input buffer because FIND does not return data. For any status code other than 0000, execution branches to the STATERR label.
	After the INSURANCE-PLAN record has been successfully located, an OBTAIN CURRENT call is generated to request that the record be retrieved. The `INPUT @;` statement submits the generated call and places a hold on the input buffer so that the returned status code can be checked. For any status code other than 0000, execution branches to the STATERR label. For a successful OBTAIN, the INPUT statement maps INSURANCE-PLAN data from the input buffer to the specified variables in the PDV. At this point, an observation is written to output data set WORK.EMPPLAN that contains related EMPLOYEE, COVERAGE, and INSURANCE-PLAN information.
[10]	Currency must be re-established on the COVERAGE record so that the DO WHILE loop can obtain the NEXT COVERAGE record in the current set occurrence of EMP-COVERAGE. A FIND call is generated using the stored database key in COVDBKEY. This call locates the correct COVERAGE record occurrence. The null INPUT statement submits the generated call, but no hold is placed on the input buffer since FIND establishes a position in the database rather than returning data. For any status code other than 0000, execution branches to the STATERR label. If the FIND is successful, currency has been re-established, and SEQ is assigned a value of NEXT to generate OBTAIN NEXT COVERAGE.
[11]	This group of statements allows execution to continue when no EMPLOYEE record exists for the CALC value specified in the transaction data set. In this case, an informative WARNING message is written to the SAS log and _ERROR_ is reset to 0, which prevents the contents of the PDV from being written to the SAS log.
[12]	At this point, the STAT variable must have a value of 0307, which indicates that all COVERAGE records for the specified EMPLOYEE have been accessed. Since this code is non-zero, _ERROR_ is reset to 0, which prevents the contents of the PDV from being written to the SAS log.
[13]	See Statements Common to All Examples for a description of the STATERR statements.