EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.

Tips to Trun around & Transpose data nicely
Cool application for Proc Transpose and array statement

Question: When we try to transpose our deta from horizontal to vertical, or vice versa, we usually have some difficult situation like the following: there are some missing values in the original table, after applying the transpose procedure, the order of the columns is changed.

When we apply proc transpose procedure to transpose the data via By statement, the order of the transposed columns is dependent on the first by "value" in the dataset, if the first by "value" has id variable ordered, then it will show up in that order from left to right in the transposed dataset.

/****************************************************/
data sbp;
input subject \$ visit sbp;
datalines;
101 1 160
101 3 140
101 4 130
101 5 120
202 1 141
202 2 161
202 4 171
202 5 181
; run;

**** SORT SBP VALUES BY SUBJECT.;
proc sort data = sbp;
by subject; run;

**** Apply proc transpose to turn data around;
proc transpose data=sbp out=sbp_flat1(drop=_name_) prefix=visit_;
by subject; id visit; var sbp; run;

We propose the following several ways to resolve the issue.

Method (1)(recommended): apply retain statement to re-order the position.
/****************************************************/
data sbp_flat2;
retain subject _name_ visit_1-visit_5;
set sbp_flat1;         run;

You can also use a macro variable to get all the variables in order.
/****************************************************/
proc contents data=datain out=allvars; run;

proc sql;
select _name_ into: all_vars
separated by ' '
from allvars; quit;

Method (2) (smart application for retain, not that easy to understand):
/****************************************************/
**** TRANSPOSE THE NORMALIZED SBP VALUES TO A FLAT STRUCTURE.;
data sbpflat;
set sbp;
by subject;
keep subject visit1-visit5;
retain visit1-visit5;

**** DEFINE ARRAY TO HOLD SBP VALUES FOR 5 VISITS.;
array sbps {5} visit1-visit5;

**** AT FIRST SUBJECT, INITIALIZE ARRAY TO MISSING.;
if first.subject then
do i = 1 to 5;
sbps{i} = .;
end;

*** Load sbp value into proper slot in the array.
sbps{visit} = sbp;

**** KEEP THE LAST OBSERVATION PER SUBJECT WITH 5 SBPS.;
if last.subject;
run;

Some highly-recommended data visulization books