Examples of SAS/ACCESS DATA Step Programs

Navigating Multiple Set Relationships

This example shows how to navigate multiple set relationships and use direct access methods involving database record keys. The output consists of observations containing related employee, office, and dental claim information. Observations are only output for employees that have dental claim record occurrences. To gather the information, the program performs an area sweep for the DEPARTMENT records and uses the FIND command to establish currency and navigate the DEPT-EMPLOYEE, OFFICE-EMPLOYEE, EMP-COVERAGE, and COVERAGE-CLAIMS sets. By accepting and storing database keys, currency can be re-established on the EMPLOYEE record after obtaining OFFICE information and before gathering COVERAGE and DENTAL CLAIM information. The numbers in the program correspond to the numbered comments following the program.

1  *options $idmdbug;
   data work.dental_records;
     drop tempkey;

2    infile empss01 idms func=func record=recname 
            dbkey=dkey errstat=stat sequence=seq 
            set=inset area=subarea;


     /* BIND the records to be accessed */

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

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

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

         recname    = 'DENTAL-CLAIM';
         input;
         if stat ne '0000' then go to staterr;

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


     /* FIND FIRST/NEXT DEPARTMENT record in   */
     /* area ORG-DEMO-REGION                   */

4    seq        = 'NEXT';
     if _n_ = 1 then seq = 'FIRST';
     func       = 'FIND';
     recname    = 'DEPARTMENT';
     subarea    = 'ORG-DEMO-REGION';
     inset      = ' ';
     input;
     if stat not in ('0000', '0307') then go to 
         staterr;


     /* STOP DATA step execution if no more */
     /* DEPARTMENT records                  */

5    if stat = '0307' then do;
         _error_ = 0;
         stop;
     end;


6    do until (stat ne '0000');

        /* OBTAIN NEXT EMPLOYEE record */

         func       = 'OBTAIN';
         seq        = 'NEXT';
         recname    = 'EMPLOYEE';
         inset      = 'DEPT-EMPLOYEE';
         input @;
         if stat not in ('0000','0307') then go to 
             staterr;
         if stat = '0000' then do;
           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.
                 @109 birthdate      yymmdd6.;


        /* ACCEPT DBKEY for current EMPLOYEE and */
        /* store in tempkey                      */

7          func       = 'ACCEPT';
           seq        = 'CURRENT';
           dkey       = '    ';
           inset      = '             ';
           input;
           if stat ne '0000' then go to staterr;
           tempkey=dkey;


         /* OBTAIN OFFICE record for current  */
         /* EMPLOYEE                          */

8          func       = 'OBTAIN';
           seq        = 'OWNER';
           dkey       = '    ';
           inset      = 'OFFICE-EMPLOYEE';
           input @;
           if stat ne '0000' then go to staterr;
           input @1   office_code    $char3.
                 @4   office_street  $char20.
                 @24  office_city    $char15.
                 @39  office_state   $char2.
                 @41  office_zip     $char9.;


         /* FIND EMPLOYEE using DBKEY stored in */
         /* tempkey                             */

9          func       = 'FIND';
           recname    = '            ';
           dkey       = tempkey;
           seq        = '       ';
           inset      = '               ';
           input;
           if stat ne '0000' then go to staterr;


         /* FIND FIRST COVERAGE record for  */
         /* current EMPLOYEE                */

10          func       = 'FIND';
           recname    = 'COVERAGE';
           dkey       = '    ';
           seq        = 'FIRST';
           inset      = 'EMP-COVERAGE';
           input;
           if stat ne '0000' then go to staterr;


         /* OBTAIN LAST DENTAL-CLAIM record      */
         /* within COVERAGE-CLAIMS               */
         /* Observations are only OUTPUT for     */
         /* employees with dental claim records  */
                    
