SAS/ACCESS Interface to CA-IDMS Software: Reference

Using Information in a SAS Data Set to Locate Records

This example, like the previous example, uses the information stored in a SAS data set to locate records in the CA-IDMS database. In this case, not only do the observations in the transaction data set WORK.OFFICE provide CALC information for the OFFICE record, they supply sort key information as well for the EMPLOYEE record. Therefore, the program uses both pieces of information to locate a specific occurrence of the OFFICE record, followed by a specific occurrence of the EMPLOYEE record in the OFFICE-EMPLOYEE set occurrence. If any of the transaction information is incorrect, a WARNING message is issued and no observation is output to WORK.EMP. The numbers in the program correspond to the numbered comments following the program.

[1] *options $idmdbug;

[2] data work.office;
   input offkey $3. emp $25.;
   datalines;
   001GARFIELD       JENNIFER
   002BLOOMER        JUNE
   005JOE            SMITH
   008WAGNER         RICHARD
   010ANDALE         ROY
   ;
   data work.emp;
      drop offkey emp;

[3]   infile empss01 idms func=func record=recname
            ikey=ckey ikeylen=keyl errstat=stat 
            sequence=seq set=inset sortfld=skey;


      /* BIND the records to be accessed */

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

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


   /* OBTAIN OFFICE record based on CALC key */

[5]   set work.office;
     func       = 'OBTAIN';
     ckey       = offkey;
     keyl       = 3;
     recname    = 'OFFICE';
     inset      = ' ';
     skey       = ' ';
     input @;
     if stat not in ('0000', '0326') then go to
         staterr;
     if stat = '0000' then do;
        input @1   office_code       $char3.
              @4   office_street     $char20.
              @24  office_city       $char15.
              @39  office_state      $char2.
              @41  office_zip        $char9.
              @50  officephone1     9.0
              @59  officephone2     9.0
              @68  officephone3     9.0
              @77  areacode         $char3.
              @80  speeddial        $char3.;


      /* FIND EMPLOYEE record within set  */
      /* using SORT key                   */

[6]      func       = 'FIND';
        skey       = emp;
        ckey       = '   ';
        keyl       = 25;
        recname    = 'EMPLOYEE';
        inset      = 'OFFICE-EMPLOYEE ';
        input;
        if stat not in ('0000', '0326') then 
            go to staterr;
        if stat = '0000' then do;


           /* OBTAIN CURRENT record */

[7]         func       = 'OBTAIN';
           seq        = 'CURRENT';
           skey       = '                         ';
           keyl       = 0;
           inset      = '                ';
           input @;
           if stat ne '0000' then go to staterr;
           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      yymmdd6.
                 @103 termdate       6.0
                 @109 birthdate      yymmdd6.;
           output;
        end;

[8]      else do;
           put 'WARNING: No EMPLOYEE record for
                  SORT key= ' emp '.';
           put 'WARNING: Execution continues with
                  next OFFICE CALC.';
           put;
           _error_ = 0;
        end;
     end;
     else do;
        put 'WARNING: No OFFICE record for CALC 
               key= 'offkey '.';
        put 'WARNING: Execution continues with
               next OFFICE CALC.';
        put;
        _error_ = 0;
      end;
   return;

[9] 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= skey=;
      put @1 'ERROR: DATA step execution 
                terminating.';
      _error_ = 0;
      stop;
   run;

   proc print data=work.emp;
     format startdate birthdate date9.;
     title1 'Office and Employee Information';
     title2 'as Specified in Transaction Data Set';
   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.OFFICE. The 3-byte character variable OFFKEY contains CALC key values that will be used to access OFFICE records directly by office code. The 25-byte character variable EMP contains SORT key values that will be used to access EMPLOYEE records directly using the EMP-NAME-NDX.

[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] An observation is read from WORK.OFFICE, and the current OFFKEY value is used as a CALC value to obtain the OFFICE record. The length of the CALC key is specified by the IKEYLEN= variable KEYL. The INPUT @; statement submits the call and places a hold on the input buffer so that the status code can be checked. Any unexpected status code branches execution to the STATERR label. A status code of 0000 directs execution to the INPUT statement, which maps data from the held input buffer to the PDV, then releases the buffer.

