Using the SAS/ACCESS Interface to CA-IDMS

Example: Traversing a Set

The following DATA step shows how to traverse the DEPT-EMPLOYEE set using the CA-IDMS INFILE and CA-IDMS INPUT statements. The numbers in the program correspond to the numbered comments following the program.

1  data work.dept_employee;

2  infile empss01 idms func=func1 record=recname 
          area=iarea sequence=iseq errstat=err 
          set=iset;

   /* BIND the DEPARTMENT and EMPLOYEE       */
   /* records in the first data set          */
   /* iteration;  if successful, then        */
   /* OBTAIN FIRST DEPARTMENT WITHIN AREA    */

3  if _n_ = 1 then do;
      func1      = 'BIND';
      recname    = 'DEPARTMENT';

4     input;
      if (err ne '0000') then go to staterr;
      recname  = 'EMPLOYEE';
      input;
      if (err ne '0000') then go to staterr;

      /* Get a DEPARTMENT record */

      iseq       = 'FIRST';
      func1      = 'OBTAIN';
      recname    = 'DEPARTMENT';
      iarea      = 'ORG-DEMO-REGION';
   end;

   else do;
      func1      = 'FIND';
      iseq       = 'OWNER';
      input;
      if (err ne '0000') then go to staterr;
      func1      = 'OBTAIN';
      iseq       = 'NEXT';
      recname    = 'DEPARTMENT';
      iarea      = 'ORG-DEMO-REGION';
      iset       = ' ';
   end;

   /* OBTAIN DEPT record and test     */
   /* for success */

5  input @;
6  if (err ne '0000' and err ne '0307') then
     go to staterr;
   if err eq '0307' then do;
      _error_ = 0;      
      /* No more DEPT records so STOP */
      stop;
   end;
7  input @1   department_id    4.0
         @5   department_name  $char45.
         @50  department_head  4.0;

   /* Get the EMPLOYEE records for this DEPT */
   /* record */

   iseq       = 'FIRST';
   recname    = 'EMPLOYEE';
   iset       = 'DEPT-EMPLOYEE';
   iarea      = ' ';
   do until (err = '0307');

     /* OBTAIN EMPLOYEE records and test for */
     /* SUCCESS */

     input @;
     if (err ne '0000' and err ne '0307') then
        go to staterr;
     if err = '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       $char9.
              @96  startdate      8.0
              @104 termdate       8.0
              @112 birthdate      8.0;
8              output;
9         iseq = 'next';
      end;
   end;
   _error_ = 0;
   return;

   staterr:
      put @1 'WARNING: ' @10 func1 @17 
             'RETURNED ERR ='@37 err;
      stop;
   run;

10  proc print data=work.dept_employee;
   title1 'This is an Area Sweep of the 
           DEPT-EMPLOYEE Set';
   title2 'The Area Sweep is from the Beginning to End';
   run;

	The DATA statement references a temporary SAS data set called DEPT_EMPLOYEE, which is to be opened for output.
	The INFILE statement tells SAS to use the EMPSS01 subschema. The IDMS option tells SAS that EMPSS01 is a CA-IDMS subschema instead of a fileref. The statement also tells the DATA step interface to use the SAS variables as follows: FUNC1 to contain the function type RECNAME to contain the record name IAREA to contain the area name ISEQ to contain the function call sequence information ISET to contain the set name. The statement also tells the interface to store the call status in ERR.
	All record types to be retrieved must first be bound to CA-IDMS. The BIND function call needs to be issued only once per record type before retrieval. The automatic SAS variable _N_ is used to indicate the first iteration of the DATA step code.
	The INPUT statements generate and submit the function call to CA-IDMS requesting that a BIND be performed for the record type specified in RECNAME. In this example, the DEPARTMENT record type is bound first, then the EMPLOYEE record type is bound.
	This INPUT statement also uses the values in the SAS variables FUNC1 and RECNAME, along with the values in ISEQ and IAREA to generate an OBTAIN FIRST DEPARTMENT RECORD IN AREA ORG-DEMO-REGION DML call. However, no data is moved into the program data vector because no variables are defined on the INPUT @; statement. This function call enables the DATA step to check the status that is returned from CA-IDMS before moving data into the program data vector. This function call is issued only on the first iteration of the DATA step. On subsequent iterations, the values in these SAS variables are used to generate an OBTAIN NEXT DEPARTMENT RECORD IN AREA ORG-DEMO-REGION DML call.
	The program examines the status code returned by CA-IDMS. If CA-IDMS returns 0000, then the program proceeds to the next statement. If CA-IDMS returns 0307 (end of set), then there are no more department records and the DATA step stops.
	When this INPUT statement executes, DEPARTMENT RECORD data is moved from the SAS buffer into the program data vector.
	As the DATA step executes, EMPLOYEE records that are members of the DEPT-EMPLOYEE set are retrieved, and observations that contain the EMPLOYEE data is written to the DEPT_EMPLOYEE data set.
	The ISEQ value is changed to NEXT to generate an OBTAIN NEXT EMPLOYEE RECORD IN SET DEPT-EMPLOYEE DML call.
	The PRINT procedure prints the list of DEPARTMENT and EMPLOYEE records.

