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
Create a profile entry by selecting, from the SQL Query Window,
Profile – Set Preferences
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
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.
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
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.
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 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.
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 select Access Mode Options to set additional options.
An automatic join (or "autojoin") data set contains the table
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.
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.
These two tables have the NAME column in common. Select NAME from both the SAMPLE.EMPINFO Columns list and the SAMPLE.LEAVE
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.
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.
If desired, type an appropriate label in the Label field. Select OK .
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.
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.
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.
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.
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.
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 specifies a lookup table. See "Using the Automatic Lookup Feature" on page 58 for information about the automatic lookup feature.
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 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 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 enables you to set SQL options for the execution of the query.
restricts the number of rows that are
restricts the number of rows that are processed as the target.
limits the number of iterations in the inner loop.
specifies the limit beyond which character columns are to be
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 enables you to remove or retain the
item on the SQL Query Window menu bar and to
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.