|Doing More with SAS/ASSIST Software|
Many times you might want to begin a new program by using part of another program. Or, you might want to begin a new program by using the tools that Query and Reporting offers to create a template program. The following sections show you how to use these tools to create a template that you can edit and customize for your purposes.
|Using a Stored Query or Statement|
can include a query that you have previously created in the Query window or include a query or non-SELECT statement that
you previously created in the SQL Editor window.
From the SQL Editor window, select Open from the File menu. The Open window appears with a
list of your saved queries or non-SELECT
statements (of types QUERY and SQL). Type an
S (for Select) next to
the query or statement that you want to include.
The Open window has the fields shown in the following table.
||Specifies the current catalog name. To change it, type
a two-level name or
||At the top of the display, the Name
field is empty, by default. To search for and limit the listing to certain
entries, type the complete name of an entry, or type a portion of a name with
a pattern-matching character (such as
||Displays the number of queries and non-SELECT statements. You can edit the value of the starting number to change the number of the rows displayed.|
||In the window's listing, Name displays the names of your queries or non-SELECT statements.|
||Describes your queries or non-SELECT statements.|
||Lists whether the entry was saved from the SQL Editor window (SQL) or from the Query window (QUERY).|
||Lists the date when the query or non-SELECT statement was created or last edited.|
You can also sort the contents of the window by Name, Description, Date, or Type by selecting Sort By from the View menu.
After you type
S in the Cmd field and press ENTER, the query or non-SELECT statement
is loaded into the SQL Editor window and a message
is displayed to indicate that the query has been loaded. Select Goback to return to the SQL Editor
If you load a query of type QUERY, changes that you make to your SQL code in the SQL Editor window are not reflected in the query that you display with the Query window. However, you can save your changed SQL code, as described in Saving SQL Code or Output.
You can now edit or run your SQL code. You can edit it in the SQL Editor window or select Enhanced Editor from the Edit menu. The Enhanced Editor enables you to make more significant changes to your code. See Using the Enhanced SQL Editor for more information.
|Selecting Tables with the Select Window|
You can use the Query and Reporting Select window to include tables in the SQL Editor. Select Query Manager from the Tools menu to open the Query and Reporting Select window. Choose one or more tables and columns, as described in Selecting Tables and Columns for Queries.
Note: Selections made from the Query and Reporting
Select window replace non-SELECT SQL statements in the SQL Editor window and append SQL queries in the SQL Query
window. If you want to append table selections to
non-SELECT SQL code, or want to replace SQL queries, do not use the Query
and Reporting Select window; instead, use the SQL Select Table window as described in the next section.
After you select your columns and select Close from the File menu, you return automatically to the SQL Editor window. SQL code that is based on your selections is displayed in the SQL Editor window.
Under DB2 SQL, if a column name has special or national characters (such as FLIGHT#), the column automatically appears in quotation marks.
If relations are defined on the tables, the tables are joined automatically; a WHERE clause is automatically generated in the SQL code. If relations are not defined, edit the SQL code to add a WHERE clause to complete the join.
|Choosing Tables with Select Table|
Another way to begin a template program is to follow these instructions:
SQL Select Table Window
SQL Editor Window With AIRLINE.DELAY Table Selected
In this example, no relations are defined on the AIRLINE.DELAY and AIRLINE.FLINFO tables. For a join to work efficiently, it must have a WHERE (or ON) clause, and so you must edit the SQL code.
You can do minor editing in the SQL Editor window by using the Edit menu's Insert Line and Delete Line items. For more substantial editing, use the SQL Enhanced Editor, as described in the following section.
Note: If you decide that you do not want to join AIRLINE.DELAY
with AIRLINE.FLINFO and instead want only AIRLINE.FLINFO, choose Select Table again. Type the name AIRLINE.DELAY and select Replace. The SQL
template program for the join is then replaced
by a template program for AIRLINE.FLINFO.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.