space
space
Previous Page | Next Page

Examples of SAS/ACCESS DATA Step Programs

Reestablishing Currency on a Record

This example illustrates how a program can reestablish 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 SAS/ACCESS DATA Step 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 SAS/ACCESS DATA Step Examples for a description of the INFILE statement.

[4] See Statements Common to All SAS/ACCESS DATA Step 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 enables 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 SAS/ACCESS DATA Step Examples for a description of the STATERR statements.

The following output shows a portion of the output from this program.

Reestablishing 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

space
space
Previous Page | Next Page | Top of Page

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