REPEATED MEASURES DATA FORMATS by 2KE0Yt3a

VIEWS: 0 PAGES: 10

									SAS REPEATED MEASURES DATA FORMATS

There are 2 common formats for storing and working with balanced repeated measures data. One
common format, called the MULTIVARIATE format has 1 record per unit in the data set with
distinct columns for the repeated measures (and possibly other covariate information). In
REGRESSION format, one row is used to represent the data at each distinct time point. Design
features or covariates that do not change over time are repeated in each row. The terms used to
describe these formats are suggestive: MULTIVARIATE formats are typically required for
standard multivariate methods such as discriminant analysis and MANOVA while
REGRESSION formats are used with regression based procedures.

The 2 SAS programs below show you how to convert one format into the other. Both programs
use the dental data described in Chapters 1 and 4 of Davidian’s notes. This was conducted
involving 27 children, 16 boys and 11 girls. On each child, the distance (mm) from the center of
the pituitary to the pterygomaxillary fissure was made at ages 8, 10, 12, and 14 years of age.
I went to Daividian’s course webpage, and downloaded the data, which is in REGRESSION
format and stored it in a file dental2.data. The SAS program given by Davidian for these data
shows you how to turn the data into MULTIVARIATE format, a task completed by the first
program below. The data for this exercise are provided at the end of this handout.

PROGRAM prog1.129.sas:

data d1;
infile 'C:\ed\LONGITUDINALcourse\dental2.data';

/* col 1 observ number col 2 child id number
   col 3 age               col 4 response (distance)
   col 5 gender indicator (0=girl, 1=boy)              */

input obs child age resp gender;

proc print;

data d1; set d1;
 if age= 8 then age=1;
 if age=10 then age=2;
 if age=12 then age=3;
 if age=14 then age=4;
 drop obs;

proc sort data=d1;
 by gender child;

data d2 (keep=age1-age4 gender child);
 array aa{4} age1-age4; /* age was recoded in d1 to make this easier */
 do age=1 to 4;
 set d1;
 by gender child;
 aa{age}=resp;
 if last.child then return;
end;
title "TRANSFORMED DATA -- 1 RECORD/INDIVIDUAL";

proc print data=d2;
run;

Output from program:

ORIGINAL DATA IN REGRESSION FORMAT:

                Obs obs child age resp gender

                 1     1    1       8   21.0    0
                 2     2    1     10    20.0    0
                 3     3    1     12    21.5    0
                 4     4    1     14    23.0    0
                 5     5    2       8   21.0    0
                 6     6    2     10    21.5    0
                 7     7    2     12    24.0    0
                 8     8    2     14    25.5    0
                 9     9    3       8   20.5    0
                10     10     3    10    24.0   0
                11     11     3    12    24.5   0
                12     12     3    14    26.0   0
                13     13     4     8    23.5   0
                14     14     4    10    24.5   0
                15     15     4    12    25.0   0
                16     16     4    14    26.5   0
                17     17     5     8    21.5   0
                18     18     5    10    23.0   0
                19     19     5    12    22.5   0
                20     20     5    14    23.5   0
                21     21     6     8    20.0   0
                22     22     6    10    21.0   0
                23     23     6    12    21.0   0
                24     24     6    14    22.5   0
                25     25     7     8    21.5   0
                26     26     7    10    22.5   0
                27     27     7    12    23.0   0
                28     28     7    14    25.0   0
                29     29     8     8    23.0   0
                30     30     8    10    23.0   0
                31     31     8    12    23.5   0
                32     32     8    14    24.0   0
                33     33     9     8    20.0   0
                34     34     9    10    21.0   0
                35     35     9    12    22.0   0
                36     36     9    14    21.5   0
                37     37    10     8    16.5   0
                38     38    10    10    19.0   0
                39     39    10    12    19.0   0