[6] The program must now locate a specific occurrence of EMPLOYEE within the current OFFICE-EMPLOYEE set. A FIND EMPLOYEE WITHIN OFFICE-EMPLOYEE call is generated using the sort key information in the EMP variable read from WORK.OFFICE. The sort key length is set to 25. (The previous length of 3 applied to the OFFICE CALC key.) The null INPUT statement submits the call but does not place a hold on the buffer. FIND does not return any data. For any unexpected status code, execution branches to the STATERR label. If the FIND is successful, execution continues with the next DML call.

[7] Having successfully located the EMPLOYEE using the supplied index value, an OBTAIN CURRENT call is generated so that EMPLOYEE record information can be accessed by the program. SKEY is set to blank and KEYL is set to 0 so that their values are not used for the OBTAIN call. The INPUT @; statement submits the generated call and places a hold on the input buffer so that the status code can be checked. Any status code not equal to 0000 routes execution to the STATERR label. For a successful OBTAIN, the INPUT statement maps EMPLOYEE record data from the input buffer to the specified variables in the PDV and releases the input buffer. At this point, the OUTPUT statement writes an observation to the output data set. Only observations that contain both office and employee information are output.

[8] This group of statements allows execution to continue when either no EMPLOYEE record exists for the specified sort key value or no OFFICE record exists for the specified CALC value from WORK.OFFICE. In both cases, informative WARNING messages are 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.

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

Locating Records shows a portion of the output from this program.

Locating Records

Office and Employee Information as Specified in Transaction Data Set office_ office_ office_ Obs code office_street office_city state zip officephone1 1 001 20 W BLOOMFIELD ST SPRINGFIELD MA 02076 369772100 2 002 567 BOYLSTON ST BOSTON MA 02243 956237795 3 008 910 E NORTHSOUTH AVE WESTON MA 02371 367919136 employee_ Obs officephone2 officephone3 areacode speeddial id firstname 1 0 0 3 JENNIFER 2 625719562 398000000 69 JUNE 3 792923671 327000000 458 RICHARD Obs lastname street city state zip phone status 1 GARFIELD 110A FIRTH ST STONEHAM MA 02928 6173321967 01 2 BLOOMER 14 ZITHER TERR LEXINGTON MA 01675 6175555544 01 3 WAGNER 677 GERMANY LN NATICK MA 02178 6174321109 01 Obs ssnumber startdate termdate birthdate 1 021994516 21JAN1977 0 18AUG1945 2 039557818 05MAY1980 0 25APR1960 3 011776663 07JUN1978 0 04MAR1934

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.OFFICE. The 3-byte character variable OFFKEY contains CALC key values that will be used to access OFFICE records directly by office code. The 25-byte character variable EMP contains SORT key values that will be used to access EMPLOYEE records directly using the EMP-NAME-NDX.
	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.
	An observation is read from WORK.OFFICE, and the current OFFKEY value is used as a CALC value to obtain the OFFICE record. The length of the CALC key is specified by the IKEYLEN= variable KEYL. The `INPUT @;` statement submits the call and places a hold on the input buffer so that the status code can be checked. Any unexpected status code branches execution to the STATERR label. A status code of 0000 directs execution to the INPUT statement, which maps data from the held input buffer to the PDV, then releases the buffer.
	The program must now locate a specific occurrence of EMPLOYEE within the current OFFICE-EMPLOYEE set. A FIND EMPLOYEE WITHIN OFFICE-EMPLOYEE call is generated using the sort key information in the EMP variable read from WORK.OFFICE. The sort key length is set to 25. (The previous length of 3 applied to the OFFICE CALC key.) The null INPUT statement submits the call but does not place a hold on the buffer. FIND does not return any data. For any unexpected status code, execution branches to the STATERR label. If the FIND is successful, execution continues with the next DML call.
	Having successfully located the EMPLOYEE using the supplied index value, an OBTAIN CURRENT call is generated so that EMPLOYEE record information can be accessed by the program. SKEY is set to blank and KEYL is set to 0 so that their values are not used for the OBTAIN call. The `INPUT @;` statement submits the generated call and places a hold on the input buffer so that the status code can be checked. Any status code not equal to 0000 routes execution to the STATERR label. For a successful OBTAIN, the INPUT statement maps EMPLOYEE record data from the input buffer to the specified variables in the PDV and releases the input buffer. At this point, the OUTPUT statement writes an observation to the output data set. Only observations that contain both office and employee information are output.
	This group of statements allows execution to continue when either no EMPLOYEE record exists for the specified sort key value or no OFFICE record exists for the specified CALC value from WORK.OFFICE. In both cases, informative WARNING messages are 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.
	See Statements Common to All Examples for a description of the STATERR statements.