CODEWITHSUNDEEP
sas
Galadude
Galadude

Reputation: 253

Transposing wide to long in SAS, without extra columns

I'd like to transpose a dataset, but SAS insists on adding a new column, if the "by" column has multiple entries.

So if I run

data test;
    input a b $ c $ ; 
datalines;
1  aaa bbb
1  bbb bbb
2  ccc ccc
3  ccc ccc
;
run;

proc transpose data=test;
   by a;
   var b b;
run;

I get a table with two columns that looks like this:

1   b   aaa bbb
1   c   bbb bbb
2   b   ccc 
2   c   ccc 
3   b   ccc 
3   c   ccc

What I'd like with a table that looks like this:

1   b   aaa
1   c   bbb
1   b   bbb
1   c   bbb
2   b   ccc 
2   c   ccc 
3   b   ccc 
3   c   ccc

So instead of adding columns, for each entry, I want SAS to add rows. Any ideas on how to do this?

Just to be clear, this is a toy example! The dataset I'm working with has more columns.

Upvotes: 1

Views: 4805

Answers (3)

rambles
rambles

Reputation: 706

This ought to work (using your example code):

proc transpose data=test out=test_tran1(rename=(_name_ = old_var));
  by a;
  var b c;
run;

proc transpose data=test_tran1 out=test_tran2(drop=_: rename = (col1=values) where = (not missing(values)));
  by a old_var;
  var col:;
run;

Upvotes: 4

vasja
vasja

Reputation: 4792

data output;
set test;
array vars {*} b -- c; * define array composed of list of variables from B to C, have to be of same type;

length varname $32;
keep a varname value;

do i=1 to dim(vars);* loop array (list of variables);
    varname= vname(vars(i));* get name of variable that provided value;
    value = vars(i);* get the value of variable;
    output; *output row;
end;
run;

Upvotes: 0

Joe
Joe

Reputation: 63424

You can't use PROC TRANSPOSE in a single step with a 'mixed' dataset (multiple rows per by group AND multiple columns) to get long. Transpose only really works well going all one or the other.

Easiest way to get long is usually the data step.

data want;
 set test;
 array vars b c;
 do _i = 1 to dim(vars);
  varname = vname(vars[_i]);
  value   = vars[_i];
  output;
 end;
 keep a varname value;
run;

Upvotes: 2

Related Questions