Proc Transpose was never simpler before

Let's try to learn Proc Transpose in Conjunction with Excel Pivot.

Both Pivot table and Proc Transpose are quite similar in functionality with few differences. Let's not dwell much into difference; learning Proc Transpose in analogy with Pivot would make it easier to visualize and difficult to forget in future.

Shall we begin ?



Please download the following data >>     Result.csv  

Now let's import the same in SAS. Don't forget to change location as per file location in your PC.

Proc Import datafile = "location\Result.csv"
out = Result
dbms = csv replace;
Run;

Let's now open the Result.csv file, select the data and create a pivot table on it.

In Excel pivot table we get 4 blocks, in which we drop variables :

Report Filter        -     For filtering data

Row Labels         -     What we want to keep in left

Column Labels    -     What column we want to create

Values                 -     The numeric value we summarize

Suppose we want to do following transformation of data:

We do the following selection in Pivot section :

Suppose same thing we want to do in Proc Transpose, we write :

proc Sort data = Result ; By Student Class; Run;

Proc transpose data = Result out = Result_Trans ;

By Student Class;

ID subject ; 

Var Marks;

Run;

Basically in Proc Transpose, we use fields in :

By :  What we want to keep in left
ID :  What column we want to create
Var : The value we summarize

Whichever variables we take in By statement in Proc Transpose (or any other procedure of SAS), we first need to sort the data on those variables in same order (ascending/descending).

So If we draw Analogy, we can say

Proc Transpose                               Pivot
     BY                     =                     Row Label
     ID                       =                  Column Label
    Var                      =                        Values

Also, we can filter the data in proc Transpose, using a where statement. In Pivot we can do it across Row Lables, Column Labels, Report Filter .... using filter.

proc Sort data = Result ; By Student Class; Run;

Proc transpose data = Result out = Result_Trans ;

By Student Class;

ID subject ; 

Var Marks;

where student ~= "Geeta";

Run;

Little more about Proc Transpose

For understanding Prefix, try below code

proc Sort data = Result ; By Student subject; Run;

Proc transpose data = Result out = Result_Trans  prefix = Class_;

By Student subject;

ID class ; 

Var Marks;

Run;

As we are using a numeric variable in ID and  SAS name can't start with number, SAS automatically add "_" as a prefix with it. However, if we want to customize it we can do it using Prefix.


Enjoy reading our other articles and stay tuned with ....


Kindly do provide your feedback in the 'Comments' Section and share as much as possible.