PROC TRANSPOSE: How to Convert Variables(columns) into Observations(ROWS) and Observations(ROWS) into Variables(Columns)

During my early days as a SAS programmer, I used to get confused which statement in PROC TRANSPOSE used for what and why?

PROC TRANSPOSE syntax looks like a straightforward thing, but we need to look at important details it offers and without that we may get unexpected results.

Proc Transpose Options:
Proc Tranpose offers several options like OUT=, LABEL=, NAME=, and PREFIX=.
Each option is distinct from the others. "OUT=" option assigns an output dataset, "LABEL=" option assigns a nemae to the variable which has the label of the transposed variable. If we don’t use "LABEL=" option in the PROC TRANSPOSE syntax, the defalut “_LABEL_” will be assigned to the variable that is being transposed.

"NAME= " option works same as the "LABEL=" option, as if we use NAME=option in the TRANSPOSE syntax, which will assign the name to the variable that is being tranposed.

There is another option that we can use in the TRANSPOSE syntax that is "PREFIX=", which assigns the prefix for the transposed variables.

We have two different type of TRANSPOSE: “UP” and “DOWN”:

“UP” transpose change rows of a dataset to columns, whereas the “Down” transpose change columns to rows. We either use "UP" or "DOWN" tranpose depending upon the requirement.

Whenver we use PROC TRANSPOSE we need to ask some questions ourselves, since visualizing the PROC Transpose syntax is not that easy:

To develop the PROC TRANSPOSE syntax we need to know the answers to the above questions,

1) Which variable/variables need to get transposed?
A) Whatever the variable we mention in the ID statement.
2) What are the variables that need to stay same as in the input dataset?
A) Variables that are included in the BY statement.
3) Which variables values need to become the values for the transposed variable.
A) Variables that are included in the VAR statement.


Here I am taking a simple example:

I have a dataset called X {has 3 variables: QTR (char) mob (num) ncl (num)}

Data x;
input qtr$ mob ncl;
cards;
2006q1 0 4
2006q1 1 5
2006q1 2 4
2006q1 3 6
2006q2 0 7
2006q2 1 2
2006q2 2 8
2006q2 3 7
2006q3 0 2
2006q3 1 4
2006q3 2 8
2006q3 3 8
;
 run;


To get the required output use the following syntax: (Proc sort+Proc Tranpsoe+Arrays)

*Sorting needs to be done before we use PROC TRANSPOSE;
proc sort data=x;
by qtr mob;
run;
 
*Transposing the variable MOB using PROC TRANSPOSE; 
proc transpose data=x out=new prefix=mob;
var ncl;
by qtr mob;
id mob;
run;

*Array was used to convert all missing values to zero; 
data new; 
set new;
array zero{4} mob0-mob3;
do i=1 to 4;
if zero(i)=. then zero(i)=0;
end;
drop i mob _name_;
run;

Output:

Example 2:

data grades;

input patid name $ class $ grade;
cards;
10 Alice E1 78
10 Alice E2 82
10 Alice E3 86
10 Alice E4 69
10 Alice P1 97
10 Alice F1 160
11 Barabara E1 88
11 Barabara E2 72
11 Barabara E3 86
11 Barabara E4 99
11 Barabara P1 100
11 Barabara F1 170
12 Jane E1 98
12 Jane E2 92
12 Jane E3 92
12 Jane E4 99
12 Jane P1 99
12 Jane F1 185
;
run;


*Using datastep;


data grades2;
set grades;
by patid;
if class='E1' then E1=grade;
else if class='E2' then E2=grade;
else if class='E3' then E3=grade;
else if class='E4' then E4=grade;
else if class='P1' then P1=grade;
else if class='F1' then F1=grade;
if last.patid then output;
retain E: P: F:;
drop class grade;
run;




*Using Arrays;

data grades1;
array t(*) e1 e2 e3 e4 p1 f1;
do i=1 to 6;
set grades;
t(i)=grade;
end;
drop i class grade;
run;




*Using Proc transpose;


proc transpose data=grades out=grades3(drop=_:);
by patid name;
var grade;
id class;
run;




