SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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 output

the column name when a label is assigned, and it does not output 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