Syntax: TRANSPOSE Procedure

Tip: Does not support the Output Delivery System

Reminder: You can use the ATTRIB, FORMAT, LABEL, and WHERE statements. See Chapter 3, Statements with the Same Function in Multiple Procedures, on page 57 for details. You can also use any global statements. See Global Statements on page 18 for a list.

PROC TRANSPOSE <DATA= input-data-set > <LABEL= label > <LET>

• • < NAME = name > <OUT= output-data-set > <PREFIX= prefix >;

• BY <DESCENDING> variable-1

  • • < <DESCENDING> variable-n >

    • <NOTSORTED>;

• COPY variable(s) ;

• ID variable ;

  • IDLABEL variable ;

• VAR variable(s) ;

To do this	Use this statement
Transpose each BY group	BY
Copy variables directly without transposing them	COPY
Specify a variable whose values name the transposed variables	ID
Create labels for the transposed variables	IDLABEL
List the variables to transpose	VAR

PROC TRANSPOSE Statement

Reminder: You can use data set options with the DATA= and OUT= options. See Data Set Options on page 18 for a list.

PROC TRANSPOSE <DATA= input-data-set > <LABEL= label > <LET>

• <NAME= name > <OUT= output-data-set > <PREFIX= prefix >;

Options

DATA= input-data-set

• names the SAS data set to transpose.

• Default: most recently created SAS data set

LABEL= label

• specifies a name for the variable in the output data set that contains the label of the variable that is being transposed to create the current observation.

• Default: _LABEL_

LET

• allows duplicate values of an ID variable. PROC TRANSPOSE transposes the observation that contains the last occurrence of a particular ID value within the data set or BY group.

• Featured in: Example 5 on page 1342

NAME= name

• specifies the name for the variable in the output data set that contains the name of the variable that is being transposed to create the current observation.

• Default: _NAME_

• Featured in: Example 2 on page 1337

OUT= output-data-set

• names the output data set. If output-data-set does not exist, then PROC TRANSPOSE creates it by using the DATA n naming convention.

• Default: DATA n

• Featured in: Example 1 on page 1335

PREFIX= prefix

• specifies a prefix to use in constructing names for transposed variables in the output data set. For example, if PREFIX=VAR, then the names of the variables are VAR1, VAR2, ,VAR n .

• Interaction: when you use PREFIX= with an ID statement, the value prefixes to the ID value.

• Featured in: Example 2 on page 1337

BY Statement

Defines BY groups.

Main discussion: BY on page 58

Featured in: Example 4 on page 1340

Restriction: You cannot use PROC TRANSPOSE with a BY statement or an ID statement with an engine that supports concurrent access if another user is updating the data set at the same time.

BY <DESCENDING> variable-1

• < <DESCENDING> variable-n >

• <NOTSORTED>;

Required Arguments

variable

• specifies the variable that PROC TRANSPOSE uses to form BY groups. You can specify more than one variable. If you do not use the NOTSORTED option in the BY statement, then either the observations must be sorted by all the variables that you specify, or they must be indexed appropriately. Variables in a BY statement are called BY variables .

Options

DESCENDING

• specifies that the data set is sorted in descending order by the variable that immediately follows the word DESCENDING in the BY statement.

NOTSORTED

• specifies that observations are not necessarily sorted in alphabetic or numeric order. The data is grouped in another way, such as chronological order.

  The requirement for ordering or indexing observations according to the values of BY variables is suspended for BY-group processing when you use the NOTSORTED option. In fact, the procedure does not use an index if you specify NOTSORTED. The procedure defines a BY group as a set of contiguous observations that have the same values for all BY variables. If observations with the same values for the BY variables are not contiguous, then the procedure treats each contiguous set as a separate BY group.

Transpositions with BY Groups

PROC TRANSPOSE does not transpose BY groups. Instead, for each BY group, PROC TRANSPOSE creates one observation for each variable that it transposes.

Figure 51.1 on page 1331 shows what happens when you transpose a data set with BY groups. TYPE is the BY variable, and SOLD, NOTSOLD, REPAIRED, and JUNKED are the variables to transpose.

