Customizing Your Session and Using Advanced Features

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, you can define a profile to specify which table sources and tables are available in an SQL Query Window session.

Create a profile entry by selecting, from the SQL Query Window,Profile Set Preferences.

[Preference Settings for Profile window]

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 systems. To connect to a remote system you must first create an SQL Query Window profile that contains information about the remote configuration.

Select the right arrow next to Configure Remote Session in the Preference Settings for Profile window.

[Configure Remote Session window]

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

Use the Description field to describe the remote configuration.

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

[Configure Remote Session SAS Libnames window]

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

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 Save 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 Tools Switch to New Profile. In the Switch to New Profile window, 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.
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 on to during your SAS session, then 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 library engine to query DBMS data, then set the access mode to SAS. This mode enables you to access the DBMS data via the libraries that are defined in your SAS session. The default access mode is SAS.

[Selecting an access mode]

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.

[Acces Mode Options window]

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

[DB2 Access Mode Options window]

Automatic Join

An automatic join (or "autojoin") data set contains the table names and column links that are required to join tables automatically in an SQL 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. An autojoin data set can be shared by many users.

Creating an Automatic Join Data Set

The following example illustrates the creation of an automatic join data set. Select the right arrow next to the Automatic Join field, and then select Create an Automatic Join Data Set from the pop-up menu that appears.

[Creating an automatic join data set]

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

[Automatic Join Column Links window]

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

Select Goback to return to the Available Tables list.

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

[Displaying column links]

Select Goback. You can repeat the previous procedure for as many pairs of tables as you want.

When you are finished defining column links, select Save to save your automatic join data set.

[Saving the automatic join data set]

If desired, type an appropriate label in the Label field. Select OK.

Note: For this example, the default SASUSER.AUTOJOIN data set is used. However, by specifying a different library or table name, you can save the autojoin data set to a different location. [cautionend]

Select Goback to return to the Preference Settings for Profile window. Select Save and then OK to save the changes to the profile. Select Close to return to the SQL QUERY TABLES window.

When you start the SQL Query Window, the software looks for the automatic join data set that is specified in the default profile, whether the automatic join data set is the default SASUSER.AUTOJOIN or some other data set that you have specified. If an autojoin data set is not found, then no automatic joins are performed.

Select File Close to end your SQL Query Window session. Select OK in the dialog box that appears in order to return to the Program Editor.

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

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

Select View Where Conditions for Subset to display the WHERE EXPRESSION window.

[WHERE EXPRESSION window with column link]

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

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, in the format libname.table-name.column-name, for one of the column links.

Selecting a Different Automatic Join Data Set

You can select a different automatic join data set for a given profile. In the Preference Settings for Profile window, select the right arrow next to the Automatic Join field, then select Set Name for Automatic Join Data Set from the pop-up menu that appears. Select the library and table name of the desired autojoin data set and select OK.

Automatic Lookup

Automatic Lookup specifies a lookup table. See Using the Automatic Lookup Feature for information about the automatic lookup feature.

Data Restrictions

Data Restrictions specifies the table sources, tables, and columns that will be available in an SQL 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 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 large tables.

Set SQL Options

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

[SQL Option Settings for Profile window]

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 on the SQL Query Window menu bar and to turn on or off the ability to switch to a new profile from the Tools menu.

Exit Confirmation

Exit Confirmation enables you to turn off the dialog box that asks you if you want to end the query session. The dialog box appears when you select Close from the File menu.

Top of Page