Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 37 ยท Procedures: PROC

PROC TRANSPOSE and Reshaping Data

Reshape SAS data with PROC TRANSPOSE: BY, ID, VAR, PREFIX, NAME= and LABEL= options, convert wide to long and back, and compare with a DATA step ARRAY, with Python stand-ins.

Data comes in two shapes. Wide data has one row per subject and one column per measurement (score_jan, score_feb, score_mar). Long data has one row per measurement, with a column that says which one it is (month, score). Statistical procedures often want long data, while reports and spreadsheets are often wide. PROC TRANSPOSE converts between the two without writing a loop.

SAS cannot run on this site. The sas blocks are read-only examples. The TryIts are Python stand-ins that reshape small lists the same way, so you can predict what SAS produces.

Wide to long

Suppose work.wide has one row per student:

Text
student  jan  feb  mar
Ada       80   85   90
Ben       70   60   75

To get one row per student and month:

SAS
proc transpose data=work.wide out=work.long
               name=month prefix=score;
  by student;
  var jan feb mar;
run;

How it works:

  • var lists the columns to turn into rows.
  • by student repeats the transposition for each student, so the input must be sorted by student (or use notsorted for pre-grouped data).
  • Within each by group, the var columns become rows. Each row gets a variable holding the old column name, called _NAME_ unless you rename it with name=month.
  • The values go into new columns called COL1, COL2, ... unless you set prefix=score, which yields score1.

For this input the long result has six rows with columns student, month and score1, with month values jan, feb, mar. If your variables have labels, SAS also creates _LABEL_ unless you override with label=.

Stand-in: wide to long

Python Stand-in: PROC TRANSPOSE with BY and VAR

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every var column of each by group became a row, tagged with the former column name. That is the entire idea.

Long to wide

The reverse direction uses id to say which column supplies the new column names:

SAS
proc sort data=work.long out=work.long_sorted;
  by student;
run;

proc transpose data=work.long_sorted out=work.wide2 (drop=_name_);
  by student;
  id month;
  var score1;
run;

Now each student is one row, and the values of month (jan, feb, mar) become variable names, holding the matching score1 values. Things to watch:

  • ID values must be unique within each by group, or SAS stops with The ID value "jan" occurs twice in the same BY group. Add let to keep the last value instead, but only when that is really what you want.
  • ID values must become valid variable names. Values like 2024 or North America are not valid names under the default VALIDVARNAME=V7 rules; SAS then writes names such as _2024 or North_America. Use prefix= to make it explicit (prefix=m_ gives m_2024). The idlabel statement keeps the original text as the variable label.
  • Missing combinations simply give missing cells.

Stand-in: long to wide

Python Stand-in: ID and duplicate check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Ben's missing February shows as None, which SAS shows as .. The duplicate row triggers the same kind of failure that SAS reports.

Transposing without BY

Without a by statement, the whole data set is transposed as one block: each observation becomes a column (COL1, COL2, ...) and each var variable becomes a row. This is handy for printing a tiny table sideways:

SAS
proc transpose data=work.totals out=work.totals_t;
  var sales profit;
run;

With many observations that creates thousands of columns, so reserve it for small summary tables.

The DATA step alternative

For wide to long, a DATA step with an array and output gives you more control, for example to skip missing values or calculate something while reshaping:

SAS
data work.long2;
  set work.wide;
  array m {3} jan feb mar;
  do i = 1 to 3;
    month = vname(m{i});
    score = m{i};
    if not missing(score) then output;
  end;
  keep student month score;
run;

vname() returns the name of the array element's variable, output writes one row per loop pass, and the if removes empty cells. PROC TRANSPOSE is shorter; the DATA step is more flexible. If you need arrays refreshed, the DATA step chapters earlier in this track cover them.

Common mistakes

  1. Forgetting to sort before using by (error: BY variables not sorted).
  2. Transposing character and numeric variables together: all var variables end up in one column, so SAS makes it character if any var is character.
  3. Leaving the default COL1 and _NAME_ names, which makes later code unreadable. Always set name= and prefix=.
  4. Expecting formats to be carried over. Apply formats again to the new columns if you need them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement lists the variables that PROC TRANSPOSE turns into rows?

    VAR names the variables to transpose; BY keeps groups apart and ID supplies new column names.
  2. 2. What supplies the new variable names when reshaping long to wide?

    The values of the ID variable become the names of the new columns; PREFIX= only adds text in front.
  3. 3. What does the NAME= option control?

    By default that variable is NAME; name= renames it, such as name=month.
  4. 4. Why does SAS stop with 'ID value occurs twice in the same BY group'?

    Each ID value must be unique within a BY group, otherwise two values would compete for one new column.

Summary

  • Wide data has one column per measurement; long data has one row per measurement.
  • PROC TRANSPOSE with by and var makes wide data long; add id to go the other way.
  • Use name=, prefix= and label= to avoid cryptic _NAME_ and COL1 names.
  • ID values must be unique per group and valid as variable names.
  • A DATA step with an array is the flexible alternative.

Next: Macro Variables