SAS/ACCESS Interface to CA-IDMS Software: Reference

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;

[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.EMP. The 4-byte character variable ID contains CALC key values that will be used to access EMPLOYEE records directly by employee ID.

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

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

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

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

[9] 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 are moved into the PDV from the input buffer, and another observation is output for the current employee.

[10] This group of statements allows 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.

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

Using a SAS Data Set as a Transaction File shows a portion of the output from this program.

Using a SAS Data Set as a Transaction File

Positions Held by Specifed 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

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.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 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.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 are moved into the PDV from the input buffer, and another observation is output for the current employee.
[10]	This group of statements allows 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.
[11]	See Statements Common to All Examples for a description of the STATERR statements.