Chapter Contents

Previous

Next

Connecting Strings

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 engine translates 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:

String	Expands To
*SAS*	(SAS-conditions)
*ANDSAS*	AND (SAS-conditions)
*SASAND*	(SAS-conditions) AND
*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))
*NKSAS*	NK (SAS-conditions)
*SASAT(n)	(SAS-conditions)AT n

Syntax Limitations

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'

Using OR

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' *ANDNK*		C1='A'
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

Chapter Contents

Previous

Next

Top of Page

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