To create a PROC SQL table from
a query result, use a CREATE TABLE statement, and place it before
the SELECT statement. When a table is created this way, its data is
derived from the table or view that is referenced in the query's FROM
clause. The new table's column names are as specified in the query's
SELECT clause list. The column attributes (the type, length, informat,
and format) are the same as those of the selected source columns.
The following CREATE
TABLE statement creates the DENSITIES table from the COUNTRIES table.
The newly created table is not displayed in SAS output unless you
query the table. Note the use of the OUTOBS option, which limits the
size of the DENSITIES table to 10 rows.
libname sql 'SAS-library';
proc sql outobs=10;
title 'Densities of Countries';
create table sql.densities as
select Name 'Country' format $15.,
Population format=comma10.0,
Area as SquareMiles,
Population/Area format=6.2 as Density
from sql.countries;
select * from sql.densities;
Table Created from a Query Result
The following DESCRIBE
TABLE statement writes a CREATE TABLE statement to the SAS log:
proc sql;
describe table sql.densities;
SAS Log for DESCRIBE TABLE Statement for DENSITIES
NOTE: SQL table SQL.DENSITIES was created like:
create table SQL.DENSITIES( bufsize=8192 )
(
Name char(35) format=$15. informat=$35. label='Country',
Population num format=COMMA10. informat=BEST8. label='Population',
SquareMiles num format=BEST8. informat=BEST8. label='SquareMiles',
Density num format=6.2
);
In this form of the
CREATE TABLE statement, assigning an alias to a column renames the
column, but assigning a label does not. In this example, the Area
column has been renamed to SquareMiles, and the calculated column
has been named Densities. However, the Name column retains its name,
and its display label is
Country
.