← SASnRTranspose data - wide to long

Introduction

The reverse of long-to-wide is wide-to-long — taking one row per subject with many measurement columns and stacking those columns into rows.
Long shape is what analytic tools usually want: one row per subject per measurement, with a column naming the measurement and another holding the value.
SAS uses proc transpose with by, var, and the name=/rename= options to control the new column names.
Tidyverse uses pivot_longer() from tidyr, specifying the columns to stack and the names of the new key/value columns.
Base R uses reshape() with direction = "long", providing varying, v.names, timevar, times, and idvar.
This lesson takes a WIDE dataset with one row per subject and columns HGB, ALT, AST, and pivots it into a LONG dataset with lbtestcd and lbstresn.

Transpose data - wide to long

SAS (Base SAS)+

data WIDE;
 infile datalines dlm='|' dsd missover;
input usubjid : $4. HGB : best32. ALT : best32. AST : best32.;
label ;
 format ;
 datalines4;
1001|13|30|23
1002|12|28|15
;;;;
 run;

proc sort data=wide;
     by usubjid;
 run;

proc transpose data=wide
out=long(rename=(col1=lbstresn))
name=lbtestcd ;
    by usubjid;
    var HGB ALT AST;
 run;

PROC TRANSPOSE works in both directions — for wide-to-long the pattern uses var to list the columns to stack and name= / rename= to control the new column names.
Sort first: proc sort data=wide; by usubjid; — PROC TRANSPOSE expects BY variables to be sorted.
proc transpose data=wide out=long(rename=(col1=lbstresn)) name=lbtestcd; names the input and the output, and tells SAS how to label the new key/value columns.
name=lbtestcd renames the automatic "_NAME_" column — the one that holds the old column names — to lbtestcd.
rename=(col1=lbstresn) renames the automatic COL1 column (the one that holds the stacked values) to lbstresn.
by usubjid; keeps the subject identifier fixed across the stacked rows, so each subject appears once per lab test.
var HGB ALT AST; names the columns whose values we want to stack into the long layout.
After running, LONG has one row per usubjid per lab test, with columns usubjid, lbtestcd, and lbstresn.

R (tidyverse)+

wide<-tribble( 
~usubjid,~HGB,~ALT,~AST, 
"1001",13,30,23, 
"1002",12,28,15, 
) 

long<-pivot_longer(wide, 
cols=c(HGB,ALT,AST), 
names_to = "lbtestcd", 
values_to = "lbstresn"
)

In tidyverse, pivot_longer() from tidyr is the direct counterpart of PROC TRANSPOSE for wide-to-long reshaping.
cols = c(HGB, ALT, AST) names the columns to stack; the remaining columns stay fixed across the stacked rows.
names_to = "lbtestcd" names the new column that will hold the old column names — HGB, ALT, AST become values in lbtestcd.
values_to = "lbstresn" names the new column that will hold the values from the selected columns.
For columns with a naming pattern, pivot_longer() also accepts names_pattern or names_sep to split a single name into multiple key columns.
We run and compare: LONG should have one row per usubjid per lab test, matching the SAS PROC TRANSPOSE output row for row.

Happy learning!

Search lessons

Introduction

Transpose data - wide to long