TRANSPOSE Procedure
ID Statement
Specifies one or more variables in the input data
set whose formatted values name the transposed variables in the output
data set. When a variable name is being formed in the transposed
(output) data set, the formatted values of all listed ID variables
will be concatenated in the same order that the variables are listed
in the ID statement. The PREFIX=, DELIMITER=, and SUFFIX= options
can be used to modify the formed variable name. The PREFIX= option
specifies a common character or character string to appear at the
beginning of the formed variable names. The DELIMITER= option specifies
a common character or character string to be inserted between the
values of the ID variables. The SUFFIX= option specifies a common
character or character string to be appended to the end of each formed
variable name.
| 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. |
| Tip: | If the value of any ID variable is missing, then PROC TRANSPOSE writes a warning message to the log. The procedure does not transpose observations that have a missing value for any ID variable. |
| Example: | Naming Transposed Variables |
Syntax
ID variable(s);
Details
Duplicate Output Data Set Variable Names
A variable name formed from the
input data set ID variable values, combined with the PREFIX, DELIMITER,
and SUFFIX option values, should be unique within the output data
set. An output data set variable name that occurs more than once
indicates that two or more observations from the input data set are
transposed to a single variable in the output data set and the result
is data loss. This situation occurs when, in the case of a single
ID variable, duplicate formatted values occur within the input data
set or, if you use a BY statement, within a BY group. Similarly,
this situation occurs in the case of multiple ID variables when the
combination of formatted values of the ID variables occurs more than
once within the input data set or BY group. To prevent data loss,
if duplicate output data set variable names are formed, PROC TRANSPOSE
will issue a warning message about duplicate ID values and stop processing.
However, if the LET option is specified in the PROC TRANSPOSE statement
then the procedure will issue a warning message and continue processing,
transposing the observation containing the last occurrence of the
duplicate formatted variable values.
Note: The character substitutions
and truncation required to ensure that the variable name formed from
the ID variables combined with the PREFIX, DELIMITER, and SUFFIX option
values can cause duplicate output data set variable names in cases
where the formatted value of the ID variable or combination of ID
variables is unique within the input data set.
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.
SAS variable names
cannot begin with a number. When the first character of the formatted
value is numeric, the procedure prefixes an underscore to the value,
this action truncates the last character of a 32-character value.
Remaining invalid characters are replaced by underscores. The procedure
truncates to 32 characters any ID value that is longer than 32 characters
when the procedure uses that value to name a transposed variable.
Making Variable Names Out of Multiple ID Variables
When you specify a single
ID variable, in forming an output data set variable name, the formatted
values of the variable are made to conform with the SAS variable naming
conventions imposed by the VALIDVARNAME option. The name formed by
combining the ID variable values with the value of the PREFIX and
SUFFIX options are also made to conform with the SAS variable naming
convention. For both the formatted ID variable values and their combination
with the PREFIX and SUFFIX options, invalid characters will be replaced
with underscores or, if the name appears to be a numeric constant,
an underscore will be used as a prefix and the characters +, -, and
. will be changed to P, N, and D. The resulting name will be truncated
to the maximum name length allowed by the VALIDVARNAME option setting.
When you specify multiple ID variables, conformance to the SAS variable
naming convention will be imposed on the components of the variable
name, using the formatted value of each ID variable, and also on the
name composed from the ID variable values and the PREFIX, DELIMITER,
and SUFFIX options. The resulting name will be truncated to a length
appropriate for the VALIDVARNAME option setting.