Solved
New Contributor
Posts: 3

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
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;``````

All Replies
Solution
2 weeks ago
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
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