Doing More with SAS/ASSIST Software

Entering SQL Statements Directly

Entering an SQL Query

The SQL you use in the SQL Editor window depends on the kind of Query Manager that you are using. If you have a SAS Query Manager, you use the SAS System's SQL procedure in the SQL Editor window. If you have a DB2 Query Manager, you use IBM's DB2 SQL.

A message appears at the top of the SQL Editor window to indicate which SQL is being used. If you need to use a different set of tables, you can create a new Query Manager or talk to your SAS Support Consultant or database administrator (DBA).

You can enter an SQL query in the SQL Editor window if you already know the libref or creator, table, and column names that you want to use.(footnote 1) The SQL Editor window is a complete editor with the following rules:

Enter and submit only one SQL query at a time
Enter one or more non-SELECT statements at a time, separating them with semicolons (;)
Do not submit both SQL queries and non-SELECT statements at the same time.

You can insert or delete lines in the window by using items in the Edit menu.

The query in the following display retrieves the average number of delayed flights each day.

SQL Editor Window With Query

[IMAGE]

You can now edit the query further or run it by selecting Submit from the Run menu. If you run the query, you can save your output to a SAS or DB2 table, as described in Creating New Tables.

Entering Non-SELECT SQL Code

Occasionally you might want to submit SQL statements that are not queries, such as statements that drop tables or create indexes. These non-query, non-SELECT statements do not produce output.

You can enter several non-SELECT SQL statements at one time, separated by semicolons. Do not end the final statement with a semicolon.

Note: Using more than one statement and semicolons applies only to non-SELECT SQL statements and not to SQL queries. [cautionend]

Select New from the File menu to clear the SQL Editor window. Then enter your SQL code into the window. For example,

create index jobcode on airline.jobcde2|jobcode); 
drop table airline.temp

Select Submit from the Run menu to submit the code to the SAS System or to DB2.

The following list includes some non-Select SQL statements that you might want to submit to your database.

CREATE creates a table, view, index, or other objects, depending on how the statement is specified.

DELETE deletes one or more rows from a table.

DROP deletes a table, view, index, or other objects, depending on how the statement is specified.

INSERT adds one or more rows to a table.

UPDATE modifies the data in columns of a row in a table.

GRANT gives users the authority to access or modify objects such as tables or views. (DB2 SQL only)

REVOKE revokes the privileges that were given to users by the GRANT statement. (DB2 SQL only)

For more information and restrictions on these and other SQL statements, see your SQL documentation.

You can use the prompt facility to display a prompt window when non-SELECT SQL statements are submitted. See Using the Prompt Facility for more information. Non-SELECT SQL statements can be saved to and executed from the Result Manager window. See Saving SQL Code or Output and Doing More with Results for information.

FOOTNOTE 1: For brevity, table means table or view unless otherwise noted. The "creator" refers to the SAS libname or your DB2 authorization identifier.

Chapter Contents
Previous
Next
Top of Page

CREATE	creates a table, view, index, or other objects, depending on how the statement is specified.
DELETE	deletes one or more rows from a table.
DROP	deletes a table, view, index, or other objects, depending on how the statement is specified.
INSERT	adds one or more rows to a table.
UPDATE	modifies the data in columns of a row in a table.
GRANT	gives users the authority to access or modify objects such as tables or views. (DB2 SQL only)
REVOKE	revokes the privileges that were given to users by the GRANT statement. (DB2 SQL only)