40   40   10   14     19.5   0
41   41   11     8    24.5   0
42   42   11   10     25.0   0
43   43   11   12     28.0   0
44   44   11   14     28.0   0
45   45   12     8    26.0   1
46   46   12   10     25.0   1
47   47   12   12     29.0   1
48   48   12   14     31.0   1
49   49   13     8    21.5   1
50   50   13   10     22.5   1
51   51   13   12     23.0   1
52   52   13   14     26.5   1
53   53   14     8    23.0   1
54   54   14    10    22.5   1
55   55   14    12    24.0   1
56   56   14    14    27.5   1
57   57   15      8   25.5   1
58   58   15    10    27.5   1
59   59   15    12    26.5   1
60   60   15    14    27.0   1
61   61   16      8   20.0   1
62   62   16    10    23.5   1
63   63   16    12    22.5   1
64   64   16    14    26.0   1
65   65   17      8   24.5   1
66   66   17    10    25.5   1
67   67   17    12    27.0   1
68   68   17    14    28.5   1
69   69   18      8   22.0   1
70   70   18    10    22.0   1
71   71   18    12    24.5   1
72   72   18    14    26.5   1
73   73   19      8   24.0   1
74   74   19    10    21.5   1
75   75   19    12    24.5   1
76   76   19    14    25.5   1
77   77   20      8   23.0   1
78   78   20    10    20.5   1
79   79   20    12    31.0   1
80   80   20    14    26.0   1
81   81   21      8   27.5   1
82   82   21    10    28.0   1
83   83   21    12    31.0   1
84   84   21    14    31.5   1
85   85   22      8   23.0   1
86   86   22    10    23.0   1
87   87   22    12    23.5   1
88   88   22    14    25.0   1
89   89   23      8   21.5   1
90   90   23    10    23.5   1
   91    91    23     12    24.0    1
   92    92    23     14    28.0     1
   93    93    24       8   17.0     1
   94    94    24      10    24.5    1
   95    95    24      12    26.0    1
   96    96    24      14    29.5    1
   97    97    25       8    22.5    1
   98    98    25      10    25.5    1
   99    99    25      12    25.5    1
  100    100    25     14    26.0    1
  101    101    26      8    23.0    1
  102    102    26     10    24.5    1
  103    103    26     12    26.0    1
  104    104    26     14    30.0    1
  105    105    27      8    22.0    1
  106    106    27     10    21.5    1
  107    107    27     12    23.5    1
  108    108    27     14    25.0    1


TRANSFORMED DATA -- 1 RECORD/INDIVIDUAL


Obs age1 age2 age3 age4 obs gender

 1   21.0    20.0   21.5    23.0     1   0
 2   21.0    21.5   24.0    25.5     2   0
 3   20.5    24.0   24.5    26.0     3   0
 4   23.5    24.5   25.0    26.5     4   0
 5   21.5    23.0   22.5    23.5     5   0
 6   20.0    21.0   21.0    22.5     6   0
 7   21.5    22.5   23.0    25.0     7   0
 8   23.0    23.0   23.5    24.0     8   0
 9   20.0    21.0   22.0    21.5     9   0
10    16.5   19.0    19.0    19.5   10   0
11    24.5   25.0    28.0    28.0   11   0
12    26.0   25.0    29.0    31.0   12   1
13    21.5   22.5    23.0    26.5   13   1
14    23.0   22.5    24.0    27.5   14   1
15    25.5   27.5    26.5    27.0   15   1
16    20.0   23.5    22.5    26.0   16   1
17    24.5   25.5    27.0    28.5   17   1
18    22.0   22.0    24.5    26.5   18   1
19    24.0   21.5    24.5    25.5   19   1
20    23.0   20.5    31.0    26.0   20   1
21    27.5   28.0    31.0    31.5   21   1
22    23.0   23.0    23.5    25.0   22   1
23    21.5   23.5    24.0    28.0   23   1
24    17.0   24.5    26.0    29.5   24   1
25    22.5   25.5    25.5    26.0   25   1
26    23.0   24.5    26.0    30.0   26   1
             27 22.0 21.5 23.5 25.0             27    1

The second program “reverses” the first program: I took the output from the previous program
(i.e. the data in single record or MULTIVARIATE format) and transformed it into
REGRESSION format.

SAS PROGRAM prog2.129.sas:

data d3;
  input obs age1 age2 age3 age4 child gender;
  datalines;
 1 21.0 20.0 21.5 23.0           1      0
 2 21.0 21.5 24.0 25.5           2      0
 3 20.5 24.0 24.5 26.0           3      0
 4 23.5 24.5 25.0 26.5           4      0
 5 21.5 23.0 22.5 23.5           5      0
 6 20.0 21.0 21.0 22.5           6      0
 7 21.5 22.5 23.0 25.0           7      0
 8 23.0 23.0 23.5 24.0           8      0
 9 20.0 21.0 22.0 21.5           9      0
10 16.5 19.0 19.0 19.5 10               0
11 24.5 25.0 28.0 28.0 11               0
12 26.0 25.0 29.0 31.0 12               1
13 21.5 22.5 23.0 26.5 13               1
14 23.0 22.5 24.0 27.5 14               1
15 25.5 27.5 26.5 27.0 15               1
16 20.0 23.5 22.5 26.0 16               1
17 24.5 25.5 27.0 28.5 17               1
18 22.0 22.0 24.5 26.5 18                1
19 24.0 21.5 24.5 25.5 19               1
20 23.0 20.5 31.0 26.0 20               1
21 27.5 28.0 31.0 31.5 21               1
22 23.0 23.0 23.5 25.0 22               1
23 21.5 23.5 24.0 28.0 23                1
24 17.0 24.5 26.0 29.5 24               1
25 22.5 25.5 25.5 26.0 25               1
26 23.0 24.5 26.0 30.0 26               1
27 22.0 21.5 23.5 25.0 27               1
;
proc print;

