SAS SQL Query Window User's Guide

Setting Your Profile

You can customize your SQL Query window sessions by specifying your own default settings and storing them in a profile. When you invoke the SQL Query window with the profile, your own preferences are automatically in effect. Your user-defined default settings are called preference settings. You can set up customized profiles for yourself or for a group of users. For example, a profile can be set up to restrict which table sources and tables will be available in a session of the SQL Query window.

Create a profile entry by selecting from the SQL Query Window PMENU

Profile Set Preferences

[IMAGE]

Configure Remote Session

Installations that license SAS/CONNECT software can use the SQL Query window to query tables or databases that are stored on remote hosts. To connect to a remote host you must first create an SQL Query window profile that contains information on the remote configuration.

Select Configure Remote Session in the Preference Settings for Profile window.

[IMAGE]

Fill in the fields in this window with values that are appropriate for your site.

The Description field can contain any descriptive text you enter to describe the remote configuration.

Select the button next to Setup SAS Data Library Libnames for Remote Session: to enter the values that will be used to submit SAS statements remotely.

[IMAGE]

To query DBMS data through a SAS/ACCESS libname engine, enter the name of the libref you want to create in the Libname field. Enter the name of the SAS/ACCESS libname engine you want to use (usually the DBMS name) in the Engine field. Enter libname options required for the libref in the Options: field. In most cases, leave the SAS Data Library Name field empty. For more information on libname engines, see your SAS/ACCESS documentation.

When you have entered your values, select OK to return to the Configure Remote Session window. Select OK to return to the Profile Preference Settings window.

Use the other items in the Profile Preference Settings window to specify any other preference settings that you want to include in your profile.

Select the Save button to save the profile.

Signing On to the Remote Host

You can sign on to the remote host either when you invoke the SQL Query window, or during an SQL Query window session.

To sign on to the remote host when you invoke the SQL Query window, specify the profile that contains the remote configuration information. The connection to the remote host is made automatically.
To sign on to the remote host during an SQL Query window session, select Switch to New Profile from the Tools PMENU. The Switch to New Profile window appears.

Specify the library, catalog name, and profile name for the profile that contains your remote configuration information. Select OK to make the connection to the remote host. This example shows the default library and catalog names SASUSER.PROFILE and the profile name REMOTE.
To remain signed on after exiting the SQL Query Window, select Remain signed on after exit Query Window in the Configure Remote Session window. If the remote host that is specified in your profile has already been signed onto during your SAS session, the SQL Query Window uses that connection to the remote host. You are not signed off from the remote host when you exit the SQL Query Window session.

Access Mode

Access Mode specifies the source of the data that you will access. The source can be either SAS (for SAS data files and views), or most of the database management systems (DBMSs) for which the PROC SQL Pass-Through facility is available if you have SAS/ACCESS software installed. If you are using a SAS/ACCESS libname engine to query DBMS data, set the access mode to SAS. This will allow you to access the DBMS data via the libraries defined in your SAS session.The default access mode is SAS.

[IMAGE]

Access Mode Options

For some DBMSs such as SYBASE and ORACLE, you must specify access mode options such as the user name, password, and server. When you select one of these DBMSs that require options from the Access Mode window, an Access Mode Options window appears.

[IMAGE]

For access modes such as DB2 that do not require any options, you can select Access Mode Options to set additional options.

[IMAGE]

Automatic Join

An automatic join data set contains the table names and column links that are required to join tables automatically in a Query Window session. When tables that are defined in the automatic join data set are selected together, the corresponding column links are used to automatically start the query's WHERE expression.

Select

Automatic Join Set Name for Automatic Join Data Set...

to specify the automatic join data set.

Select

Automatic Join Create an Automatic Join Data Set...

to create an automatic join data set.

Available Table Sources

You can use the list of Available Table Sources to create an autojoin data set. An autojoin data set is a SAS data set that contains the column links for any table joins. This data set can be shared by many users.

Available Tables

You can add tables and views to the list of Available Tables by selecting one or more of the table sources. Choose two tables at a time from the list of Available Tables. Select OK to display the Column Links window.

A column link defines a column relationship between two tables in which the value of the column in the first table equals the value of the column in the second table.

The columns for each table appear in their own Columns list. Choose a column from each table and select OK to generate the column link.

When the column links are built, select Show Links to view all the links.

Saving Your Automatic Join Data Set

Select Save to create your automatic join data set. The name of the automatic join data set will be included in the profile that you are creating. You can also select a different library name to choose a SAS data library that is associated with your current SAS session.

