Examples of SAS/ACCESS DATA Step Programs

Using a SAS Data Set as a Transaction File

This example illustrates how to use an input SAS data set as a transaction file to supply parameter values for direct access DML calls. These calls obtain CA-IDMS records using CALC key values. The transaction data set WORK.EMP supplies CALC key values for EMPLOYEE records. The program then accesses EMPOSITION records in the EMP-EMPOSITION set to create an output SAS data set that contains all of the position information for the employees named in WORK.EMP. The DATA step terminates after all observations from WORK.EMP have been read. The numbers in the program correspond to the numbered comments following the program.

1  *options $idmdbug;

2  data work.emp;
   input id $4.;

   datalines;
   0471
   0301
   0004
   0091
   1002
   ;
   data work.emp_empos;
     drop id chkrec nxtrec;
     length chkrec $ 29;

3    infile empss01 idms func=func record=recname
           ikeylen=keyl errstat=stat sequence=seq 
           set=inset ikey=ckey dbkey=dkey;

     /* 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    = 'EMPOSITION';
        input;
        if stat ne '0000' then go to staterr;
     end;


     /* OBTAIN EMPLOYEE records using CALC key  */
     /* from EMP data set */

5    set work.emp;
     func       = 'OBTAIN';
     ckey       = id;
     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.
             @30  street          $char20.
             @50  city            $char15.
             @65  state           $char2.
             @67  zip             $char9.
             @76  phone           10.0
             @86  status          $char2.
             @88  ssnumber        $char9.
             @97  emp_start       yymmdd6.
             @103 emp_term        6.0
             @109 birthdate       yymmdd6.;


       /* OBTAIN LAST EMPOSITION record in */
       /* EMP-EMPOSITION set               */

6      func       = 'OBTAIN';
       seq        = 'LAST';
       ckey       = '    ';
       keyl       = 0;
       dkey      = ' ';
       recname    = 'EMPOSITION';
       inset      = 'EMP-EMPOSITION';
       input @;
       if stat not in ('0000', '0326') then go to 
           staterr;
       if stat = '0000' then do;
         chkrec = put(employee_id,z4.) ||firstname ||
            lastname;


      /* Process all EMPOSITION records for */
      /* current EMPLOYEE                   */

7       do until (nxtrec = chkrec);
           input @1   pos_start    yymmdd6.
                 @7   pos_finish   6.0
                 @13  salarygrade  2.0
                 @15  salary       pd5.2
                 @20  bonus        pd2.0
                 @22  commission   pd2.0
                 @24  overtime     pd2.0;
           output;


       /* ACCEPT CURRENCY for PRIOR record in  */
       /* EMP-EMPOSITION set                   */

8        func       = 'ACCEPT';
         dkey       = '    ';
         seq        = 'PRIOR  ';
         recname    = '          ';
         inset      = 'EMP-EMPOSITION';
         input;
         if stat eq '0000' then do;


    /* OBTAIN current record using the DBKEY */

9          func       = 'OBTAIN';
           seq        = '       ';
           inset      = '        ';
           input @1 nxtrec $29. @;
           if stat ne '0000' then go to staterr;
           end;
         end;
       end;

