Examples of SAS/ACCESS DATA Step Programs

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;

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

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

Using a Data Set to Locate 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

Top of Page