Examples of SAS/ACCESS DATA Step Programs

Supplying Transaction Information and Navigating Set Occurrences

This example introduces alternate techniques for supplying transaction information and for navigating set occurrences. It also uses program logic to subset records that are accessed to produce output which meets specified criteria. A macro variable supplies the transaction information that produces the subset of employee data. An OBTAIN Nth EMPLOYEE WITHIN DEPT-EMPLOYEE call is used to navigate the current set occurrence.

Using macro variables is one tool for providing transaction information. SAS data set variables have been used in previous examples; another method might make use of an SCL variable. The numbers in the program correspond to the numbered comments following the program.

1  *options $idmdbug;

2  %let hireyear = 1977;

   data work.emp;
      format initdate date9.;
      drop i;

3    infile empss01 idms func=func record=recname 
            area=subarea errstat=stat sequence=seq
            set=inset;

     /* BIND records to be accessed */

4    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;
     end;


   /* FIND FIRST/NEXT DEPARTMENT record in AREA */

5    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                    */

6    if stat = '0307' then do;
        _error_ = 0;
        stop;
     end;


     /* OBTAIN nth EMPLOYEE within 
        DEPT-EMPLOYEE */

7    i=0;
     do until (stat ne '0000');
        i + 1;
        func       = 'OBTAIN';
        seq        = trim(left(put(i,8.)));
        recname    = 'EMPLOYEE';
        inset      = 'DEPT-EMPLOYEE';
        subarea    = '                 ';
        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.
                 @97  initdate       yymmdd6.;


           /* For employees hired in 1977 FIND */
           /* CURRENT DEPARTMENT               */

8          if year(initdate) = &hireyear then do;
              func       = 'FIND';
              seq        = 'CURRENT';
              recname    = 'DEPARTMENT';
              inset      = '                 ';
              input;
              if stat ne '0000' then go to staterr;


            /* OBTAIN CURRENT DEPARTMENT info */
            /* and OUTPUT                     */

9             func       = 'OBTAIN';
              seq        = 'CURRENT';
              recname    = '                 ';
              input @;
              if stat ne '0000' then go to staterr;
              input @1   department_id   4.0
                    @5   department_name $char45.;
              output;
           end;
        end;
     end;
10    _error_ = 0;
   return;

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

   proc print data=work.emp;
      title "Departments that Hired Employees in 
              &hireyear";
   run;

	See Statements Common to All SAS/ACCESS DATA Step Examples for a description of the OPTIONS statement.
	The %LET statement assigns the value 1977 to a newly defined macro variable called HIREYEAR. This macro variable is used to supply subset criteria as part of the condition on the IF statement in step .
	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.
	On the first DATA step iteration, 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 is returned to the input buffer. The 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. _ERROR_ is reset to 0, which prevents the contents of the PDV from being written to the SAS log.
	At this point, the program has currency on a DEPARTMENT record and needs to navigate the current occurrence of the DEPT-EMPLOYEE set. The DO UNTIL loop generates an OBTAIN Nth EMPLOYEE call for each EMPLOYEE record in the set. Valid N values are generated using the loop counter variable i and the PUT, LEFT, and TRIM functions. The N values are stored in the variable SEQ. The INPUT @; statement submits the call and places a hold on the input buffer while the status code is checked. For any unexpected status codes, execution branches to the STATERR label. For a successful OBTAIN Nth call, the INPUT statement maps employee information from the input buffer to the specified variables in the PDV and releases the input buffer. The DO UNTIL loop terminates when CA-IDMS returns an end-of-set status code (0307).
	The program now evaluates the condition in the IF statement and enters the DO-END block of code only if the employee INITDATE indicates a hire year of 1977. The %LET statement assigned the value 1977 to macro variable &HIREYEAR before the DATA step executed (see ). This variable was resolved when the DATA step was compiled. If the year portion of the employee INITDATE is 1977, then a FIND CURRENT DEPARTMENT is generated to obtain the owner of the current EMPLOYEE record. The null INPUT statement submits the call but does not place a hold on the input buffer because FIND does not return any data. If the FIND returns any status code other than 0000, execution branches to label STATERR.
	After the owner DEPARTMENT record is located, an OBTAIN CURRENT is generated to request that the DEPARTMENT record be placed into the input buffer. The INPUT @; statement submits the call and places a hold on the input buffer while the status is checked. For any status code other than 0000, execution branches to the STATERR label. For a successful OBTAIN call, the INPUT statement maps department information from the input buffer to the specified variables in the PDV and releases the input buffer. The OUTPUT statement writes the current observation to data set WORK.EMP. To avoid unnecessary input/output for departments that contain no employees with a hire year of 1977, the program postpones the OBTAIN of DEPARTMENT until the EMPLOYEE qualification criteria have been met. If you anticipate that many employees across multiple departments were hired in &HIREYEAR, then you could either OBTAIN DEPARTMENT before navigating the DEPT-EMPLOYEE set or add additional logic to OBTAIN CURRENT only once for the current set occurrence.
	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.
	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.

Supplying Transaction Information

                      Departments that Hired Employees in 1977                 
                                                                               
                                                        d                      
                                                        e                      
                                                   d    p                      
                                                   e    a                      
                       e                           p    r                      
                       m                           a    t                      
                       p   f                       r    m                      
                 i     l   i          l            t    e                      
                 n     o   r          a            m    n                      
                 i     y   s          s            e    t                      
                 t     e   t          t            n    _                      
                 d     e   n          n            t    n                      
     O           a     _   a          a            _    a                      
     b           t     i   m          m            i    m                      
     s           e     d   e          e            d    e                      
                                                                               
     1   07SEP1977   100   EDWARD     HUTTON     2000   ACCOUNTING AND PAYROLL 
     2   14MAY1977     4   HERBERT    CRANE      3200   COMPUTER OPERATIONS    
     3   04MAR1977   371   BETH       CLOUD      5300   BLUE SKIES             
     4   01DEC1977   457   HARRY      ARM        5100   BRAINSTORMING          
     5   23MAR1977    51   CYNTHIA    JOHNSON    1000   PERSONNEL              
     6   14DEC1977   119   CHARLES    BOWER      4000   PUBLIC RELATIONS       
     7   07JUL1977   158   JOCK       JACKSON    4000   PUBLIC RELATIONS       
     8   08SEP1977   149   LAURA      PENMAN     4000   PUBLIC RELATIONS       
     9   21JAN1977     3   JENNIFER   GARFIELD   3100   INTERNAL SOFTWARE

Top of Page