The following output shows the SAS log for this example.

SAS Log for Traversing a Set

   1          data work.dept_employee(drop=filler);
   2          infile empss01 idms func=func1
   3                              record=recname
   4                              area=iarea
   5                              sequence=iseq
   6                              errstat=err
   7                              set=iset;
              .
              .
              .
   91         run;
   NOTE: The infile EMPSS01 is:
         Subschema=EMPSS01
   NOTE: 86 records were read from the infile EMPSS01.
         The minimum record length was 0.
         The maximum record length was 116.
   NOTE: The data set WORK.DEPT_EMPLOYEES has 56 
         observations and 16 variables.
   NOTE: The DATA statement used 0.37 CPU seconds 
         and 2709K.
   92         proc print data=work.dept_employees;
   93         title1 'This is an Area Sweep of the 
                      DEPT-EMPLOYEE Set';
   94         title2 'The Area Sweep is from the 
                      Beginning to End';
   95         run;
   NOTE: The PROCEDURE PRINT printed pages 1-3.

The following output shows a portion of the results of this example.

Traversing a Set

                                    This is an Area Sweep of the DEPT-EMPLOYEE Set
                                    The Area Sweep is from the Beginning to End
   
         department_                           department_  employee_
   Obs      id           department_name         head          id        firstname   lastname    street
     1     2000          ACCOUNTING AND PAYROLL   11           69         JUNE        BLOOMER    14 ZITHER TERR
     2     2000          ACCOUNTING AND PAYROLL   11           100        EDWARD      HUTTON     781 CROSS ST
     3     2000          ACCOUNTING AND PAYROLL   11           11         RUPERT      JENSON     999 HARVEY ST
     .        .          .                        .            .          .           .           .           
     .        .          .                        .            .          .           .           .           
     .        .          .                        .            .          .           .           .           
    24     5100          BRAINSTORMING            15           15         RENE        MAKER      10 DROVER DR
    25     5100          BRAINSTORMING            15           341        RICHARD     MUNYON     17 BLACKHILL DR
    26     5100          BRAINSTORMING            1            458        RICHARD     WAGNER     677 GERMANY LN
   
   Obs    city          state     zip       phone      status     ssnumber    startdate   termdate    birthdate
     1    LEXINGTON      MA      01675    617555554      40      103955781     880050      500000       60042
     2    MELROSE        MA      02176    617665101      00      101122333     377090      700000       41030
     3    MELROSE        MA      02176    617665555      60      102234789     180092      900000       48081
     .       .           .        .       .              .       .             .           .            .      
     .       .           .        .       .              .       .             .           .            .      
     .       .           .        .       .              .       .             .           .            .      
    24    BOSTON         MA      02123    617452141      40      101067334     378010      200000       45052
    25    WESTWOOD       MA      02090    617329001      70      111100208     180111      400000       50121
    26    NATICK         MA      02178    617432110      90      101177666     378060      700000       34030
   
   
                                    This is an Area Sweep of the DEPT-EMPLOYEE Set  
                                  The Area Sweep is from the Beginning to End
   
         department_                           department_  employee_
   Obs     id            department_name          head         id        firstname   lastname     street
    27   1000            PERSONNEL                13           81         TOM         FITZHUGH    450 THRUWAY ST
    28   1000            PERSONNEL                13           51         CYNTHIA     JOHNSON     17 MANIFESTO DR
    29   1000            PERSONNEL                13           91         MADELINE    ORGRATZI    67 RAINBOW DR
     .   .               .                        .            .          .       .           .                 
     .   .               .                        .            .          .       .           .                 
     .   .               .                        .            .          .       .           .                 
    50   3100           INTERNAL SOFTWARE         3            35         LARRY       LITERATA    123 SATURDAY TERR
    51   3100           INTERNAL SOFTWARE         3            23         KATHERINE   O'HEARN     12 EAST SPEEN ST
    52   3100           INTERNAL SOFTWARE         3            21         RALPH       TYRO        888 FORTITHE ST
   
   Obs    city          state     zip       phone      status     ssnumber    startdate    termdate    birthdate
    27    MANSFIELD      MA      03458    617882012      30      111234567     881091      900000       56021
    28    WALPOLE        MA      02546    617777888      80      501134787     877032      300000       45010
    29    KENDON         MA      06182    617431191      90      123106787     880101           0       51101
     .       .            .        .          .           .          .           .           .            .      
     .       .            .        .          .           .          .           .           .            .      
     .       .            .        .          .           .          .           .           .            .      
    50    WILMINGTON     MA      02476    617591232      30      102356783     180090      900000       55043
    51    NATICK         MA      02364    617889713      40      101955671     278050      400000       54040
    52    SINGER         MA      02254    617445919      10      101989345     680122      100000       55122

Top of Page