Examples of SAS/ACCESS DATA Step Programs

Performing an Area Sweep

This example performs an area sweep of all DEPARTMENT records in the ORG-DEMO-REGION, and for each DEPARTMENT record, obtains all the EMPLOYEE records within the DEPT-EMPLOYEE set. An area sweep makes a sequential pass based on the physical location of a defined area for a specified record type. Records are accessed using the OBTAIN FIRST and OBTAIN NEXT DML calls. The example illustrates the concept of flattening out network record occurrences in an owner-member relationship. Owner (DEPARTMENT) information is repeated for each member (EMPLOYEE) in the set for observations written to the output SAS data set. The numbers in the program correspond to the numbered comments following the program.

1    *options $idmdbug;
     data work.dept_employee;

2    infile empss01 idms func=func 
     record=recname area=iarea sequence=seq 
     errstat=stat set=inset;


     /* BIND records to be accessed */

     if _n_ = 1 then do;
3        func     = 'BIND';
         recname  = 'DEPARTMENT';
         input;
         if stat ne '0000' then go to staterr;

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


         /* OBTAIN FIRST DEPARTMENT record */

4        seq     = 'FIRST';
         func    = 'OBTAIN';
         recname = 'DEPARTMENT';
         iarea   = 'ORG-DEMO-REGION';
     end;


     /* FIND and OBTAIN NEXT DEPARTMENT record */

5    if _n_ ge 2 then do;
         func  = 'FIND';
         seq   = 'OWNER';
         input;
         if stat ne '0000' then go to staterr;

         func     = 'OBTAIN';
         seq      = 'NEXT';
         recname  = 'DEPARTMENT';
         iarea    = 'ORG-DEMO-REGION';
         inset    = ' ';
     end;


6    input @;
     if stat not in ('0000', '0307') then go
         to staterr;


     /* Stop DATA step when all DEPARTMENT records */
     /* have been accessed                         */

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

     input @1   department_id    4.0
           @5   department_name  $char45.
           @50  department_head  4.0;


     /* OBTAIN EMPLOYEE records in set DEPT- */
     /* EMPLOYEE for CURRENT DEPARTMENT      */

7    seq     = 'FIRST';
     recname = 'EMPLOYEE';
     inset   = 'DEPT-EMPLOYEE';
     iarea   = ' ';

     do until (stat ne '0000');
        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.
                 @30  street          $char20.
                 @50  city            $char15.
                 @65  state           $char2.
                 @67  zip             $char9.
                 @75  phone           10.0
                 @85  status          $char2.
                 @87  ssnumber        9.0
                 @96  startdate       yymmdd6.
                 @102 termdate        6.0
                 @108 birthdate       yymmdd6.;
                 output;
         seq = 'NEXT';
       end;
    end;
8   _error_ = 0;
   return;


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

   proc print data=work.dept_employee;
       format startdate birthdate date9.;
       title1 'This is an Area Sweep of the DEPT-
             EMPLOYEE Set';
   run;

	See Statements Common to All SAS/ACCESS DATA Step Examples for a description of the OPTIONS statement.
	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.
	For the first iteration of the DATA step, initialize the call parameters to obtain the FIRST DEPARTMENT record in the ORG-DEMO-REGION area.
	For subsequent iterations of the DATA step, initialize the call parameters to find the OWNER of the current EMPLOYEE record so that the program can obtain the NEXT DEPARTMENT record in the area. The null INPUT statement forces the call to be generated and submitted, but no data is returned to the input buffer (see The Null INPUT Statement). The status code returned by the FIND call is checked before proceeding to the next call.
	The INPUT @; statement holds the contents of the input buffer so the program can check the status code returned by CA-IDMS. (See Holding Records in the Input Buffer.) For a successful call, the next INPUT statement moves DEPARTMENT information from the input buffer to the named variables in the PDV. 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. Because 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. Because non-blank status codes set the automatic DATA step variable _ERROR_ to 1, _ERROR_ is reset to 0 to prevent the contents of the PDV from being written to the SAS log.
	After a DEPARTMENT record has been obtained, issue an OBTAIN for all EMPLOYEES that occur within the current DEPT-EMPLOYEE set. The DO UNTIL loop issues OBTAIN calls, verifies the status code, and moves employee information from the input buffer to the named variables in the PDV. For each successful OBTAIN, the INPUT @; statement holds onto the current input buffer contents until the status code is checked. After all EMPLOYEE records in the set have been accessed, CA-IDMS returns a status code of 0307, which terminates the DO UNTIL loop.
	At this point, the STAT variable must have a value of 0307. Because 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.

                    This is an Area Sweep of the DEPT-EMPLOYEE Set              
                                                                                 
          department_                          department_  employee_            
     Obs       id      department_name             head         id     firstname 
                                                                                 
       1      2000     ACCOUNTING AND PAYROLL       11          69     JUNE      
       2      2000     ACCOUNTING AND PAYROLL       11         100     EDWARD    
       3      2000     ACCOUNTING AND PAYROLL       11          11     RUPERT    
       4      2000     ACCOUNTING AND PAYROLL       11          67     MARIANNE  
       5      2000     ACCOUNTING AND PAYROLL       11         106     DORIS     
       6      2000     ACCOUNTING AND PAYROLL       11         101     BRIAN     
       7      3200     COMPUTER OPERATIONS           4           4     HERBERT   
       8      3200     COMPUTER OPERATIONS           4          32     JANE      
                                                                                 
                                                                                 
     Obs  lastname    street               city        state   zip     phone     
                                                                                 
       1  BLOOMER     14 ZITHER TERR       LEXINGTON    MA    01675  617555554   
       2  HUTTON      781 CROSS ST         MELROSE      MA    02176  617665101   
       3  JENSON      999 HARVEY ST        MELROSE      MA    02176  617665555   
       4  KIMBALL     561 LEXINGTON AVE    LITTLETON    MA    01239  617492121   
       5  KING        716 MORRIS ST        MELROSE      MA    02176  617665616   
       6  NICEMAN     60 FLORENCE AVE      MELROSE      MA    02176  617665431   
       7  CRANE       30 HERON AVE         KINGSTON     NJ    21341  201334143   
       8  FERNDALE    60 FOREST AVE        NEWTON       MA    02576  617888811   
                                                                                 
                                                                                 
     Obs  status     ssnumber    startdate    termdate    birthdate              
                                                                                 
       1    40      103955781      880050      500000       60042                
       2    00      101122333      377090      700000       41030                
       3    60      102234789      180092      900000       48081                
       4    20      102277887      878091      900000       49042                
       5    10      106784551      680081      600000       60091                
       6    50      103345611       80050      600000       55121                
       7    30      101677745      177051      400000       42032                
       8    20      103456789      179090      900000       58011

Top of Page