CODEWITHSUNDEEP
sastransposeproc
lydias
lydias

Reputation: 841

SAS transpose data into long form

I have the following dataset and hoping to transpose it into long form: 

data have ;
  input Height $ Front Middle Rear ;
  cards;
  Low    125 185 126
  Low    143 170 136
  Low    150 170 129
  Low    138 195 136
  Low    149 162 147
  Medium 141 176 128
  Medium 137 161 133
  Medium 145 167 148
  Medium 150 165 145
  Medium 130 184 141
  High   129 157 149
  High   141 152 137
  High   148 186 138
  High   130 164 126
  High   137 176 138
  ;
run;

Here Height is low, medium and high. Location is front, middle and rear. Numerical values are prices by location and height of a book on a bookshelf.

I'm hoping to transpose the dataset into long form with columns:

Height, Location and Price

The following code only allows me to transpose Location into long form. How should I transpose Height at the same time?

data bookp;
set bookp;
dex = _n_; 
run;

proc sort data=bookp;
by dex;
run;

proc transpose data=bookp
out=bookpLong (rename=(col1=price _name_= location )drop= _label_ dex);
var front middle rear;
by dex; 
run;

Upvotes: 0

Views: 54

Answers (1)

Tom
Tom

Reputation: 51566

I think you just need to include HEIGHT in the BY statement.

First let's convert your example data into a SAS dataset.

data have ;
  input Height $ Front Middle Rear ;
cards;
 Low    125 185 126
 Low    143 170 136
 Medium 141 176 128
 Medium 137 161 133
 High   129 157 149
 High   141 152 137
;

Now let's add an identifier to uniquely identify each row. Note that if you are really reading the data with a data step you could do this in the same step that reads the data.

data with_id ;
  row_num+1;
  set have;
run;

Now we can transpose.

proc transpose data=with_id out=want (rename=(_name_=Location col1=Price));
  by row_num height ;
  var front middle rear ;
run;

Results:

Obs    row_num    Height    Location    Price

  1       1       Low        Front       125
  2       1       Low        Middle      185
  3       1       Low        Rear        126
  4       2       Low        Front       143
  5       2       Low        Middle      170
  6       2       Low        Rear        136
  7       3       Medium     Front       141
  8       3       Medium     Middle      176
  9       3       Medium     Rear        128
 10       4       Medium     Front       137
 11       4       Medium     Middle      161
 12       4       Medium     Rear        133
 13       5       High       Front       129
 14       5       High       Middle      157
 15       5       High       Rear        149
 16       6       High       Front       141
 17       6       High       Middle      152
 18       6       High       Rear        137

Upvotes: 1

Related Questions