proc sort;
  by gender obs;

proc transpose out=d2 name=age prefix=dist;
  var age1-age4;
  by gender obs;

data d2;
   set d2;
   if age='age1' then Cage = 8;     /* just a relabeling of ages to original numerical values */
  else if age = 'age2' then Cage = 10;
  else if age = 'age3' then Cage = 12;
  else Cage = 14;

proc print;

run;

OUTPUT FROM PROGRAM: Input Data



              Obs obs age1 age2          age3     age4 child gender

               1    1    21.0    20.0    21.5    23.0          1   0
               2    2    21.0    21.5    24.0    25.5          2   0
               3    3    20.5    24.0    24.5    26.0          3   0
               4    4    23.5    24.5    25.0    26.5          4   0
               5    5    21.5    23.0    22.5    23.5          5   0
               6    6    20.0    21.0    21.0    22.5          6   0
               7    7    21.5    22.5    23.0    25.0          7   0
               8    8    23.0    23.0    23.5    24.0          8   0
               9    9    20.0     21.0    22.0    21.5         9   0
              10    10    16.5    19.0    19.0    19.5        10   0
              11    11    24.5    25.0    28.0    28.0        11   0
              12    12    26.0    25.0    29.0    31.0        12   1
              13    13    21.5    22.5    23.0    26.5        13   1
              14    14    23.0    22.5    24.0    27.5        14   1
              15    15    25.5    27.5    26.5    27.0        15   1
              16    16    20.0    23.5    22.5    26.0        16   1
              17    17    24.5    25.5    27.0    28.5        17   1
              18    18    22.0    22.0    24.5    26.5        18   1
              19    19    24.0    21.5    24.5    25.5        19   1
              20    20    23.0    20.5    31.0    26.0        20   1
              21    21    27.5    28.0    31.0    31.5        21   1
              22    22    23.0    23.0    23.5    25.0        22   1
              23    23    21.5    23.5    24.0    28.0        23   1
              24    24    17.0    24.5    26.0    29.5        24   1
              25    25    22.5    25.5    25.5    26.0        25   1
              26    26    23.0    24.5    26.0    30.0        26   1
              27    27    22.0    21.5    23.5    25.0        27   1

In REGRESSION format (first 8 children)

                   Obs gender obs age dist1             Cage

                    1     0       1   age1       21.0     8
                2     0     1    age2   20.0   10
                3     0     1    age3   21.5   12
                4     0     1    age4   23.0   14
                5     0     2    age1   21.0    8
                6     0     2    age2   21.5   10
                7     0     2    age3   24.0   12
                8     0     2    age4   25.5   14
                9     0     3    age1   20.5    8
               10      0     3   age2   24.0   10
               11      0     3   age3   24.5   12
               12      0     3   age4   26.0   14
               13      0     4   age1   23.5    8
               14      0     4   age2   24.5   10
               15      0     4   age3   25.0   12
               16      0     4   age4   26.5   14
               17      0     5   age1   21.5    8
               18      0     5   age2   23.0   10
               19      0     5   age3   22.5   12
               20      0     5   age4   23.5   14
               21      0     6   age1   20.0    8
               22      0     6   age2   21.0   10
               23      0     6   age3   21.0   12
               24      0     6   age4   22.5   14
               25      0     7   age1   21.5    8
               26      0     7   age2   22.5   10
               27      0     7   age3   23.0   12
               28      0     7   age4   25.0   14
               29      0     8   age1   23.0    8
               30      0     8   age2   23.0   10
               31      0     8   age3   23.5   12
               32      0     8   age4   24.0   14




Here is another way to transform MULTIVARIATE data into REGRESSION format.
After reading the data set D3 in the previous program, it is stored in the WORK library,
and referred to internally by SAS as WORK.D3. This data set can then be accessed by the
SAS ANALYST (a windows type point and click environment) as follows. From the
MENU BAR, click on SOLUTIONS, and then choose ANALYSIS and ANALYST.
Once in the SAS ANALYST, you will see an empty spreadsheet, called UNTITLED
(NEW). Right click on this name, choose FILE then OPEN BY SAS NAME. Then click
on the WORK library in the left window, and select D3, which will then be moved into
the spreadsheet. Go back to the MENU BAR, click on DATA, and choose STACK
COLUMNS. Ask for HELP if next steps are unclear to you. The result of stacking the
columns is the creation of a new data set, which for me was called STACK (can see this
in tree structure that appears on left part of ANALYST). When you create a data set in
the ANALYST, it is stored in the _PROJ_ library, so if you wish to operate on dataset
STACK in a SAS program editor window, you need to refer to it as _PROJ_.STACK.
As an aside, you can use the SAS EXPLORER (choose VIEW then EXPLORER from
the SAS MENU BAR) to see a list of the active data sets in various libraries – clicking
on the data set name will reveal it’s contents).

