Examples of SAS/ACCESS DATA Step Programs

Using RETURN and GET Across Executions of the DATA Step

This example contains two separate DATA steps and demonstrates the use of the RETURN and GET calls across executions of the DATA step. The first DATA step creates an output data set containing index values from EMP-NAME-NDX. The RETURN command is used to navigate the index set. The index values stored in WORK.EMPSRTKY are used to locate EMPLOYEE records in the second DATA step. Once a record is located, a GET call moves the record data to the input buffer. The numbers in the program correspond to the numbered comments following the program.

1  *options $idmdbug;
   data work.empsrtky;
     length namekey $ 25;
     keep namekey;

2    infile empss01 idms func=func sequence=seq
            dbkey=dkey sortfld=skey errstat=stat 
            set=inset;


     /* RETURN EMP-NAME-NDX key values to store */
     /* in EMPSRTKY data set                    */

3    func    = 'RETURN';
     seq     = 'FIRST';
     inset   = 'EMP-NAME-NDX';
     skey    = ' ';
     dkey    = ' ';

4    do until (stat ne '0000');
        input;
        if stat not in ('0000', '1707') then go to
            staterr;
        if stat = '0000' then do;
           namekey = skey;
           output;
           dkey  =  ' ';
           skey  =  ' ';
           seq   =  'NEXT';
        end;
     end;

5    _error_ = 0;
     stop;

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

   proc print data=work.empsrtky;
     title1 'This is a List of Index Entries from
        EMP-NAME-NDX';
   run;

   data work.employee;
      drop namekey;

7    infile empss01 idms func=func sortfld=skey
            ikeylen=keyl errstat=stat set=inset
            record=recname;


      /* BIND the record to be accessed */

8    if _n_ = 1 then do;
        func  =  'BIND';
        recname  =  'EMPLOYEE';
        input;
        if stat ne '0000' then go to staterr;
     end;


     /* Read NAMEKEY values from EMPSRTKY and */
     /* FIND EMPLOYEE using the EMP-NAME-NDX  */

9    set work.empsrtky;
     func     =  'FIND';
     recname  =  'EMPLOYEE';
     inset    =  'EMP-NAME-NDX';
     skey     =  namekey;
     keyl     =  25;
     input;
     if stat not in ('0000', '0326') then go to
         staterr;
     if stat = '0000' then do;
        func     =  'GET';
        recname  =  ' ';
        inset    =  ' ';
        skey     =  ' ';
        keyl     =  0;
        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;