11          func       = 'OBTAIN';
           recname    = 'DENTAL-CLAIM';
           seq        = 'LAST';
           inset      = 'COVERAGE-CLAIMS';
           input @;
           if stat not in ('0000','0307') then go to 
               staterr;
           do while (stat eq '0000');
              input @1   claim_year      $2.
                    @3   claim_month     $2.
                    @5   claim_day       $2.
                    @7   claim_firstname $10.
                    @17  claim_lastname  $15.
                    @32  birthyear       $2.
                    @34  birthmonth      $2.
                    @36  birthday        $2.
                    @38  sex             $1.
                    @39  relation        $10.
                    @49  dds_firstname   $10.
                    @59  dds_lastname    $15.
                    @74  ddsstreet       $20.
                    @94  ddscity         $15.
                    @109 ddsstate        $2.
                    @111 ddszip          $9.
                    @120 license         $6.
                    @126 num_procedure   ib2.
                    @131 tooth_number    $2.
                    @133 service_year    $2.
                    @135 service_month   $2.
                    @137 service_day     $2.
                    @139 procedure_code  $4.
                    @143 descservice     $60.
                    @203 fee             pd5.2;
              output;

            /* OBTAIN PRIOR DENTAL-CLAIM record */

              seq        = 'PRIOR';
              input @;
           end;


           /* When DENTAL-CLAIM records have been */
           /* processed, release INPUT buffer and */
           /* reset STAT to OBTAIN NEXT EMPLOYEE  */

12          if stat = '0307' then do;
              stat = '0000';
              input;
           end;
           else go to staterr;
         end;
       end;

       /* When all EMPLOYEEs have been processed, */
       /* reset ERROR flag and continue with next */
       /* DEPARTMENT                              */


