|SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference|
The order in which SYSTEM 2000 software processes conditions can affect which data records are selected. This consideration is most obvious when you include a SYSTEM 2000 where-clause in a view descriptor and then specify a SAS WHERE clause in a SAS program that uses the view descriptor. By default, the interface view engine connects the translated SAS WHERE clause conditions with the Boolean operator AND to the end of the SYSTEM 2000 where-clause conditions.
Therefore, to affect the order of the connected conditions, you can include a connecting string in a SYSTEM 2000 where-clause to tell the interface view engine how you want to connect the conditions. The following examples illustrate how the engine would connect the conditions:
|View Where-Clause||SAS WHERE Clause||Connected Conditions|
|C1 = A||C110 > 27||(C1 = A) & (C110 > 27)|
|*SAS* & C1 = A||C110 > 27||(C110 > 27) & C1 = A|
|C1 = 'A' *ANDSAS*||C110 > 27||C1 = 'A' AND (C110 > 27)|
Note: Remember that the interface view
only those SAS WHERE conditions it understands.
|Available Connecting Strings|
The following list summarizes the available connecting strings that you can specify in a SYSTEM 2000 where-clause included in a view descriptor:
|*ANDNK*||AND (NK (SAS-conditions))|
|*NKAND*||(NK (SAS-conditions)) AND|
|*ANDAT(n)||AND ((SAS-conditions)AT n)|
|*ATAND(n)||((SAS-conditions) AT n) AND|
|*ANDHAS(record)||AND (record HAS (SAS-conditions))|
|*HASAND(record)||(record HAS (SAS-conditions))AND|
|*HASSAS(record)||(record HAS (SAS-conditions))|
You can specify a connecting string in a SYSTEM 2000 where-clause after a keyword or a special character. For example,
C1 = A AND *SAS*
is acceptable, but the following syntax is not:
C1 = A *ANDSAS*
To use the previous syntax, you can include a delimiter (special character), as shown below:
C1 = 'A' *ANDSAS*
|Optional Omission of a SAS WHERE Clause|
If a view descriptor includes a SYSTEM 2000 where-clause with a connecting string, and you do not execute a SAS WHERE clause, there will be nothing to substitute. For example, suppose you have included the following SYSTEM 2000 where-clause in a view descriptor:
C1 = A AND *SAS*
You issue a SAS program specifying a SAS WHERE clause that produces the following SYSTEM 2000 condition:
C110 > 27
If you do not specify a SAS WHERE clause in the SAS program, the "dangling connector" would result in a SYSTEM 2000 error.
C1 = A AND
If you want the flexibility of omitting the SAS WHERE clause, you can use the *ANDSAS* and *SASAND* connecting strings. For example, suppose you use *ANDSAS* instead of *SAS*:
C1 = 'A' *ANDSAS*
Then, even if you did not specify a SAS WHERE clause, there would not be a problem.
C1 = 'A'
You cannot have an OR operator connecting a connecting string to other parts of a view descriptor where-clause. For example, the following view descriptor where-clauses are not acceptable:
C1 = A OR *SAS* C1 = C OR (C1 = A OR C1 = B) *ANDSAS*
However, you can use OR in the following example:
(C1 = A OR C1 = B) AND *SAS*
|Using HAS, AT, and NON-KEY|
As mentioned, the HAS and AT operators and the NON-KEY specification are available in a SYSTEM 2000 where-clause, but they are not available in a SAS WHERE clause. With certain connecting strings, however, you can make their function more useful in the SYSTEM 2000 where-clause. Plus, you have the option of omitting the SAS WHERE clause without introducing errors or unexpected results. Here are a few examples:
|View Where-Clause||SAS WHERE Clause||Selection Criteria|
|C1='A' *ANDNK*||C2=B OR C3=X||C1='A' & (NK C2=B OR NK C3=X)|
|C1='A' *ANDHAS(C0)||C21=B & C22=X||C1='A' AND (C0 HAS (C21=B & C22=X))|
|*ATAND(12) C1=A||C21=B||C21=B AT 12 & C1=A|
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.