SAS SQL Query Window User's Guide

SAS/AF Classes for the SQL Query Window

Several Screen Control Language (SCL) methods are available that enable you to use the SQL Query Window in SAS/AF applications. Refer to SAS Component Language: Reference for more information on SCL methods. Examples are shown later in this chapter on using SCL methods with the SQL Query Window.

WHERE Expression Builder Class

QWWHERE is the WHERE expression builder class that builds a WHERE expression by using columns from existing SAS data sets or SAS views. You can add the WHERE expression builder in the SQL Query Window to any SAS/AF application. The QWWHERE class provides the following methods:

WHERE_BLD

WHERE_BLD builds an available columns list and invokes the WHERE window. The WHERE_BLD method takes the following arguments:

SQLLIST type=listid use=IN/OUT: The master SCL list that is used by the WHERE expression builder. The first time that the WHERE expression is called, the application creates an empty list and passes it as the SQLLIST.
DSNAME type=char use=IN: The SAS data set or data sets from which the available columns are created. If this argument is blank, WHERE_BLD assumes that the available column list has already been built.
WHERELST type=listid use=OUT: Contains the WHERE list. If more than one data set has been used as input, the WHERE clause contains MEMNAME.COLUMNNAME. If one data set has been used as input, the WHERE clause contains COLUMNNAME only. This argument is optional.
WHTITLE type=char use=IN: Defines the title for the WHERE window. This argument is optional.
AVTITLE type=char use=IN: Defines the title for the available column selection list in the WHERE window. This argument is optional.
NOPROMPT type=char use=IN: Setting this argument disables <PROMPT at Run Time> as an available column selection choice. If this argument is set, GET_TEXT_LIST is no longer necessary. This argument is optional.
NOLOOKUP type=char use=IN: Setting this argument disables <LOOKUP distinct values> as an available column selection choice. Enabling <LOOKUP distinct values> allows the user to view lookup values for a column while in the WHERE window. This argument is optional.
AUTOLOOK type=char use=IN: Specifies the data set that can be included to make use of customized automatic lookup. See Using the Automatic Lookup Feature for more information on automatic lookups. This argument is optional.
WHEREMSG type=char use=OUT: This argument reports the status of the WHERE build and invocation. This argument is optional.

GET_TEXT

GET_TEXT creates an array that contains the WHERE expression text string, including the values from the <PROMPT at run time> window. GET_TEXT takes the following arguments:

SQLLIST type=listid use=IN: The master SCL list that is used by the WHERE expression builder.
TEXTARRAY type=char use=OUT: The text array that contains the text string for the existing WHERE expression. A text array should be defined in the caller program. The length of the array should be 200 characters.
WHEREMSG type=char use=OUT: Reports the status of the text character build. This argument is optional.

GET_TEXT_LIST

GET_TEXT_LIST creates an SCL list that contains the WHERE expression text string, including the values from the <PROMPT at run time> window. GET_TEXT_LIST takes the following arguments:

SQLLIST type=listid use=IN: The master list used by the WHERE expression builder.
WHRLST type=listid use=OUT: The SCL list that contains the text string for the existing WHERE expression.
WHEREMSG type=char use=OUT: Reports the status of the text character build. This argument is optional.

WHERE_CLEAN

WHERE_CLEAN removes SCL listids that are needed only for the WHERE expression builder and that are stored in SQLLIST. WHERE_CLEAN takes the following argument:

SQLLIST type=listid use=IN/OUT: The master SCL list that is used by the WHERE expression builder.

Query Invocation Class

QUERY is the query invocation class that invokes the Query window in different ways. The QUERY class invokes the SQL Query window with the following methods.

QW_EDIT

QW_EDIT invokes the SQL Query window. QW_EDIT takes the following arguments:

