Creating Output Tables

Overview

To create a new table from the results of a query, use a CREATE TABLE statement that includes the keyword AS and the clauses that are used in a PROC SQL query: SELECT, FROM, and any optional clauses, such as ORDER BY. The CREATE TABLE statement stores your query results in a table instead of displaying the results as a report.
General form, basic PROC SQL step for creating a table from a query result:
PROC SQL;
CREATE TABLE table-name AS
SELECT column-1<,...column-n>
FROM table-1|view-1<,...table-n|view-n>
<WHERE expression>
<GROUP BY column-1<,... column-n>>
<ORDER BY column-1<,... column-n>>;
Here is an explanation of the syntax:
table-name
specifies the name of the table to be created.
Note: A query can also include a HAVING clause, which is introduced at the end of this chapter. To learn more about the HAVING clause, see Performing Advanced Queries Using PROC SQL.
Note: The CREATE TABLE statement does not generate output. To view the contents of the table, use a SELECT statement as described in The SELECT Statement.

Example

Suppose that after determining the total miles traveled for each frequent-flyer membership class in the Sasuser.Frequentflyers table, you want to store this information in the temporary table Work.Miles. To do so, you can submit the following PROC SQL step: 
proc sql;
   create table work.miles as
      select membertype,
             sum(milestraveled) as TotalMiles
      from sasuser.frequentflyers
      group by membertype;
Because the CREATE TABLE statement is used, this query does not create a report. The SAS log verifies that the table was created and indicates how many rows and columns the table contains.
Table 1.2 SAS Log 
NOTE: Table WORK.MILES created, with three rows and two columns.
Tip
In this example, you are instructed to save the data to a temporary table that is deleted at the end of the SAS session. To save the table permanently in the Sasuser library, use the libref Sasuser instead of the libref Work in the CREATE TABLE clause.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.4.191