Using the AS Keyword

AS Keyword Syntax

Suppose 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.
Syntax, 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 columns that will appear in the table.
FROM
specifies the tables or views to be queried.
optional query clauses
are used to refine the query further and include WHERE, GROUP BY, HAVING, and ORDER BY.
Here are the results of creating a table 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 turned off. Only the new table is generated as output.

Example: Creating a Table from a Query Result

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, Certadv.Payrollmaster and Certadv.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 certadv.payrollmaster,
              certadv.staffmaster
         where payrollmaster.empid
               = staffmaster.empid
               and jobcode contains 'TA';
quit;
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.
proc sql;
   select *
      from work.ticketagents;
quit;
Output 2.1 PROC SQL Query Result: Work.Ticketagents (partial output)
Partial Output of PROC SQL Query Result: Work.Ticketagents
The SAS log also displays a message, indicating that the table has been created.
Log 2.4 SAS Log
NOTE: Table WORK.TICKETAGENTS created, with 41 rows and 4 columns.
Last updated: October 16, 2019
..................Content has been hidden....................

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