10      else do;
           put 'WARNING: No EMPOSITION record for 
                  EMPID= ' id;
           put 'WARNING: Execution continues with 
                  next EMPID.';
           _error_ = 0;
       end;
     end;
     else do;
          put 'WARNING: No EMPLOYEE record for EMPID= '
                  id;
          put 'WARNING: Execution continues with next
                  EMPID.';
          _error_ = 0;
     end;
   return;


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

   proc print data=work.emp_empos;
      format emp_start birthdate pos_start
          date9. salary dollar12.2
      title1 'Positions Held by Specified 
          Employees';
      title2 'Listed in Ascending Order by
          Initdate/Termdate';
   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.EMP. The 4-byte character variable ID contains CALC key values that will be used to access EMPLOYEE records directly by employee ID.
	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.EMP, and the current ID value is used as a CALC key for obtaining the EMPLOYEE. The length of the CALC key is specified with 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. For any unexpected status code, execution branches 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 and then releases the buffer.
	The program now attempts to obtain EMPOSITION records in the order of oldest (LAST) to most current (FIRST). First, an OBTAIN LAST call is issued for the EMPOSITION record in set EMP-EMPOSITION. The INPUT @; statement submits the call and holds the buffer so the status code can be checked. Execution branches to the STATERR label for any unexpected status code. For status code 0000, a variable called CHKREC is assigned a value that is composed of the current employee's CALC key, first name, and last name. CHKREC is used in the condition of the DO UNTIL loop described in the next step.
	The DO UNTIL loop navigates the EMP-EMPOSITION set occurrences in reverse order. The condition on a DO UNTIL loop is evaluated at the bottom of the loop after the statements in the loop have been executed (see ). The input buffer already contains an EMPOSITION record. The INPUT statement maps EMPOSITION data from the held buffer into the variables in the PDV. At this point, a complete observation exists and is output to the WORK.EMP_EMPOS data set. No observation is written when no EMPOSITION records exist for a specified employee.
	To move in reverse order, the ACCEPT PRIOR call is generated and issued within the EMP-EMPOSITION set to return the database key of the prior record in the current set occurrence. The database key is stored in the variable defined by the DBKEY= parameter on the INFILE statement, DKEY. The null INPUT statement submits the call. For any status code not equal to 0000, execution branches to the STATERR label.
	For a successful ACCEPT call, an OBTAIN is issued using the database key stored in DKEY. Using this method to navigate the set implies that no end-of-set status code is set. To determine whether an end-of-set condition exists, the INPUT statement submits the OBTAIN, moves the first 29 bytes of data into a character variable called NXTREC and places a hold on the buffer contents. For a successful OBTAIN, execution resumes with the evaluation of the DO UNTIL condition. If CHKREC equals NXTREC, then the program is current on the EMPLOYEE (owner of the set) so the loop terminates. If the variables are not equal, then the record in the buffer is an EMPOSITION record, so data is moved into the PDV from the input buffer, and another observation is output for the current employee.
	This group of statements enables execution to continue when either no EMPOSITION records exist for the specified employee or no EMPLOYEE record exists for the CALC value specified in the transaction data set. 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.

                           Positions Held by Specified Employees                   
                      Listed in Ascending Order by Initdate/Termdate              
                                                                                  
           employee_                                                               
       Obs     id     firstname  lastname         street        city       state   
                                                                                   
        1     471     THEMIS     PAPAZEUS    234 TRANSWORLD ST  NORTHBORO   MA     
        2     471     THEMIS     PAPAZEUS    234 TRANSWORLD ST  NORTHBORO   MA     
        3     301     BURT       LANCHESTER  45 PINKERTON AVE   WALTHAM     MA     
        4     301     BURT       LANCHESTER  45 PINKERTON AVE   WALTHAM     MA     
        5     301     BURT       LANCHESTER  45 PINKERTON AVE   WALTHAM     MA     
        6       4     HERBERT    CRANE       30 HERON AVE       KINGSTON    NJ     
        7       4     HERBERT    CRANE       30 HERON AVE       KINGSTON    NJ     
        8       4     HERBERT    CRANE       30 HERON AVE       KINGSTON    NJ     
        9      91     MADELINE   ORGRATZI    67 RAINBOW DR      KENDON      MA                                                                            
                                                                                   
       Obs  zip     phone   status ssnumber  emp_start emp_term birthdate pos_start
                                                                                   
        1  03256 6174561277   01   022887770 07SEP1978     0    04MAR1935 07SEP1978
        2  03256 6174561277   01   022887770 07SEP1978     0    04MAR1935 01JAN1982
        3  01476 6175341109   01   129040506 03FEB1975     0    19APR1932 03FEB1975
        4  01476 6175341109   01   129040506 03FEB1975     0    19APR1932 03FEB1977
        5  01476 6175341109   01   129040506 03FEB1975     0    19APR1932 03FEB1980
        6  21341 2013341433   01   016777451 14MAY1977     0    21MAR1942 14MAY1977
        7  21341 2013341433   01   016777451 14MAY1977     0    21MAR1942 15NOV1979
        8  21341 2013341433   01   016777451 14MAY1977     0    21MAR1942 14MAY1982
        9  06182 6174311919   01   231067878 10OCT1980     0    16OCT1951 10OCT1980
                                                                                  
            pos_                                                                   
       Obs finish   salarygrade         salary   bonus   commission   overtime     
                                                                                   
        1  811231        72         $90,000.00     10         0           0        
        2       0        82        $100,000.00     10         0           0        
        3  770202        52         $39,000.00      7         0           0        
        4  800202        52         $45,000.00      7         0           0        
        5       0        53         $54,500.00      7         0           0        
        6  791114        71         $60,000.00     10         0           0        
        7  820513        71         $70,000.00     10         0           0        
        8       0        71         $75,000.00     10         0           0   
        9       0        43         $39,000.00      7         0           0

Top of Page