Advanced Topics for Users

Connecting Strings to Order Conditions

The order in which SYSTEM 2000 processes conditions can affect which data records are selected. This is most obvious when you include a SYSTEM 2000 where-clause in a view descriptor, and specify a WHERE clause in a SAS program that uses the view descriptor. By default, the interface view engine connects the translated WHERE clause conditions in SAS to the end of the SYSTEM 2000 where-clause conditions by using the Boolean operator AND.

To affect the order of the connected conditions, you can include a connecting string in a SYSTEM 2000 where-clause to tell the engine how you want to connect the conditions. See Table A2.6.

Examples of Using Connecting Strings
SYSTEM 2000 where-clause in View Descriptor	WHERE Clause in SAS Program	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 WHERE conditions in SAS that it understands. [cautionend]

Table A2.7 summarizes the connecting strings that you can specify in a SYSTEM 2000 where-clause that is included in a view descriptor.

Strings to Specify in SYSTEM 2000 where-clauses
Connecting 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 for Specifying a Connecting String

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*

The following syntax is not acceptable:

    C1 = A *ANDSAS*

however, you can use the preceding syntax if you include a delimiter (special character.) In the following example, the delimiter is a set of single quotation marks:

    C1 = 'A' *ANDSAS*

Omitting a WHERE Clause in SAS

If a view descriptor includes a SYSTEM 2000 where-clause with a connecting string, and you do not execute a WHERE clause in SAS, there will be nothing to substitute. For example, suppose you have included the following SYSTEM 2000 where-clause (with the connecting string *SAS*) in a view descriptor:

    C1 = A AND *SAS*

Then, you issue a SAS program specifying a WHERE clause that produces the following SYSTEM 2000 condition:

    C110 > 27

If you do not specify a 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 WHERE clause in SAS, you can use the *ANDSAS* or *SASAND* connecting string. For example,

    C1 = 'A' *ANDSAS*

Then, even if you did not specify a WHERE clause in SAS, there would not be a problem. The result would be:

    C1 = 'A'

Using the OR Operator

You cannot use an OR operator to connect 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 the OR operator as shown in the following example:

    (C1 = A OR C1 = B) AND *SAS*

Using HAS, AT, and NON-KEY

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 WHERE clause in SAS. By using specific connecting strings, you can make the function of HAS, AT, and NON-KEY more useful in the SYSTEM 2000 where-clause and have the option of omitting the WHERE clause in SAS without introducing errors or unexpected results. See Table A2.8 for examples.

HAS, AT, and NON-KEY in SAS and SYSTEM 2000
SYSTEM 2000 where-clause in View Descriptor	WHERE Clause in SAS	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

Top of Page