CODEWITHSUNDEEP
sastranspose
alex
alex

Reputation: 844

Reshape SAS dataset

If I have a dataset structured like this:


year  quarter var_1  var_2  var_3 
2000    1      5      8      1
2000    2      3      11     5
2000    3      8      2      10

How could I reshape it to this:


year  quarter  variable   value
2000    1        var_1     5
2000    2        var_1     3
2000    3        var_1     8
2000    1        var_2     8
2000    2        var_2     11
2000    3        var_2     2
2000    1        var_3     1
2000    2        var_3     5
2000    3        var_3     10

I've tried some different things with proc transpose but can't figure it out exactly.

Upvotes: 0

Views: 32

Answers (2)

Stu Sztukowski
Stu Sztukowski

Reputation: 12849

Transpose by year quarter.

proc transpose data = have2
               out  = want(rename=(COL1=value) )
               name = variable
               ;
    by row year quarter;
    var var:;
run;

year    quarter variable    value
2000    1       var_1       5  
2000    1       var_2       8
2000    1       var_3       1
2000    2       var_1       3
2000    2       var_2       11
2000    2       var_3       5
2000    3       var_1       8
2000    3       var_2       2
2000    3       var_3       10

Upvotes: 1

Tom
Tom

Reputation: 51566

This looks like another direct application of PROC TRANSPOSE, like your previous question. This time with a BY statement.

data have;
  input year  quarter var_1-var_3 ;
cards;
2000    1      5      8      1
2000    2      3      11     5
2000    3      8      2      10
;

proc transpose data=have out=want;
  by year quarter;
  var var_1-var_3;
run;

Results

Obs    year    quarter    _NAME_    COL1

 1     2000       1       var_1       5
 2     2000       1       var_2       8
 3     2000       1       var_3       1
 4     2000       2       var_1       3
 5     2000       2       var_2      11
 6     2000       2       var_3       5
 7     2000       3       var_1       8
 8     2000       3       var_2       2
 9     2000       3       var_3      10

To change the variable names you can use dataset options.

proc transpose data=have out=want(rename=(_name_=variable col1=value));
  by year quarter;
  var var_1-var_3;
run;

Upvotes: 1

Related Questions