13    _error_ = 0;
   return;


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

   proc print data=work.dental_records;
     format birthdate date9.;
     title1 'Dental Claim Information';
   run;

	See Statements Common to All SAS/ACCESS DATA Step Examples for a description of the OPTIONS statement.
	See Statements Common to All SAS/ACCESS DATA Step Examples for a description of the INFILE statement.
	See Statements Common to All SAS/ACCESS DATA Step Examples for a description of the BIND RECORD statement.
	The first time the DATA step executes, the FIND command locates the FIRST DEPARTMENT record in the area. For subsequent DATA step iterations, initialize the call parameters to find the NEXT DEPARTMENT record in the area. The null INPUT statement generates and submits the call, but no data is returned to the input buffer. A SAS IF statement checks the status code returned by the FIND call.
	As DEPARTMENT records are located, the program checks the status code returned by CA-IDMS. When all records in the area have been accessed, CA-IDMS returns a 0307 status code (end-of-area). The program then issues a STOP statement to terminate the DATA step. Since there is no other end-of-file condition to normally terminate the DATA step, the STOP statement must be issued to avoid a looping condition. Also, non-blank status codes set the automatic DATA step variable _ERROR_ to 1, so _ERROR_ is reset to 0, which prevents the contents of the PDV from being written to the SAS log.
	For the current DEPARTMENT, the program must access all EMPLOYEE records in the DEPT-EMPLOYEE set. The DO UNTIL loop executes until the status code that is returned from CA-IDMS is not equal to 0000. For unexpected status codes, the statements associated with the STATERR label are executed, and the loop terminates when the end-of-set status code (0307) is encountered. An OBTAIN is used to retrieve the EMPLOYEE records. After the status code is verified to be successful, data is moved from the input buffer to the PDV by executing the INPUT statement. The first INPUT @; statement forces the call to be submitted and enables a returned status code to be checked before any attempt to move data from the input buffer to the PDV. This process eliminates any possibility of moving invalid data into the PDV and avoids unnecessary data conversions when the call fails.
	After an EMPLOYEE record has been obtained, the ACCEPT command takes the record's database key and stores it in DKEY, the variable defined by the DBKEY= INFILE parameter. The value is then stored in a variable called TEMPKEY because the DKEY variable must be set to blanks to generate the next call correctly. By saving the record's database key, the program can re-establish currency on the EMPLOYEE record after obtaining OWNER information from the OFFICE record in the OFFICE-EMPLOYEE set.
	OFFICE records are retrieved by issuing an OBTAIN OWNER within the OFFICE-EMPLOYEE set. The INPUT @; statement generates and submits the call. For a successful OBTAIN, OFFICE information is moved from the held input buffer to the PDV.
	The program is now ready to establish currency back to the EMPLOYEE record current in the DEPT-EMPLOYEE set. The database key value stored in TEMPKEY is used to format a FIND DBKEY command. The null INPUT statement submits the call and the status code is checked to be sure it was successful. Any status code other than 0000 routes execution to the STATERR label.
	Now current on EMPLOYEE, a FIND is issued to locate the FIRST COVERAGE record in the EMP-COVERAGE set. For any status code not equal to 0000, execution is routed to the STATERR label.
	The goal is to process all the DENTAL-CLAIM records in the COVERAGE-CLAIMS set for the current COVERAGE record. An OBTAIN LAST is submitted by the INPUT @; statement, and if DENTAL-CLAIM records exist in the set, then the subsequent INPUT statement maps the returned data from the input buffer to the PDV. At this point, a complete observation--one containing EMPLOYEE, OFFICE and DENTAL-CLAIM data--is output to the SAS data set. The sequence variable SEQ is assigned a value of PRIOR so that subsequent iterations of the DO WHILE loop submit an OBTAIN PRIOR call. The DO WHILE continues executing until the OBTAIN PRIOR returns a status code not equal to 0000.
	If the status code indicates end-of-set (0307) then the status variable is reset to 0000. The assignment is done to enable the DO UNTIL loop (see ) to continue executing and issuing OBTAIN calls for employees in the current department. The null INPUT statement is issued to release the buffer held by the INPUT @; statement within the DO WHILE loop. In this example, because there was a held buffer, the null INPUT statement does not attempt to generate and submit a DML call. The buffer must be released so the next DML call, the OBTAIN NEXT EMPLOYEE WITHIN DEPT-EMPLOYEE, can be generated. For any other status code, execution branches to the STATERR label.
	At this point, the STAT variable must have a value of 0307. 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.
	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.

                            Dental Claim Information                         
                                                                               
        employee_                                                             
   Obs     id     firstname  lastname     street       city     state   zip  
                                                                             
    1       4     HERBERT     CRANE    30 HERON AVE  KINGSTON    NJ    21341 
    2      30     HENRIETTA   HENDON   16 HENDON DR  WELLESLEY   MA    02198 
                                                                             
                                                  office_                    
   Obs    phone    status  ssnumber   birthdate   code      office_street    
                                                                             
    1  2013341433    01    016777451    420321     001    20 W BLOOMFIELD ST 
    2  6178881212    01    011334444    331006     002    567 BOYLSTON ST    
                                                                             
                   office_ office_ claim_ claim_ claim_  claim_    claim_    
   Obs office_city  state    zip    year  month   day   firstname lastname   
                                                                             
    1  SPRINGFIELD   MA     02076    80     10     04    JESSICA   CRANE     
    2  BOSTON        MA     02243    77     05     23    HELOISE   HENDON    
                                                                             
                                                         dds_     dds_       
   Obs birthyear  birthmonth  birthday  sex  relation  firstname  lastname   
                                                                             
    1     57          01         11      F   WIFE         DR      PEPPER     
    2     68          03         15      F   DAUGHTER     SAL     SARDONICUS 
                                                                             
                                                             num_   tooth_   
   Obs ddsstreet         ddscity  ddsstate ddszip license procedure number   
                                                                             
    1  78 COLA RD       PRINCETON    NJ    01762  877073      2       08     
    2  402 NATURE'S WAY NEEDHAM      MA    02243  459631      1       14     
                                                                             
       service_    service_    service_    procedure_                        
   Obs   year       month        day          code       descservice    fee  
                                                                             
    1     80          09          16          0076         FILLING       14  
    2     77          05          02          0076         FILLING       14

Top of Page