|SAS SQL Query Window User's Guide|
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
|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.
Configure Remote Session in the Preference Settings
for Profile window.
Fill in the fields in this window with values that are appropriate for your site.
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.
To query DBMS data through a
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.
Save button to save the profile.
You can sign on to the remote host either when you invoke the SQL Query window, or during an SQL Query window session.
Switch to New Profilefrom the
ToolsPMENU. 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
to make the connection to the remote host. This example shows the default
library and catalog names
SASUSER.PROFILE and the profile name
Remain signed on after exit Query Windowin the
Configure Remote Sessionwindow. 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 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.
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.
For access modes such as DB2 that do not require
any options, you can
Access Mode Options to set additional options.
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.
|Automatic Join||Set Name for Automatic Join Data Set...|
to specify the automatic join data set.
|Automatic Join||Create an Automatic Join Data Set...|
to create an automatic join data set.
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
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
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
When the column links are built, select
Show Links to view all
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.
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.
The following example illustrates creating an automatic join data set. Select
|Automatic Join||Create an Automatic Join Data Set...|
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.
These two tables have the NAME
column in common. Select NAME from
the SAMPLE.EMPINFO Columns and SAMPLE.LEAVE Columns. Select
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.
Goback to return to the list of Available Tables.
Show Links to display the link that you have created
between the two data sets.
Goback. You can select two tables again to define
their column links. Select
Save to save your automatic join.
Employee Leave as a label for your automatic join
SAS data set. Select
Goback to return to the Preference Settings for Profile
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.
to end your SQL Query Window session. Select
in the dialog to return to the PROGRAM EDITOR window. Issue the following
command in the PROGRAM EDITOR window:
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
Select DIVISION from the list of Available Columns and add it to the list of Selected Columns.
|View||Where Conditions for Subset...|
to display the WHERE EXPRESSION window.
The WHERE expression begins with the column link that you specified in your autojoin table.
Automatic Lookup specifies a lookup table. Select
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 specifies the table sources, tables, and columns
that will be available in an SQL Query window session that is invoked with
Data Restrictions also shows you which table sources,
tables, and columns you have made available for the profile.
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
|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 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
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.