SAS : Transpose Multiple Variables

This tutorial explains how to reshape data by converting multiple variables from long format to wide format. This tutorial explores 'Double Transpose' SAS method which is used to transpose multiple variables. It's a very handy technique to solve this challenge. Otherwise, it would be a very time consuming task. This post includes a sample data set which is used for demonstrating the method for transposing more than one variable.

Create a sample data set

The code below creates a data set named 'temp' which constitutes five variables - ID, TIME, X1, X2, X3.
data temp;
input ID time $ x1-x3;
cards;
1 Y1 85 85 86
1 Y2 80 79 70
1 Y3 78 77 87
2 Y1 79 79 79
2 Y2 83 83 85
;
run; 
SAS : Reshape Data

We want to see our output look like the table shown in the image below -
SAS : Transpose Variables
Solution :
proc sort data=temp;
by ID time;
run;

proc transpose data=temp out=out1;
by ID time;
var x1-x3;
run;
proc transpose data=out1 delimiter=_ out=new2(drop=_name_);
by ID;
var col1;
id _name_ time;
run;
How it works :

1. First step, it is required to sort the variables 'ID' 'time' before using them in BY statement in PROC TRANSPOSE. It is done with PROC SORT.

2. In the first transpose of the above code, we are telling SAS to store information of all the variables in a single variable and the respective values in the another variable. And we do not want to transpose variables ID and Time. Hence, we have specified them in BY statement. See the following output generated in this step -

ID Time _NAME_ COL1
1 Y1 x1 85
1 Y1 x2 85
1 Y1 x3 86
1 Y2 x1 80
1 Y2 x2 79
1 Y2 x3 70
1 Y3 x1 78
1 Y3 x2 77
1 Y3 x3 87
2 Y1 x1 79
2 Y1 x2 79
2 Y1 x3 79
2 Y2 x1 83
2 Y2 x2 83
2 Y2 x3 85

3. Second transpose further reshapes the data from long to wide format. It generates the desired output. The delimiter= option is used to place a separator that separates values of two ID variables.
Related Posts
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.

10 Responses to "SAS : Transpose Multiple Variables"
  1. thanks for your information

    ReplyDelete
  2. plz provide more examples.....Tx..

    ReplyDelete
  3. Hi, when I run your last example code above, I got error messages:
    proc transpose data=out1 delimiter=_ out=new2(drop=_name_);
    ---------
    22
    76
    ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA, LABEL, LET, NAME, OUT,
    PREFIX.
    ERROR 76-322: Syntax error, statement will be ignored.
    328 by ID;
    329 var col1;
    330 id _name_ time;
    -----
    22 200
    ERROR 22-322: Expecting ;.
    ERROR 200-322: The symbol is not recognized and will be ignored.
    331 run;

    NOTE: The SAS System stopped processing this step because of errors.
    NOTE: PROCEDURE TRANSPOSE used (Total process time):
    real time 0.05 seconds
    cpu time 0.04 seconds

    Why is that? Thank you very much for your help!

    ReplyDelete
  4. I Have transaction dataset in which I have a column of expenses I want to keep all transactions side by side using comma based on the account id wise.Below i have mentioned small scenario of the one.

    Acct_Id gender expenses
    101 M 20000
    102 F 20000
    103 F 50000
    101 M 10000
    103 F 18000
    102 F 21000
    102 F 11000
    103 F 49000
    101 M 20000
    I want all expenses in one column side by side using deimeter as comaa, I want it as below in SAS, Can anyone please assist me in doing this will be a great help for me.

    101 M 20000,10000,20000
    102 F 20000,21000,11000
    103 F 50000,18000,49000
    Thanks and regards,
    Swarupa

    ReplyDelete
    Replies
    1. First sort d data by id then
      Proc transpose data=example out=example1(drop=_name_) prefix=expenses;
      Var expenses;
      By acct_id gender;
      Run;
      Then use catx
      Data ex2(drop=expenses expenses2 expenses 3);
      Set example1;
      Expenses=catx(',',expenses1,expenses,expenses);
      Run;

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Hello,
    I have this dataset:

    CONTRACT IND MONTH1 MONTH2 MONTH3
    1 100 10 20 30
    1 200 30 10 10
    2 100 20 20 20
    2 300 10 20 30

    I need this dataset:

    CONTRACT MONTH IND100 IND200 IND300
    1 month1 10 30 0
    1 month2 20 10 0
    1 month3 30 10 0
    2 month1 20 0 10
    2 month2 20 0 20
    2 month3 20 0 30

    Can you help me?
    Thank you

    ReplyDelete
    Replies
    1. data mydata;
      input CONTRACT IND MONTH1 MONTH2 MONTH3;
      cards;
      1 100 10 20 30
      1 200 30 10 10
      2 100 20 20 20
      2 300 10 20 30
      ;
      run;

      proc transpose data=mydata prefix=IND out=out1(rename=(_name_=Month));;
      by CONTRACT;
      var MONTH1-MONTH3;
      id IND;
      run;

      Delete
    2. In case you want zeros instead of missing values in PROC TRANSPOSE output, you can add the following program after running proc transpose.

      data out1;
      set out1;
      array replace _numeric_;
      do over replace;
      if replace=. then replace=0;
      end;
      run;

      Delete
  7. Thank you very much for the answer.
    I have read in several sites that the proc transpose is a very slow procedure for large data files that is my case. There is some other option in SAS code to do it.

    Regards,
    natàlia

    ReplyDelete

Next → ← Prev
Love this Post? Spread the Word!
Share