SAS/ACCESS Interface to CA-IDMS Software: Reference

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 prior to 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;

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

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

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

[4] 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 are returned to the input buffer. A SAS IF statement checks the status code returned by the FIND call.

[5] 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.

[6] 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 are moved from the input buffer to the PDV by executing the INPUT statement. The first INPUT @; statement forces the call to be submitted and allows a returned status code to be checked prior to 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.

[7] 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.

[8] 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.

[9] 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.

[10] 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.

[11] 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.

[12] If the status code indicates end-of-set (0307) then the status variable is reset to 0000. The assignment is done to allow the DO UNTIL loop (see [6] ) 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.

[13] 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.

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

Navigating Multiple Set Relationships shows a portion of the output from this program.

Navigating Multiple Set Relationships

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

Chapter Contents
Previous
Next
Top of Page

	See Statements Common to All Examples for a description of the OPTIONS statement.
	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 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 are 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 are moved from the input buffer to the PDV by executing the INPUT statement. The first `INPUT @;` statement forces the call to be submitted and allows a returned status code to be checked prior to 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.
[10]	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.
[11]	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.
[12]	If the status code indicates end-of-set (0307) then the status variable is reset to 0000. The assignment is done to allow 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.
[13]	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.
[14]	See Statements Common to All Examples for a description of the STATERR statements.