DATA Step, Macro, Functions and more

How to transpose this dataset into long form?

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

How to transpose this dataset into long form?

[ Edited ]

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

    Front   Middle  Rear
 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

 

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;

 

Any help appreciated!


Accepted Solutions
Solution
2 weeks ago
Respected Advisor
Posts: 4,702

Re: How to transpose this dataset into long form?

Posted in reply to lydiawawa

@lydiawawa

Using a SAS data step with array processing makes it normally quite simple to transpose data structures from wide to long.

data have;
  infile datalines dlm=' ' truncover;
  input Height $ Front Middle Rear;
  datalines;
 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;

data want(keep=height location price);
  set have;
  array loc {*} Front Middle Rear;

  do _i=1 to dim(loc);
    Location=vname(loc[_i]);
    Price=loc[_i];
    output;
  end;
run;

View solution in original post


All Replies
Solution
2 weeks ago
Respected Advisor
Posts: 4,702

Re: How to transpose this dataset into long form?

Posted in reply to lydiawawa

@lydiawawa

Using a SAS data step with array processing makes it normally quite simple to transpose data structures from wide to long.

data have;
  infile datalines dlm=' ' truncover;
  input Height $ Front Middle Rear;
  datalines;
 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;

data want(keep=height location price);
  set have;
  array loc {*} Front Middle Rear;

  do _i=1 to dim(loc);
    Location=vname(loc[_i]);
    Price=loc[_i];
    output;
  end;
run;
New Contributor
Posts: 3

Re: How to transpose this dataset into long form?

Thank you for the tips! It is a very smart way to convert transpose into a loop. 

Super User
Super User
Posts: 7,948

Re: How to transpose this dataset into long form?

[ Edited ]
Posted in reply to lydiawawa

Just keep tell it to keep HEIGHT in the dataset.  Include it in the BY statement.

proc transpose data=bookp
  out=bookplong (rename=(_name_=Location col1=Price));
  by dex height ;
  var front middle rear ;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 135 views
  • 3 likes
  • 3 in conversation