O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Determining the Structure of a Table
To obtain a list of all of the columns in a table and their attributes, you can use the
DESCRIBE TABLE statement. The following example generates a description of the
Sql.UnitedStates table. PROC SQL writes the description to the log.
libname sql 'SAS-library';
proc sql;
describe table sql.unitedstates;
Log 2.1 Portion of Log to Determine the Structure of a Table
NOTE: SQL table SQL.UNITEDSTATES was created like:
create table SQL.UNITEDSTATES( bufsize=12288 )
(
Name char(35) format=$35. informat=$35. label='Name',
Capital char(35) format=$35. informat=$35. label='Capital',
Population num format=BEST8. informat=BEST8. label='Population',
Area num format=BEST8. informat=BEST8.,
Continent char(35) format=$35. informat=$35. label='Continent',
Statehood num
);
Creating New Columns
In addition to selecting columns that are stored in a table, you can create new columns
that exist for the duration of the query. These columns can contain text or calculations.
PROC SQL writes the columns that you create as if they were columns from the table.
Adding Text to Output
You can add text to the output by including a string expression, or literal expression, in a
query. The following query includes two strings as additional columns in the output:
libname sql 'SAS-library';
proc sql outobs=12;
title 'U.S. Postal Codes';
select 'Postal code for', Name, 'is', Code
from sql.postalcodes;
Creating New Columns 27
Output 2.6 Adding Text to Output
To prevent the column headings Name and Code from printing, you can assign a label
that starts with a special character to each of the columns. PROC SQL does not write the
column name when a label is assigned, and it does not write labels that begin with
special characters. For example, you could use the following query to suppress the
column headings that PROC SQL displayed in the previous example:
libname sql 'SAS-library';
proc sql outobs=12;
title 'U.S. Postal Codes';
select 'Postal code for', Name label='#', 'is', Code label='#'
from sql.postalcodes;
28 Chapter 2 Retrieving Data from a Single Table
Output 2.7 Suppressing Column Headings in Output
Calculating Values
You can perform calculations with values that you retrieve from numeric columns. The
following example converts temperatures in the Sql.WorldTemps table from Fahrenheit
to Celsius:
libname sql 'SAS-library';
proc sql outobs=12;
title 'Low Temperatures in Celsius';
select City, (AvgLow - 32) * 5/9 format=4.1
from sql.worldtemps;
Note: This example uses the FORMAT attribute to modify the format of the calculated
output. For more information, see “Specifying Column Attributes” on page 36.
Creating New Columns 29
Output 2.8 Calculating Values
Assigning a Column Alias
By specifying a column alias, you can assign a new name to any column within a PROC
SQL query. The new name must follow the rules for SAS names. The name persists only
for that query.
When you use an alias to name a column, you can use the alias to reference the column
later in the query. PROC SQL uses the alias as the column heading in output. The
following example assigns an alias of LowCelsius to the calculated column from the
previous example:
libname sql 'SAS-library';
proc sql outobs=12;
title 'Low Temperatures in Celsius';
select City, (AvgLow - 32) * 5/9 as LowCelsius format=4.1
from sql.worldtemps;
30 Chapter 2 Retrieving Data from a Single Table
Output 2.9 Assigning a Column Alias to a Calculated Column
Referring to a Calculated Column by Alias
When you use a column alias to refer to a calculated value, you must use the
CALCULATED keyword with the alias to inform PROC SQL that the value is
calculated within the query. The following example uses two calculated values, LowC
and HighC, to calculate a third value, Range:
libname sql 'SAS-library';
proc sql outobs=12;
title 'Range of High and Low Temperatures in Celsius';
select City, (AvgHigh - 32) * 5/9 as HighC format=5.1,
(AvgLow - 32) * 5/9 as LowC format=5.1,
(calculated HighC - calculated LowC)
as Range format=4.1
from sql.worldtemps;
Note: You can use an alias to refer to a calculated column in a SELECT clause, a
WHERE clause, or ORDER BY clause.
Creating New Columns 31

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required