The SQL Procedure

column-modifier

Sets column attributes.

See also:

column-definition and SELECT Clause

Featured in:

Creating a Table and Inserting Data into It

Creating a Table from a Query's Result

column-modifier

Arguments

column-modifier

is one of the following:

INFORMAT=informatw.d

specifies a SAS informat to be used when SAS accesses data from a table or view. You can change one permanent informat to another by using the ALTER statement. PROC SQL stores informats in its table definitions so that other SAS procedures and the DATA step can use this information when they reference tables created by PROC SQL.

See SAS Language Reference: Dictionary for more information about informats.

FORMAT=formatw.d

specifies a SAS format for determining how character and numeric values in a column are displayed by the query-expression. If the FORMAT= modifier is used in the ALTER, CREATE TABLE, or CREATE VIEW statements, then it specifies the permanent format to be used when SAS displays data from that table or view. You can change one permanent format to another by using the ALTER statement.

See SAS Language Reference: Dictionary for more information about formats.

LABEL='label'

specifies a column label. If the LABEL= modifier is used in the ALTER, CREATE TABLE, or CREATE VIEW statements, then it specifies the permanent label to be used when displaying that column. You can change one permanent label to another by using the ALTER statement.

A label can begin with the following characters: a through z, A through Z, 0 through 9, an underscore (_), or a blank space. If you begin a label with any other character, such as pound sign (#), then that character is used as a split character and it splits the label onto the next line wherever it appears. For example:

select dropout label=
'#Percentage of#Students Who#Dropped Out'
   from educ(obs=5);

If a special character must appear as the first character in the output, then precede it with a space or a forward slash (/).

You can omit the LABEL= part of the column-modifier and still specify a label. Be sure to enclose the label in quotation marks, as in this example:

select empname "Names of Employees"
   from sql.employees;

If an apostrophe must appear in the label, then type it twice so that SAS reads the apostrophe as a literal. Alternatively, you can use single and double quotation marks alternately (for example, "Date Rec'd").

LENGTH=length

specifies the length of the column. This column modifier is valid only in the context of a SELECT statement.

TRANSCODE=YES|NO

for character columns, specifies whether values can be transcoded. Use TRANSCODE=NO to suppress transcoding. Note that when you create a table by using the CREATE TABLE AS statement, the transcoding attribute for a given character column in the created table is the same as it is in the source table unless you change it with the TRANSCODE= column modifier. For more information about transcoding, see SAS National Language Support (NLS): Reference Guide.

Default:	YES
Restriction:	The TRANSCODE=NO argument is not supported by some SAS Workspace Server clients. In SAS 9.2, if the argument is not supported, column values with TRANSCODE=NO are replaced (masked) with asterisks (*). Before SAS 9.2, column values with TRANSCODE=NO were transcoded.
Restriction:	Suppression of transcoding is not supported for the V6TAPE engine.
Interaction:	If the TRANSCODE= attribute is set to NO for any character variable in a table, then PROC CONTENTS prints a transcode column that contains the TRANSCODE= value for each variable in the data set. If all variables in the table are set to the default TRANSCODE= value (YES), then no transcode column is printed.

Details

If you refer to a labeled column in the ORDER BY or GROUP BY clause, then you must use either the column name (not its label), the column's alias, or its ordering integer (for example, ORDER BY 2 ). See the section on SAS statements in SAS Language Reference: Dictionary for more information about labels.

Top of Page