PROFILE type=char use=IN: The library.catalog.entry name of the user-defined profile that will define the parameters of the SQL Query Window session. This is an optional argument.
DATA type=char use=IN: Defines the table or tables that you want to be pre-selected for this query session. This argument is limited to 200 characters. This is an optional argument.
INCLUDE type=char use=IN: Contains the library.catalog.entry name of a previously saved query to include as the initial query. This is an optional argument.
ACCESS type=char use=IN: Contains the access mode for the SQL Query Window session. This is an optional argument.
NOEND type=char use=IN: Specifying the value "NOEND" turns off the confirmation window when exiting the SQL Query Window. This argument is optional.
INCLMSG type=char use=OUT: A message that reports the status of QW startup.

QW_EDIT_INCLUDE

QW_EDIT_INCLUDE invokes the SQL Query window with a previously saved query. QW_EDIT_INCLUDE takes the following arguments:

INCLUDE type=char use=IN: The library.catalog.entry name of a previously saved query to be included as your initial query.
NOEND type=char use=IN: Specifying the value "NOEND" turns off the confirmation window when exiting the SQL Query Window. This argument is optional.
INCLMSG type=char use=OUT: A message that reports the status of the initial include.

QW_EDIT_SELECTED

QW_EDIT_SELECTED invokes the SQL Query Window with a pre-selected table or tables. QW_EDIT_SELECTED takes the following argument:

DATA type=char use=IN: Defines the table or tables to be selected for this SQL Query Window session. This argument is limited to 200 characters.
NOEND type=char use=IN: Specifying the value "NOEND" turns off the confirmation window when exiting the SQL Query Window. This argument is optional.

QW_LIST_QUERIES

QW_LIST_QUERIES returns a list of previously saved queries to use for populating a list box. List box selections can be passed to the QW_EDIT_INCLUDE_RUN method. QW_LIST_QUERIES takes the following arguments:

LIBNAME type=char use=IN

The name of the SAS library.

CATALOG type=char use=IN

The catalog within the SAS library.

QUERYLST type=listid use=IN/OUT

Defines the SCL listid that contains the QUERY entries found in the catalog of the SAS library.

The items in QUERYLST contain the query name and query description separated by blanks.

QUERYMSG type=char use=OUT

Reports the status of QW_LIST queries. This is an optional argument.

QW_RUN

QW_RUN runs a query that has been previously saved and displays the output or passes the query to PROC REPORT. It does not invoke an SQL Query Window session. QW_RUN takes the following arguments:

INCLUDE type=char use=IN

The name of a previously saved query to be included.

RUNMODE type=char use=IN

The run query option. The values for RUNMODE are:

IMMEDIATE: The query is run and displayed in the output window.
REPORT: PROC REPORT is invoked with the results of the query.

If this query was last saved with a REPORT definition, the saved definition will be used. This argument is optional.

ACCESS type=char use=IN

The access mode for this query. Prior to Release 6.11 of the SAS System, saved queries did not include the access mode. If the saved query used an access mode other than SAS, the access mode must be supplied. An access mode value is not required for queries saved in Release 6.11 or later.

This is an optional argument.

PROFILE type=char use=IN

The name of the user-defined profile that defines the parameters of the Query. This argument is optional.

INCLMSG type=char use=OUT

A message that reports the status of QW startup. This argument is optional.

QW_EDIT_INCLUDE_RUN

QW_EDIT_INCLUDE_RUN invokes the SQL Query with a saved query and runs the query. QW_EDIT_INCLUDE_RUN takes the following arguments:

INCLUDE type=char use=IN

The name of a previously saved query to be included.

RUNMODE type=char use=IN

The run query option. This argument is optional. The values for this argument are:

IMMEDIATE: The query is run and displayed in the OUTPUT window.
REPORT: PROC REPORT is invoked with the results of the query. If this query was last saved with a REPORT definition, the saved definition is used.

This argument is optional.

ACCESS type=char use=IN

PROFILE type=char use=IN

The name of the user-defined profile that defines the parameters of the query. This argument is optional.

NOEND type=char use=IN

Specifying the value "NOEND" turns off the confirmation window when exiting the SQL Query Window. This argument is optional.

INCLMSG type=char use=OUT

A message that reports the status of QW startup. This argument is optional.

Column Expression Builder Class

QWCLEXPR is a column expression builder class that enables you to add the column expression builder in the SQL Query Window to any SAS/AF application. QWCLEXPR contains the following methods:

EDIT_ADD_EXPR

EDIT_ADD_EXPR builds an SCL list that contains columns that can be selected to build a column expression for a data set or data sets. The column expression builder is invoked, and the user can create a calculated column. After an expression is built, the expression is added to the EXPRMAST, the expression master SCL list.

If EXPRMAST argument is blank, EXPRMAST is created and the expression is added. EXPRMAST contains all sublists that are needed to modify that expression, such as available columns used, column expression type, available formats and expression attributes.

EDIT_ADD_EXPR takes the following arguments:

DSNAME type=char use=IN

The SAS data set or data sets from which the column is created. This argument is optional.

EXPRMAST type=listid use=IN/OUT

The master SCL expression list.

EXPRNAME type=char use=IN/OUT

The name of the expression. If EXPRNAME is blank, a new column expression is built and the new EXPRNAME is returned to the caller. If EXPRNAME is not blank, the column expression builder is invoked and updated or reset with the existing expression.

This argument is optional.

EXPRLABEL type=char use=IN

The label of the expression. This argument is optional.

EXPRFORMT type=char use=IN

The format of the expression. This argument is optional.

EXPRMSG type=char use=OUT

The status of the call for the expression builder. This argument is optional.

GET_TEXT

GET_TEXT creates a text array that contains the text string of an existing column expression, including format, label and expression name. GET_TEXT accepts the following arguments:

EXPRMAST type=listid use=IN: The master expression list.
TEXTARRAY type=char use=IN/OUT: The text array that contains the text string for an existing column expression.
EXPRNAME type=char use=IN: The name of an existing expression. If EXPRNAME is blank, the last expression created in the master expression list is used. This argument is optional.

GET_TEXT_LIST

GET_TEXT_LIST creates an SCL list that contains the text string of an existing column expression including format, label and expression name. GET_TEXT_LIST takes the following arguments:

EXPRMAST type=listid use=IN: The master expression list.
STRINGLST type=listid use=IN/OUT: The SCL list that contains the text string for an existing column expression.
EXPRNAME type=char use=IN: The name of an existing expression. This argument is optional.

GET_VALUE_LIST

GET_VALUE_LIST creates an SCL list with the text string of an existing column expression. The text string does not contain any of the column attributes such as label, alias, or format. If EXPRNAME is blank, the last expression created in the master expression list is used. GET_VALUE_LIST takes the following arguments:

EXPRMAST type=listid use=IN: The master expression list.
STRINGLST type=listid use=IN/OUT: The SCL list that contains the text string for an existing column expression.
EXPRNAME type=char use=IN: The name of an existing expression. This argument is optional.

SUBMIT_EXPR

SUBMIT_EXPR submits the text string of an existing column expression including format, label and expression name. If EXPRNAME is blank, the last expression created in the master expression list is used. SUBMIT_EXPR takes the following arguments:

EXPRMAST type=listid use=IN: The master expression list.
EXPRNAME type=char use=IN: The name of an existing expression. This argument is optional.

GET_ATTRIB

GET_ATTRIB extracts column expression attributes. If EXPRNAME is blank, the last expression created in the master expression list is used. GET_ATTRIB takes the following arguments:

EXPRMAST type=listid use=IN/OUT: The master expression list.
EXPRNAME type=char use=IN: The name of an existing expression. This argument is optional.
TYPE type=char use=OUT: The data type of an existing expression. This argument is optional.
FORMAT type=char use=OUT: The format of an existing expression. This argument is optional.
LABEL type=char use=OUT: The label of an existing expression. This argument is optional.

CLEAN_UP

CLEAN_UP removes the sublists of a specific expression that is stored in the expression master list. If EXPRNAME is blank, the last expression created in the master expression list is used. If EXPRNAME is _ALL_, all expressions are cleaned up. CLEAN_UP takes the following arguments:

EXPRMAST type=listid use=IN/OUT: The master expression list.
EXPRNAME type=char use=IN: The name of an existing expression. This argument is optional.

Chapter Contents
Previous
Next
Top of Page