BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lydiawawa
Lapis Lazuli | Level 10

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

3 REPLIES 3
Patrick
Opal | Level 21

@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;
lydiawawa
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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