To replace an existing SAS data set, enter a different data set name in the Table field or select the [→] to choose a SAS data set name from the selected library. If the SAS data set is new, type in a new SAS data set name in the Table field.

The Set Automatic Join Data Set Name window enables you to specify your own automatic join data set. The default data set name is SASUSER.AUTOJOIN.

Updating Your Automatic Join Data Set

You can update an automatic join data set with PROC FSEDIT or PROC SQL. Automatic join data sets contain two columns, AUTOCOL1 and AUTOCOL2. Each column contains the library name, table name, and column name for one of the column links.

Creating an Automatic Join Data Set

The following example illustrates creating an automatic join data set. Select

Automatic Join Create an Automatic Join Data Set...

[IMAGE]

Select SAMPLE from the Table Source column to display a list of Available Tables. Select SAMPLE.EMPINFO and SAMPLE.LEAVE. Select OK. The Automatic Join Column Links window is displayed.

[IMAGE]

These two tables have the NAME column in common. Select NAME from the SAMPLE.EMPINFO Columns and SAMPLE.LEAVE Columns. Select OK. If the two tables had any other columns in common, you would be able to select these columns and store the column links in the automatic join data set.

Select Goback to return to the list of Available Tables.

Select Show Links to display the link that you have created between the two data sets.

[IMAGE]

Select Goback. You can select two tables again to define their column links. Select Save to save your automatic join.

[IMAGE]

Type Employee Leave as a label for your automatic join SAS data set. Select OK.

Select Goback to return to the Preference Settings for Profile window. Select Goback to return to the SQL QUERY TABLES window.

When you invoke the SQL Query window, the automatic join information in SASUSER.AUTOJOIN or in another SAS library that you have specified is included. If you invoke the SQL Query Window with a profile that contains an automatic join data set, the data set is used for automatic joins. If no automatic join data set has been defined in the profile, the default automatic join data set is SASUSER.AUTOJOIN.

Select

File End

to end your SQL Query Window session. Select OK in the dialog to return to the PROGRAM EDITOR window. Issue the following command in the PROGRAM EDITOR window:

query

to invoke another SQL Query Window session. Select SAMPLE.EMPINFO and SAMPLE.LEAVE from the list of Available Tables and add them to the list of Selected Tables. Select OK to display the SQL QUERY COLUMNS window.

Select DIVISION from the list of Available Columns and add it to the list of Selected Columns.

Select

View Where Conditions for Subset...

to display the WHERE EXPRESSION window.

[IMAGE]

The WHERE expression begins with the column link that you specified in your autojoin table.

Automatic Lookup

Automatic Lookup specifies a lookup table. Select Library and Table to specify existing library and table names or to name new ones. If you do not specify a lookup table name, the table name defaults to SASUSER.LOOKUP when you save the profile. If you invoke the SQL Query window without specifying a profile, the Query window uses SASUSER.LOOKUP if it exists.

Data Restrictions

Data Restrictions specifies the table sources, tables, and columns that will be available in an SQL Query window session that is invoked with this profile. Data Restrictions also shows you which table sources, tables, and columns you have made available for the profile.

Password Protect

Password Protect enables you to specify a SAS System password for your profile. After you enter the password, you are prompted to re-enter it for verification. Thereafter, users can invoke the SQL Query window with this profile without knowing the password; however, a user cannot update the profile without supplying the password.

Restrict Input Rows to Query

Restrict Input Rows to Query imposes a limit on the number of rows (observations) that the SQL Query window will process from any single table. This item is useful for debugging queries on large tables, or for preventing the excessive expenditure of computer resources that would result from running queries on huge tables.

Set SQL Options

Set SQL Options enables you to set SQL options for the execution of the query.

[IMAGE]

INOBS=: restricts the number of rows that are processed from any single source.
OUTOBS=: restricts the number of rows that are processed as the target.
LOOPS=: limits the number of iterations in the inner loop.
FLOW=: specifies the limit beyond which character columns are to be flowed to multiple lines.
SORTSEQ=: specifies the collating sequence to be used with an ORDER BY clause. Use this option to specify a collating sequence other than the default.

Keep Profile in Menu

Keep Profile in Menu enables you to remove or retain the Profile item in the PMENU and to turn on or off the ability to switch to a new profile from the PMENU.

Exit Confirmation

Exit Confirmation enables you to turn off the dialog that asks you if you want to end the query session. The dialog is displayed when you choose End from the File PMENU.

Chapter Contents
Previous
Next
Top of Page