Skip to content
9to5sas
  • Index
  • Glossary
Facebook Twitter Instagram Email RSS

  • Start Here
  • Base SAS
  • Advanced SASExpand
    • SAS Macros
    • PROC SQL
  • SAS/STATSExpand
    • SAS Analytics
    • Statistics
  • SAS Programs
9to5sas

9to5sas » ADVANCED SAS » How to transpose a SAS dataset using the Proc Transpose procedure

How to transpose a SAS dataset using the Proc Transpose procedure

BySubhro Posted onFebruary 2, 2020January 6, 2023 Last Updated:January 6, 2023
0 Comments

PROC TRANSPOSE provides the ability to go from a long dataset (where there are multiple rows for a given subject) to a wide dataset (where there are multiple columns for a subject).

Proc Transpose

Most SAS procedures prefer normalised data that tends to be tall and narrow, like Proc means and Proc Freq.

Since we often do not have control over the form of the data when we receive it, we need to be able to convert the data from the normal to non-normal form and from non-normal to normal form or from long data to wide data and vice versa.

This process is known as transposing the data, and the operations are commonly performed by <strong>PROC TRANSPOSE</strong>.

Proc Transpose

Variables become observations, and observations become variables.

The general syntax of the Transpose Procedure

PROC TRANSPOSE DATA=Dataset-name OUT=New-dataset-name;
     BY variable(s);
     COPY variable(s);
     ID variable;
     VAR variable(s);
 RUN;
  • The variables specified in the BY statement is transposed within the combination of the BY variable. The BY variables themselves aren’t transposed but are used to determine the row structure of the transposed dataset.
    • The variables need to be sorted before running PROC TRANSPOSE unless you specify the NOSORTED option.
    • For long-to-wide transposes, the BY variables should uniquely identify each row.
    • For wide-to-long transposes, the BY variables determine the row structure of the long data; that is, it determines the repetition of the rows.
  • The ID statement can be used to help identify rows. The new columns created will be named as per the variables specified in the ID statement. Thus, ID Statement also gives names to the Transposed column.
    • The ID statement also ties a value in a specific row to a specified new column.
    • In the case of long-to-wide transposes, the structure of the column is determined by the ID variable. There will be one column for each unique value of the ID variable (or if multiple ID variables are present, one column for each unique combination of values).
    • For wide-to-long transposes, you typically do not need an ID variable. However, if you do supply an ID variable, it will determine the column structure.
    • The combination of variables on the BY and ID statements must identify down to the row level.
  • The variables in the VAR statement are transposed. If the VAR statement is not included, PROC TRANSPOSE will transpose all numeric variables that are not included in a BY statement or an ID statement. Character variables are transposed only if they are listed in a VAR statement.
    • Usually, one variable is specified for a long to wide transpose, whereas multiple variables are specified for wide to long datasets.
    • The output dataset returns one row for each variable in the VAR statement.

Page Contents

  • Transposing Long to Wide Datasets
  • Transposing Wide to Long Datasets
  • Options available Proc Transpose
  • Transposing multiple variables – Double Transpose

Transposing Long to Wide Datasets

PROC TRANSPOSE provides the ability to go from a long dataset to a wide dataset. Below is an example of a long dataset (SASHELP.ORSALES).

How to transpose a SAS dataset using the Proc Transpose procedure
proc transpose data=sashelp.orsales out=sales;
 var quantity profit total_retail_price;
run;

Output:

How to transpose a SAS dataset using the Proc Transpose procedure

Transposing Wide to Long Datasets

The syntax for transposing wide to long datasets is identical. Still, the objective is to reduce the number of columns and create a data structure where multiple rows are used to define the different attributes of a variable.

How to transpose a SAS dataset using the Proc Transpose procedure
proc transpose data=sashelp.library out=column1;
 id libref;
 var _all_;
run;

Output:

How to transpose a SAS dataset using the Proc Transpose procedure