10    else do;
        put @1 'WARNING: No EMPLOYEE record with
                  name = ' namekey;
        put @1 'WARNING: Execution continues with 
                  next NAMEKEY';
        _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: ' inset= skey= keyl= recname=;
      put @1 'ERROR: DATA step execution 
                terminating.';
      _error_ = 0;
      stop;
   run;

   proc print data=work.employee;
     format startdate birthdate date9.
     title1 'This is a List of Employee Information
                 Obtained';
     title2 'Using a Transaction Data Set 
                 Containing Name Index Values';
   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.
	Parameter values are initialized to generate the RETURN CURRENCY SET call for the entries in the EMP-NAME-NDX index set. The SKEY and DKEY variables are set to blank and will be assigned the sort key and database key values returned from the call.
	In the DO UNTIL loop, the null INPUT statement submits the generated RETURN CURRENCY SET FIRST/NEXT call. The call returns sort key and database key values to the SKEY and DKEY variables. For any unexpected status code, execution branches to the STATERR label. For a successful call, the SKEY value is assigned to NAMEKEY, the current NAMEKEY is written to WORK.EMPSRTKY, SKEY and DKEY variables are reset to blank, and SEQ is set to NEXT. The next iteration of the DO UNTIL loop will return the next index entry. The DO UNTIL loop executes as long as STAT equals 0000. When the index set has been traversed and all sort values returned and stored in output data set WORK.EMPSRTKY, CA-IDMS returns a 1707 status code, which terminates the loop.
	When the DO UNTIL loop terminates, _ERROR_ is reset to 0, which prevents the contents of the PDV from being written to the SAS log. The index set is traversed in the DO UNTIL loop during the first DATA step iteration, so a STOP statement is used to prevent the DATA step from executing again. Without the STOP statement, the DATA step would loop endlessly, traversing the same index set once for each iteration.
	See Statements Common to All SAS/ACCESS DATA Step Examples for a description of the STATERR statements.
	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.
	The WORK.EMPSRTKY data set, which was created in the first DATA step, serves as a transaction data set. Each iteration of this DATA step reads a new sort key value, NAMEKEY, and uses it to locate an EMPLOYEE record via the EMP-NAME-NDX. The DATA step terminates when all observations have been read from WORK.EMPSRTKY. To gather employee information, INFILE parameter variables are initialized to generate the FIND EMPLOYEE WITHIN EMP-NAME-NDX call using the supplied sort key from NAMEKEY. The IKEYLEN= parameter variable KEYL is set to 25 to indicate the sort key length. The null INPUT statement submits the FIND call but places no hold on the input buffer because no record data is returned. For any unexpected status code, execution branches to the STATERR label. For a successful FIND, a GET call is generated to request that the record data be retrieved. The INPUT @; statement submits the GET call and places a hold on the input buffer so the status code can be checked. Any status code not equal to 0000 branches execution to the STATERR label. If the GET call is successful, the INPUT statement maps EMPLOYEE data from the input buffer to the specified variables in the PDV. The contents of the PDV are then written as an observation to output data set WORK.EMPLOYEE.
	This group of statements enables execution to continue when no EMPLOYEE record exists for the sort key value specified in the transaction data set. In this case, an informative WARNING message is 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 RETURN and GET

               This is a List of Index Entries from EMP-NAME-NDX             
                                                                             
                        Obs            namekey                               
                                                                             
                          1    ANDALE         ROY                            
                          2    ANGELO         MICHAEL                        
                          3    ARM            HARRY                          
                          4    BANK           MONTE                          
                          5    BLOOMER        JUNE                           
                          6    BOWER          CHARLES                        
                          7    BREEZE         C.                             
                          8    CLOTH          TERRY                          
                          9    CLOUD          BETH                           
                         10    CRANE          HERBERT                        
                         11    CROW           CAROLYN                        
                         12    DONOVAN        ALAN                           
                         13    DOUGH          JANE                           
                         14    FERNDALE       JANE                           


                 This is a List of Employee Information Obtained               
            Using a Transaction Data Set Containing Name Index Values          
                                                                               
       employee_                                                               
   Obs     id     firstname  lastname   street               city        state 
                                                                               
     1    466     ROY        ANDALE     44 TRIGGER RD        FRAMINGHAM   MA   
     2    120     MICHAEL    ANGELO     507 CISTINE DR       WELLESLEY    MA   
     3    457     HARRY      ARM        77 SUNSET STRIP      NATICK       MA   
     4      7     MONTE      BANK       45 EAST GROVE DR     HANIBAL      MA   
     5     69     JUNE       BLOOMER    14 ZITHER TERR       LEXINGTON    MA   
     6    119     CHARLES    BOWER      30 RALPH ST          WELLESLEY    MA   
     7    467     C.         BREEZE     200 NIGHTINGALE ST   FRAMINGHAM   MA   
     8    479     TERRY      CLOTH      5 ASPHALT ST         EASTON       MA   
     9    371     BETH       CLOUD      3456 PINKY LN        NATICK       MA   
    10      4     HERBERT    CRANE      30 HERON AVE         KINGSTON     NJ   
    11    334     CAROLYN    CROW       891 SUMMER ST        WESTWOOD     MA   
    12    366     ALAN       DONOVAN    6781 CORNWALL AVE    MELROSE      MA   
    13     24     JANE       DOUGH      15 LOCATION DR       NEWTON       MA   
    14     32     JANE       FERNDALE   60 FOREST AVE        NEWTON       MA   
                                                                               
                                                                               
   Obs  zip      phone    status  ssnumber   startdate  termdate  birthdate    
                                                                               
     1 03461  6175541108    03    027601115  15JUN1978      0     04MAR1960    
     2 01568  6178870235    01    127675593  08SEP1979      0     05APR1957    
     3 02178  6174320923    05    028770147  01DEC1977      0     05APR1934    
     4 02415  6173321933    01    022446676  30APR1978      0     01JAN1950    
     5 01675  6175555544    01    039557818  05MAY1980      0     25APR1960    
     6 01568  6178841212    01    092345812  14DEC1977      0     04MAR1939    
     7 03461  6175542387    01    111556692  02JUN1979      0     04MAY1934    
     8 05491  6177738398    01    028701666  02NOV1979      0     04MAR1945    
     9 02178  6174321212    01    326710472  04MAR1977      0     09SEP1945    
    10 21341  2013341433    01    016777451  14MAY1977      0     21MAR1942    
    11 02090  6173291776    01    023980110  17JUN1979      0     03APR1944    
    12 02176  6176655412    01    025503622  10OCT1981      0     17NOV1951    
    13 02456  6174458155    01    022337878  08AUG1976      0     29MAR1951    
    14 02576  6178888112    01    034567891  09SEP1979      0     17JAN1958

Top of Page