Chapter Contents
Previous
Next
SAS/ACCESS Interface to ADABAS Software

Using Periodic Group Fields in Selection Criteria

For an ADABAS periodic group data field, the ACCESS procedure automatically creates a SAS variable for the occurrence number within the periodic group. For example, the NATURAL DDM named CUSTOMERS has a periodic group field named SIGNATURE-LIST, which groups data fields LIMIT and SIGNATURE. The ACCESS procedure creates a SAS variable named SL_OCCUR for the occurrence numbers in LIMIT and SIGNATURE.

By including the _OCCUR variable in a view descriptor, you can retrieve the occurrence numbers for the periodic group. You can also include the _OCCUR variable in SAS WHERE clauses to qualify data, but the condition is processed by the SAS System after ADABAS has completed its selection processing. You cannot update the occurrence values, and you cannot use the _OCCUR variable in a view WHERE clause.

Periodic Group Fields in WHERE Clauses lists whether you can use periodic group SAS variable names, periodic group occurrence syntax, and a periodic group's corresponding _OCCUR variable in SAS and view WHERE clauses.

Periodic Group Fields in WHERE Clauses
Periodic Group Field SAS WHERE Clause View WHERE Clause
SAS variable name yes yes
ADABAS data field name and occurrence syntax no yes
_OCCUR variable yes no

WHERE Clause Examples

Using the periodic group data field LIMIT from the CUSTOMERS DDM, the following examples illustrate using a periodic group data field in WHERE clauses.

Example 1

You can use the SAS variable name of a data field within a periodic group in both a SAS WHERE clause and a view WHERE clause. However, they will not always produce the same results because the SAS WHERE clause post-processes the results and, using the following example, looks at the value of variable LIMIT to determine whether it's equal to 5000. The view WHERE clause is not post-processed; when you use a periodic group field, ADABAS qualifies all periodic group occurrence values if any one meets the WHERE clause criteria.

For example, you can include the following WHERE clause in a view descriptor, and you can issue it as a SAS WHERE clause: 

where limit = 5000
Stored in a view descriptor, the WHERE clause produces the results in Periodic Group Data Field Referenced in View WHERE Clause:

Periodic Group Data Field Referenced in View WHERE Clause
           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    12345678            1               5000.00
             2    14324742            1               5000.00
             3    14324742            2              25000.00
             4    14569877            1               5000.00
             5    14569877            2             100000.00
             6    19783482            1               5000.00
             7    19783482            2              10000.00
             8    26422096            1               5000.00
             9    26422096            2              10000.00
            10    27654351            1               5000.00
            11    29834248            1               5000.00

However, as a SAS WHERE clause, the results in Periodic Group Data Field Referenced in SAS WHERE Clause are produced.

Periodic Group Data Field Referenced in SAS WHERE Clause
           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    12345678            1               5000.00
             2    14324742            1               5000.00
             3    14569877            1               5000.00
             4    19783482            1               5000.00
             5    26422096            1               5000.00
             6    27654351            1               5000.00
             7    29834248            1               5000.00
             8    43459747            2               5000.00


Example 2

You can qualify a specific occurrence of a periodic group with a view WHERE clause, but only by using the periodic group occurrence syntax. However, all of the periodic group occurrence values for the qualified records are returned, not just the individual occurrence specified in the view WHERE clause. You cannot specify the occurrence syntax in a SAS WHERE clause. For example, this view WHERE clause produces the results in Periodic Group Occurrence Syntax in View WHERE Clause. 

where limit(2) = 5000

Periodic Group Occurrence Syntax in View WHERE Clause
           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    43459747            1               1000.00
             2    43459747            2               5000.00


Example 3

If you include the _OCCUR SAS variable in the view descriptor, you can use it in a SAS WHERE clause to specify an occurrence. However, you cannot use the _OCCUR variable in a view WHERE clause.

For example, this SAS WHERE clause produces the results shown in Periodic Group _OCCUR SAS Variable in SAS WHERE Clause. 

where sl_occur = 2

Periodic Group _OCCUR SAS Variable in SAS WHERE Clause
           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    14324742            2              25000.00
             2    14569877            2             100000.00
             3    14898029            2              50000.00
             4    18543489            2              50000.00
             5    19783482            2              10000.00
             6    19876078            2              25000.00
             7    26422096            2              10000.00
             8    43459747            2               5000.00

To qualify the data even further, you could use this SAS WHERE clause, which produces the results in Periodic Group _OCCUR Variable and Occurrence Syntax in SAS WHERE Clause. 

where sl_occur = 2 and limit = 5000

Periodic Group _OCCUR Variable and Occurrence Syntax in SAS WHERE Clause
           OBS    CUSTNUM      SL_OCCUR                 LIMIT

             1    43459747            2               5000.00


Chapter Contents
Previous
Next
Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.