Options available Proc Transpose

NAME= SAS automatic variable _NAME_ contains the name of the variable being transposed. The remaining transposed variables are named COL1 all the way throughCOLn.

DELIMITER= specifies a delimiter to use as a name for transposed variables in the output data set. The delimiter specified is inserted between variable values if more than one variable is given in the ID statement.

You can use the PREFIX= or SUFFIX= option to specify a prefix or suffix for each new variable name.

data exa;
input subject test $ score;
datalines;
1 post 92
1 pre 90
2 post 88
2 pre 77
3 post 50
3 pre 51
4 post 77
4 pre 72
5 post 69
5 pre 60
;
run;

Output:

How to transpose a SAS dataset using the Proc Transpose procedure
proc transpose data=exa out=exa1 prefix=score;
by subject;
id test;
var score;
run;

Output:

How to transpose a SAS dataset using the Proc Transpose procedure

Transposing multiple variables – Double Transpose

Double Transpose helps us to transpose multiple variables and reshape long data to a wide format.

Below is the original format of the data we want to convert to a wide format.

data subj;
input subject Month $ potassium sodium;
datalines;
210 JAN 5.0 14.0
210 FEB 3.0 11.0
210 MAR 2.0 12.0
211 JAN 1.0 11.0
211 FEB 5.0 10.0
211 MAR 3.0 19.0
212 JUN 3.0 12.0
;
run;

Output:

How to transpose a SAS dataset using the Proc Transpose procedure

We want an output similar to the below.

How to transpose a SAS dataset using the Proc Transpose procedure

1st Transpose

The first PROC TRANSPOSE step creates one column for each value of the variable Potassium and Sodium, and all the values are stored in a single variable COL1.

proc transpose data=subj out=labtran;
 by subject Month notsorted;
 var sodium potassium;
run;
How to transpose a SAS dataset using the Proc Transpose procedure

2nd Transpose

The second PROC TRANSPOSE step reconverts the columns of Potassium and Sodium into rows. The data now has every month represented as a column for each Potassium and Sodium value.

proc transpose data=labtran out=sparsed(drop=_name_); 
by subject; 
var col1; 
id Month _name_; 
run;
proc print;

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Check your inbox or spam folder to confirm your subscription.

Post Tags: #proc transpose by group#proc transpose columns to rows#proc transpose in sas#proc transpose in sas syntax#proc transpose prefix#proc transpose syntax#proc transpose wide to long#SAS Procedures
Subhro

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.

Facebook Twitter Linkedin

Post navigation

Previous Previous
Proc Sort Options in SAS
NextContinue
7 Table lookup techniques for SAS Programmers

SAS Tips in your inbox

Subscribe to 9to5sas for timely SAS tips and news delivered each month.
Learn about the latest articles, and code samples to keep your SAS skills fresh!

Your subscription is successful!

Recent Posts

  • Concatenate strings in SAS: The CAT Functions Demystified
  • 5 Techniques for Quickly Removing Leading Zeros in SAS
  • Troubleshoot Your Proc SQL Code Like a Pro with These 7 Automatic Macro Variables
  • 7 PROC SQL Options You Should Use to Debug Queries
  • How To Use The SAS Proc SQL Order By Statement?
9to5sas
  • Privacy Policy
  • Disclaimer
  • About
  • Contact
Facebook Twitter Instagram RSS Email Pinterest

Copyright © 2023 9TO5SAS, All rights reserved.

Scroll to top
  • 9to5sas Blueprint
  • About
  • About
  • Acceptable use policy
  • calculator
  • Confirm email
  • Contact
  • Contact
  • Cookie Policy
  • DISCLAIMER
  • Getting Started with SAS
  • Glossary
  • Index
  • Post #13801
  • Privacy Policy
  • Privacy policy
  • SAS Programs
  • Styles
  • Subscription confirmed
  • Terms and conditions
  • Thank You