In the previous section, we compared actual revenue values to target revenue values using an array as a lookup table. Remember the following:

Using arrays was a good solution because the orientation of the data sets differed. An alternate solution is to transpose Sasuser.Ctargets using PROC TRANSPOSE, and then merge the transposed data set with Sasuser.Monthsum by the values of Year and Month.

The TRANSPOSE procedure creates an output data set by restructuring the values in a SAS data set. When the data set is restructured, selected variables are transposed into observations. The TRANSPOSE procedure can often eliminate the need to write a lengthy DATA step to achieve the same result. The output data set can be used in subsequent DATA or PROC steps for analysis, reporting, or further data manipulation.

PROC TRANSPOSE does not print the output data set. Use PROC PRINT, PROC REPORT, or some other SAS reporting tool to print the output data set.

The following program transposes the SAS data set Sasuser.Ctargets. The OUT= option specifies the name of the output data set, Work.Ctarget2. All of the variables in Sasuser.Ctargets are transposed because all of the variables are numeric and a VAR statement is not used in the program.

proc transpose data=sasuser.ctargets
     out=work.ctarget2;
run;

Notice that in the output data set, the variables are named _NAME_, COL1, COL2, and COL3.

_NAME_ is the default name of the variable that PROC TRANSPOSE creates to identify the source of the values in each observation in the output data set. This variable is a character variable whose values are the names of the variables that are transposed from the input data set. For example, in Work.Ctarget2 the values in the first observation in the output data set come from the values of the variable Year in the input data set.

The remaining transposed variables are named COL1...COLn by default. In Work.Ctarget2, the values of the variables COL1, COL2, and COL3 represent the target cargo revenue for each month in the years 1997, 1998, and 1999.

You can use PROC TRANSPOSE options to give the variables in the output data set more descriptive names. The NAME= option specifies a name for the _NAME_ variable.

The PREFIX= option specifies a prefix to use in constructing names for transposed variables in the output data set. For example, if PREFIX=Ctarget, the names of the variables are Ctarget1, Ctarget2, and Ctarget3.

proc transpose data=sasuser.ctargets 
     out=work.ctarget2
     name=Month
     prefix=Ctarget;
run;

proc transpose data=sasuser.ctargets 
     out=work.ctarget2 (rename=(col1=Ctarget1 
         col2=Ctarget2 col3=Ctarget3))
     name=Month;
run;

The default label for the _NAME_ variable is NAME OF FORMER VARIABLE. To see this, print the transposed data set using PROC PRINT with the LABEL option. You can use a LABEL statement to override the default label.

proc transpose data=sasuser.ctargets 
     out=work.ctarget2
     name=Month 
     prefix=Ctarget; 
run;
proc print data=work.ctarget2 label;
label Month=MONTH;
run;