How can I reshape pairs data long to wide?

Data are often collected on pairs of subjects (twins, couples, parent and
child) or on the same subject twice (with and without treatment, before and
after). Depending on the analysis, you may wish to have one observation
represent one subject (or subject-time)–"long" form. Or you may wish to have
one observation represent one pair–"wide" form.

Reshaping from long to wide in SAS can often be done using arrays or proc
transpose. See
our SAS arrays
seminar and our reshaping in SAS learning module for details on these.
However, reshaping pairs data from long to wide often means creating a second
set of an unknown number of variables that are likely not named in any
consistent or numbered manner. These issues make arrays and proc transpose difficult
to use (though not impossible!).

This page provides a set of steps that can be used to reshape pairs data. We
are assuming that the pairs are identified with a pair ID and that, within a pair,
the subjects are differentiated with another variable.

This small sample dataset contains four variables (a, b, c,
d) for three pairs where the subjects within each pair are designated
with the variable t:


data example; input id a b c d t;
datalines;
 1    0.76    0.21    0.11    0.86    1
 1    0.39    0.39    0.94    0.17    2
 2    0.18    0.50    0.54    0.42    1
 2    0.74    0.88    0.74    0.81    2
 3    0.97    0.82    0.03    0.40    1
 3    0.06    0.42    0.47    0.69    2
;

First, using the with-pair ID variable, we create two datasets with one
observation each per pair.

data out1 out2;
  set example;
  if t = 1 then output out1;
  if t = 2 then output out2;
run;

Next, we use one of the macros found in Code Fragment: Renaming variables dynamically
to rename the variables in both new datasets.

%macro rename2(oldvarlist, suffix);
  %let k=1;
  %let old = %scan(&oldvarlist, &k);
     %do %while("&old" NE "");
      rename &old = &old.&suffix;
        %let k = %eval(&k + 1);
      %let old = %scan(&oldvarlist, &k);
  %end;
%mend;

%let varlist = a b c d;

data out1;
  set out1;
  %rename2(&varlist, _1);
run;

data out2;
  set out2;
  %rename2(&varlist, _2);
run;

We have now created two datasets where the variables of interest (those in
the macro variable varlist) have been renamed appropriately. Now we can merge
these two datasets. This method will work smoothly even if there are pair IDs
that do not have two observations.

data all;
  merge out1 (drop=t) out2 (drop=t);
  by id;
run;

proc print data = all;
run;

Obs    id     a_1     b_1     c_1     d_1     a_2     b_2     c_2     d_2

 1      1    0.76    0.21    0.11    0.86    0.39    0.39    0.94    0.17
 2      2    0.18    0.50    0.54    0.42    0.74    0.88    0.74    0.81
 3      3    0.97    0.82    0.03    0.40    0.06    0.42    0.47    0.69