If the data in D3 was stored in a WINDOWS directory as a data file, EXCEL spreadsheet,
or SAS data set, it could be directly read into the ANALYST, and then STACKED.


ORIGINAL DATA IN REGRESSION FORMAT:

              Obs obs child age resp gender

                1   1    1         8    21.0    0
                2   2    1       10     20.0    0
                3   3    1       12     21.5    0
                4   4    1       14     23.0    0
                5   5    2         8    21.0    0
                6   6    2       10     21.5    0
                7   7    2       12     24.0    0
                8   8    2       14     25.5    0
                9   9    3         8    20.5    0
               10   10       3    10     24.0   0
               11   11       3    12     24.5   0
               12   12       3    14     26.0   0
               13   13       4     8     23.5   0
               14   14       4    10     24.5   0
               15   15       4    12     25.0   0
               16   16       4    14     26.5   0
               17   17       5     8     21.5   0
               18   18       5    10     23.0   0
               19   19       5    12     22.5   0
               20   20       5    14     23.5   0
               21   21       6      8    20.0   0
               22   22       6    10     21.0   0
               23   23       6    12     21.0   0
               24   24       6    14     22.5   0
               25   25       7     8     21.5   0
               26   26       7    10     22.5   0
               27   27       7    12     23.0   0
               28   28       7    14     25.0   0
               29   29       8     8     23.0   0
               30   30       8    10     23.0   0
               31   31       8    12     23.5   0
               32   32       8    14     24.0   0
               33   33       9     8     20.0   0
               34   34       9    10     21.0   0
               35   35       9    12     22.0   0
36   36    9   14     21.5    0
37   37   10     8    16.5    0
38   38   10   10     19.0    0
39   39   10   12     19.0    0
40   40   10   14      19.5    0
41   41   11     8     24.5    0
42   42   11   10      25.0    0
43   43   11   12      28.0    0
44   44   11   14      28.0    0
45   45   12     8     26.0    1
46   46   12   10      25.0    1
47   47   12   12      29.0    1
48   48   12   14      31.0    1
49   49   13     8     21.5    1
50   50   13   10      22.5    1
51   51   13   12      23.0    1
52   52   13   14      26.5    1
53   53   14     8     23.0    1
54   54   14    10     22.5    1
55   55   14    12     24.0    1
56   56   14    14     27.5    1
57   57   15      8    25.5    1
58   58   15    10     27.5    1
59   59   15    12     26.5    1
60   60   15    14     27.0    1
61   61   16      8    20.0    1
62   62   16    10     23.5    1
63   63   16    12     22.5    1
64   64   16    14     26.0    1
65   65   17      8    24.5    1
66   66   17    10     25.5    1
67   67   17    12     27.0    1
68   68   17    14     28.5    1
69   69   18      8    22.0    1
70   70   18    10     22.0    1
71   71   18    12     24.5    1
72   72   18    14     26.5    1
73   73   19      8    24.0    1
74   74   19    10     21.5    1
75   75   19    12     24.5    1
76   76   19    14     25.5    1
77   77   20      8    23.0    1
78   78   20    10     20.5    1
79   79   20    12     31.0    1
80   80   20    14     26.0    1
81   81   21      8    27.5    1
82   82   21    10     28.0    1
83   83   21    12     31.0    1
84   84   21    14     31.5    1
85   85   22      8    23.0    1
86   86   22    10     23.0    1
 87   87    22    12     23.5    1
 88   88    22    14     25.0    1
 89   89    23      8    21.5    1
 90   90    23    10     23.5    1
 91   91    23    12     24.0    1
 92   92    23    14     28.0     1
 93   93    24      8    17.0     1
 94   94    24     10     24.5    1
 95   95    24     12     26.0    1
 96   96    24     14     29.5    1
 97   97    25       8    22.5    1
 98   98    25     10     25.5    1
 99   99    25     12     25.5    1
100   100    25    14     26.0    1
101   101    26      8    23.0    1
102   102    26    10     24.5    1
103   103    26    12     26.0    1
104   104    26    14     30.0    1
105   105    27      8    22.0    1
106   106    27    10     21.5    1
107   107    27    12     23.5    1
108   108    27    14     25.0    1

								
To top