Creating a Table from a Query Result
Overview
Sometimes you want to create a new table that contains
both columns and rows that are derived from an existing table or set
of tables. In this situation, you can submit one PROC SQL step that
does both of the following:
-
creates a new table
-
populates the table with data from the result of a PROC SQL query.
To create a table from a query result, use a CREATE
TABLE statement that includes the keyword AS and the clauses that
are used in a query: SELECT, FROM, and any optional clauses, such
as ORDER BY.
|
General form, CREATE
TABLE statement with query clauses:
CREATE TABLE table-name AS
SELECT column-1<,
... column-n>
FROM table-1
| view-1<,
... table-n | view-n>
<optional
query clauses>;
table-name
specifies the name
of the table to be created.
SELECT
specifies the column(s)
that appear in the table.
FROM
specifies the table(s)
or view(s) to be queried.
optional query clauses
are used to refine
the query further and include WHERE, GROUP BY, HAVING, and ORDER BY.
|
You should be familiar
with the use of the CREATE TABLE statement to create a table from
a query result. Here is a review of this method.
When a table is created
from a query result,
-
the new table is populated with data that is derived from one or more tables or views that are referenced in the query's FROM clause
-
the new table contains the columns that are specified in the query's SELECT clause
-
the new table's columns have the same column attributes (type, length, informat, and format) as those of the selected source columns.
Note: When you are creating a table,
if you do not specify a column alias for a calculated column, SAS
assigns a column name, such as _TEMA001.
When query clauses are
used within a CREATE TABLE statement, that query's automatic report
generation is shut off. Only the new table is generated as output.
Example
Suppose you want to
create a new, temporary table that contains data for ticket agents
who are employed by an airline. The data that you need is a subset
of the data contained in two existing tables, Sasuser.Payrollmaster
and Sasuser.Staffmaster. The following PROC SQL step creates the new
table Work.Ticketagents from the result of a query on the two existing
tables. The WHERE clause joins the table by matching EMPID and selects
the subset of rows for employees whose JobCode contains TA.
proc sql; create table work.ticketagents as select lastname, firstname, jobcode, salary from sasuser.payrollmaster, sasuser.staffmaster where payrollmaster.empid = staffmaster.empid and jobcode contains 'TA';.
Note: Because this query lists
two tables in the FROM clause and subsets rows based on a WHERE clause,
the query is actually a PROC SQL inner join.
The new table Work.Ticketagents
is not empty; it contains rows of data. Therefore, you can submit
a SELECT statement to display Work.Ticketagents as a report:
select * from work.ticketagents;
The first four rows
of Work.Ticketagents are shown below.
The SAS log also displays
a message, indicating that the table has been created.
Copying a Table
To copy a table quickly, you can use the CREATE TABLE
statement with a query that returns an entire table instead of a subset
of columns and rows. The CREATE TABLE statement should contain only
the following clauses:
-
a SELECT clause that specifies that all columns from the source table should be selected
-
a FROM clause that specifies the source table.
Note: Remember that the order of
rows in a PROC SQL query result cannot be guaranteed, unless you use
an ORDER BY clause. Therefore, a CREATE TABLE statement without an
ORDER BY clause can create a table that contains the same rows as
the original table, but the rows might be in a different order.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.