*REVERSE PROCESS; Getting back the Original dataset;


*Using Arrays;

data grade;
set grades1;
array t(*) e1 e2 e3 e4 p1 f1;
do i=1 to 6;
grade=t(i);
output;
end;
drop e1 e2 e3 e4 p1 f1 i;
run;






*Using Proc transpose;


proc transpose data=grades1 out=grade(rename=(_name_=class));
by patid name;
var e1 e2 e3 e4 p1 f1;
run;






*Transposing multiple variables using ARRAYS;


*Example:; Arrays are very effective when you want to transpose multiple variables at a time.;


data test;
length petest $40 peorres $200;
set test(drop=petest);
array tra {*} peorres01-peorres12;
array tr {*} petest01-petest12;
array t {*} peclnsig01-peclnsig12;
do i=1 to 12;
peorres=tra(i);
petest=tr(i);
peclnsig=t(i);
output;
end;
drop peclnsig01-peclnsig12 peorres01-peorres12 petest01-petest12 i;
run;

****You need to use multiple proc transpose steps if you want to do the above process using Proc Transpose .

Change all missing values of all variables into zeros/putting zeros in place of missing values for variables

Have you been asked how to convert missing values for all the variables into zeros..... if you are.... here is the answer for that.....

In this example the I have used array to do the same.

The variable list includes ID and Score1 to score6.Using simple array method we can change all the missing value for the variables score1 to score6 to 0.

data old;
input ID SCORE1 SCORE2 SCORE3 SCORE4 SCORE5 SCORE6;
cards;
24 100 97 . 100 85 85
28 . 87 98 100 . 90
60 100 . . 100 100 100
65 100 98 100 . 90 100
70 99 97 100 100 95 100
40 97 99 98 . 100 95
190 100 . 97 100 100 90
196 100 100 . 100 100 100
210 . 85 . 90 80 95
; 
run;

*Ist Method;
data new;
set old;
array zero score1-score6;do over zero;
if zero=. then zero=0;
end;
run;

*2nd Method;
data new;

set old;
array nums _numeric_;

do over nums;
if nums=. then nums=0;
end;
run;

proc print;
Title 'Missing values changed to zero using arrays and a do loop';

run;
Output:

ID SCORE1 SCORE2 SCORE3 SCORE4 SCORE5 SCORE6

24 100 97 0 100 85 85
28 0 87 98 100 0 90
60 100 0 0 100 100 100
65 100 98 100 0 90 100
70 99 97 100 100 95 100
40 97 99 98 0 100 95
190 100 0 97 100 100 90
196 100 100 0 100 100 100
210 0 85 0 90 80 95

 Missing values changed to zero using arrays and a do loop.

What if we don't want to convert all.. missing values in variables to zero... I mean .. some of them needs to be converted to zeros and some to 1.

Here is the sample code for that:

The following code will convert all the missing values into either 1 or 0 depending upon the value of ID. If the value of ID less than or equal to 70 then the missing value should be converted to 1 else if the ID value is greater than 70 then the missing values can be converted into 0.

data new1;
set old;
array RS(6) score1-score6 ; 
do i=1 to 6;
if ID le 70 then do;
if RS(i)=. then RS(i)=1; 
end;
else if id gt 70 then do;
if RS(i)=. then RS(i)=0; 
end;
end;
run;
 
/*Macro converts all missing values for numeric variables into 0*/
%macro replaceMissing(ds);
DATA &ds.;SET &ds.;
ARRAY ZERO _NUMERIC_;
DO OVER ZERO;
if ZERO=. then ZERO=0;
end;
run;
%mend replaceMissing;
%replacemissing(dsn);

********************************************************************;
data missing;
set sashelp.column;
array chars _character_;
do over chars;
if chars='' then chars='Missing';
end;
array nums _numeric_;
do over nums;
if nums=. then nums=0;
end;
run;
********************************************************************;
The above code converts missing values of all charcater variables in the sashelp.column dataset  to 'MISSING' . It also converts missing values of all numeric variables in the sashelp.column dataset to 0.