Wednesday, December 1, 2010

reshape data from wide to long using data steps and using array

Suppose there is data like

data wide;
  input a $ var1 var2 var3 var4;
  cards;
  a1 1 2 3 4
  a2 10 20 30 40
  a3 5 6 7 8
  a4 50 60 70 80
  ;
  run;

If print, the data looks like (wide)
                              Obs    a     var1    var2    var3    var4
                               1     a1      1       2       3       4
                               2     a2     10      20      30      40
                               3     a3      5       6       7       8
                               4     a4     50      60      70      80

Now you want to reshape from wide to long like the following (e.g., you want to use proc mixed which doesn't support multi-var responsible data )
                                           Obs    a     var
                                             1    a1      1
                                             2    a1      2
                                             3    a1      3
                                             4    a1      4
                                             5    a2     10
                                             6    a2     20
                                             7    a2     30
                                             8    a2     40
                                             9    a3      5
                                            10    a3      6
                                            11    a3      7
                                            12    a3      8
                                            13    a4     50
                                            14    a4     60
                                            15    a4     70
                                            16    a4     80

You need to use (data steps)

data long;
   set wide;
   var=var1; output;
   var=var2; output;
   var=var3; output;
   var=var4; output;
   drop var1-var4;
run;

It's clear from the data step we repeat the same action to the different variables, so we can use array to fulfill this job like

data long1;
   set wide;
   array along(4) var1-var4;
   do i=1 to 4;
      var=along[i];
      output;
    end;
    drop var1-var4;
run;

We can get the same result.

No comments:

Post a Comment