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!
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;
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;
Thank you for the tips! It is a very smart way to convert transpose into a loop.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.