Figure 51.1: Transposition with BY Groups

• The number of observations in the output data set (12) is the number of BY groups (3) multiplied by the number of variables that are transposed (4).

• The BY variable is not transposed.

• _NAME_ contains the name of the variable in the input data set that was transposed to create the current observation in the output data set. You can use the NAME= option to specify another name for the _NAME_ variable.

• The maximum number of observations in any BY group in the input data set is two; therefore, the output data set contains two variables, COL1 and COL2. COL1 and COL2 contain the values of SOLD, NOTSOLD, REPAIRED, and JUNKED.

Note: If a BY group in the input data set has more observations than other BY groups, then PROC TRANSPOSE assigns missing values in the output data set to the variables that have no corresponding input observations.

COPY Statement

Copies variables directly from the input data set to the output data set without transposing them.

Featured in: Example 6 on page 1343

COPY variable(s) ;

Required Argument

variable(s)

• names one or more variables that the COPY statement copies directly from the input data set to the output data set without transposing them.

Details

Because the COPY statement copies variables directly to the output data set, the number of observations in the output data set is equal to the number of observations in the input data set.

The procedure pads the output data set with missing values if the number of observations in the input data set is not equal to the number of variables that it transposes.

ID Statement

Specifies a variable in the input data set whose formatted values name the transposed variables in the output data set.

Featured in: Example 2 on page 1337

Restriction: You cannot use PROC TRANSPOSE with an ID statement or a BY statement with an engine that supports concurrent access if another user is updating the data set at the same time.

ID variable ;

Required Argument

variable

• names the variable whose formatted values name the transposed variables.

Duplicate ID Values

Typically, each formatted ID value occurs only once in the input data set or, if you use a BY statement, only once within a BY group. Duplicate values cause PROC TRANSPOSE to issue a warning message and stop. However, if you use the LET option in the PROC TRANSPOSE statement, then the procedure issues a warning message about duplicate ID values and transposes the observation that contains the last occurrence of the duplicate ID value.

Making Variable Names out of Numeric Values

When you use a numeric variable as an ID variable, PROC TRANSPOSE changes the formatted ID value into a valid SAS name.

However, SAS variable names cannot begin with a number. Thus, when the first character of the formatted value is numeric, the procedure prefixes an underscore to the value, truncating the last character of a 32-character value. Any remaining invalid characters are replaced by underscores. The procedure truncates to 32 characters any ID value that is longer than 32 characters when it uses that value to name a transposed variable.

If the formatted value looks like a numeric constant, then PROC TRANSPOSE changes the characters ˜+ , ˜ ˆ’ , and ˜. to ˜P , ˜N , and ˜D , respectively. If the formatted value has characters that are not numerics, then PROC TRANSPOSE changes the characters ˜+ , ˜ ˆ’ , and ˜. to underscores.

Note: If the value of the VALIDVARNAME system option is V6, then PROC TRANSPOSE truncates transposed variable names to eight characters.

Missing Values

If you use an ID variable that contains a missing value, then PROC TRANSPOSE writes an error message to the log. The procedure does not transpose observations that have a missing value for the ID variable.

IDLABEL Statement

Creates labels for the transposed variables.

Restriction: Must appear after an ID statement.

Featured in: Example 3 on page 1338

IDLABEL variable ;

Required Argument

variable

• names the variable whose values the procedure uses to label the variables that the ID statement names. variable can be character or numeric.

Note: To see the effect of the IDLABEL statement, print the output data set with the PRINT procedure by using the LABEL option, or print the contents of the output data set by using the CONTENTS statement in the DATASETS procedure.

VAR Statement

Lists the variables to transpose.

Featured in: Example 4 on page 1340 and Example 6 on page 1343

• VAR variable(s) ;

Required Argument

variable(s)

• names one or more variables to transpose.

Details

• If you omit the VAR statement, the then TRANSPOSE procedure transposes all numeric variables in the input data set that are not listed in another statement.

• You must list character variables in a VAR statement if you want to transpose them.