SAS : PROC TRANSPOSE with Examples

PROC TRANSPOSE helps to reshape data in SAS. This tutorial explains the basic and intermediate applications of PROC TRANSPOSE with examples. It's a very powerful procedure when you need to change the shape of the data. For example, you have data in vertical (long) format and you are asked to change it to horizontal (wide) format. It can be done via data step as well but it would be a complex code which takes a lot of time to write and test it. To save programming time and maintaining the accuracy of the code, we should use TRANSPOSE procedure to restructure data.
Transpose Data with PROC TRANSPOSE
Example Data Set

Let's create sample data which is used for explaining the TRANSPOSE procedure.
Suppose you have data for students with their marks in respective subjects. In the data set, you have three variables 'Name', 'Subject' and 'Marks'. See the table below showing this data.

Name Subject Marks
Samma Maths 96
Sandy English 76
Devesh German 76
Rakesh Maths 50
Priya English 62
Kranti Maths 92
William German 87

Create data set in SAS

To see this data in SAS data set format, run the following code -
data transp;
input Name $ Subject $ Marks;
cards;
Samma Maths 96
Sandy English 76
Devesh German 76
Rakesh Maths 50
Priya English 62
Kranti Maths 92
William German 87
;
run;
It creates a data set named 'TRANSP' which is stored in WORK library.

Run Simplest Form of PROC TRANSPOSE
proc transpose data = transp out= outdata;
run;
The above code creates a data set called outdata which contains values of variable 'Marks' stored in horizontal (wide) format. In other words, it transposes only variable i.e. Marks (which is numeric). It is because by default, PROC TRANSPOSE transposes all numeric variables in the data set.

Output Data Set

The output of the data set looks like below -
_NAME_ COL1 COL2 COL3 COL4 COL5 COL6 COL7
Marks 96 76 76 50 62 92 87


Options in PROC TRANSPOSE

1. The NAME= option allows you to change the name of the _NAME_ variable. It is the name of the variable that is transposed.
2. The PREFIX= option allows you to change the prefix "COL". It is prefix to the transposed values.
proc transpose data = transp name=VarName prefix=Student out= outdata;
run;
Observe the above code with the previous section code - There are two changes in the code above that are : specifying name 'VarName' to the variable Name. The other change is adding a prefix 'Student' to the transposed marks.

Output Data Set
VarName Student1 Student2 Student3 Student4 Student5 Student6 Student7
Marks 96 76 76 50 62 92 87

Statements in PROC TRANSPOSE

1. ID - [Move to Column Name] It allows you to include the values of a variable as variable names in the output data set. In other words, it tells SAS to give the variable names in the output file which were observations (rows) values in a variable in the input data set. If the variable in the ID statement is numeric, an underscore will be put by default at the beginning of the variable name. Instead of a default '_', you can use PREFIX= option to give a specific prefix which can be any character value. For example, you want to add 'Height' as a prefix which would create variables like 'Height20' 'Height 30'.

2. BY - It allows you to transpose data within the combination of the BY variables. The BY variables themselves aren’t transposed. The variables need to be sorted before running PROC TRANSPOSE. You can sort the variables with PROC SORT.

3. VAR - [Transpose Column] It lists the actual data that needs to be transposed. If you do not include a VAR statement, the procedure will transpose all numeric variables that are not included in a BY statement or a ID statement. If you want to transpose a character variable, a VAR statement is required.


Example 2 : Give name to transposed columns

Suppose you want to have actual students' name instead of 'Student1 Student2 etc' in the variable names. You can use ID statement to accomplish this task. Check out the code below -
proc transpose data = transp name=VarName out= outdata;
id name;
run;
Output Data Set
VarName Samma Sandy Devesh Rakesh Priya Kranti William
Marks 96 76 76 50 62 92 87

In this case, the variable 'name' is used for naming variables.

Example 3 : Restructure Data

Suppose you want to change the structure of data in the manner in which the row values of the variable 'Subjects' come at top i.e. heading / variable names and marks under the respective column in the output dataset.

In this case, we need to sort the data as we are going to use BY processing in PROC TRANSPOSE.
proc sort data = transp;
by Name;
run;
proc transpose data = transp out= outdata;
by Name;
id Subject;
var Marks;
run;

In this example, we are specifying variable 'Name' in the BY option which means we do not want to transpose this variable.. The variable 'Marks' specified in the VAR option implies this variable is actually transposed and shape of the data format would be changed in the output data set.
Output : PROC TRANSPOSE Tutorial
If you look at the output above, everything looks perfect except the variable '_NAME' which is not relevant. We can eliminate this variable with DROP= option.
proc transpose data = transp out= outdata (drop=_name_);
by Name;
id Subject;
var Marks;
run;

Is SORTING required when i use BY statement? 

Answer is No. The NOTSORTED option tells SAS that data is not sorted and it is not required to sort it. If you don't specify NOTSORTED option, you need to sort the variable that is listed in BY statement.
proc transpose data = transp out= outdata (drop=_name_) ;
by Name NOTSORTED;
id Subject;
var Marks;
run;

Output Data Set
Name Maths English German
Samma 96 . .
Sandy . 76 .
Devesh . . 76
Rakesh 50 . .
Priya . 62 .
Kranti 92 . .
William . . 87

See the above output. You must have observed the names are not sorted in the output data set.

How to use Two Variables in ID Statement

We can use DELIMITER= option to separate values of two variables specified in the ID statements. In this example, we have used underscore ( _ ) as a delimiter.
proc transpose data = transp delimiter=_ name=VarName out= outdata;
id name subject;
run;

VarName Samma_Maths Sandy_English Devesh_German Rakesh_Maths
Marks 96 76 76 50


How to label the Output Variables with PROC TRANSPOSE

The ID statement tells SAS to provide variable names to the variables after the transpose. But if you want to label these variables, you can use IDLABEL statement which picks labels from a variable from the input file.
 proc transpose data=temp out=outdata prefix=height;
  by id;
  var scores;
  id height;
  idlabel heightl;
  run;

Practice Example

Suppose you have monthly financial data. You need to convert long formatted data to wide format.

ID Months Revenue Balance
101 1 3 90
101 2 33 68
101 3 22 51
102 1 100 18
102 2 58 62
102 3 95 97

SAS Code 
data example;
input ID Months Revenue Balance;
cards;
101 1 3 90
101 2 33 68
101 3 22 51
102 1 100 18
102 2 58 62
102 3 95 97
;
Output

The final output should like the following table.

ID balance_1 balance_2 balance_3
101 90 68 51
102 18 62 97

Solution :
proc transpose data=example out= Output1 (drop = _NAME_) prefix=balance_;
id months;
var balance;
by ID;
run;
In this case, the variable 'Month' specified in ID statement is a numeric variable. Hence, we have added prefix 'balance_' to make it to the desired output.

If you want to see your output looks like the data shown in the image below -
PROC TRANSPOSE : Reshape Data
proc transpose data=example1 out=out1 name=variable prefix=x;
by id month;
run;
In this case, the information of the 'Revenue' and 'Balance' variables are stacked to one variable. And the variable 'x1' refers to the values corresponding to it.

Related Tutorial : Transpose Multiple Variables

SAS Tutorials : 100 Free SAS Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

4 Responses to "SAS : PROC TRANSPOSE with Examples"

  1. what is use of let option in transpose process

    ReplyDelete
  2. proc transpose data = transp delimiter=_ name=VarName out= outdata;
    id name subject;
    run;


    its not working

    ReplyDelete
    Replies
    1. proc transpose data=transp delimiter=_ name=varname out=outdata;
      id name subject;
      run;

      Delete

Next → ← Prev