Transpose data with macro %MAKEWIDE and %MAKELONG (based on Proc TRANSPOSE)

From sasCommunity
Jump to: navigation, search

Introduction

These samples deal with the case when data needs to the re-structured from a multiple-row-per-subject structure into a one-row-per-subject structure (and vice versa). If only one variable needs to be transposed, PROC TRANSPOSE can perform these tasks directly. If however 2 or more variables shall be transposed per BY group you need to transpose each variable separately.

For this purpose %MAKEWIDE and %MAKELONG is introduced that allow to transpose several variables in one go. Note that a first version of this macro with the same have already been introduced with the book “Data Preparation For Analytics Using SAS” by Gerhard Svolba, however this new set of macros allows to specify more than one VAR variable.

2 different data mart structures: Multiple-rows-per-subject data mart (also called: Univariate data set or LONG data set)

       ID    TIME     WEIGHT
        1     1         77
        1     2         79
        1     3         81

One-row-per-subject data set (also called: Multivariate data set or WIDE data set

       ID    WEIGHT1     WEIGHT2     WEIGHT3
        1     77          79           81
  • Comments and suggestions can be sent to the author.
  • Also navigate to content of Data Preparation for Analytics or find other samples under Gerhard's Samples
  • These two macros have been removed from the support.sas.com homepage in 2012 as now PROC TRANSPOSE provides additional options. As I got many requests from users to make my MAKEWIDE and MAKELONG macros available again and as they allow to specify one macro call instead of several SAS statements I made them available here. Also these two implementations are better in performance than the actual version shown at support.sas.com.


Download and Installation

  • Include the macro (not the example code) into your autoexec.sas or run it on demand. Then invoke the MAKEWIDE and MAKELONG macro when needed.

Parameters

  • MAKEWIDE macro
    • DATA and OUT: The names of the input and output data sets, respectively.
    • ID: The name of the ID variable that identifies the subject.
    • COPY: A list of variables that occur repeatedly with each observation for a subject and will be copied to the resulting data set. Note that the COPY variable(s) must not be used in the COPY statement of PROC TRANSPOSE for our purposes, but are listed in the BY statement after the ID variable. We assume here that COPY variables have the same values within one ID.
    • VAR: The variables that holds the values to be transposed.
    • TIME: The variable that numerates the repeated measurements.
  • MAKELONG macro
    • DATA and OUT: The names of the input and output data sets, respectively.
    • ID: The name of the ID variable that identifies the subject.
    • COPY: A list of variables that occur repeatedly with each observation for a subject and will be copied to the resulting data set. Note that the COPY variable(s) must not be used in the COPY statement of PROC TRANSPOSE for our purposes, but should be listed in the BY statement after the ID variable. We assume here that COPY variables have the same values within one ID.
    • ROOT: The part of the variable names (without the measurement number) of the variable that will be transposed.
    • MEASUREMENT: The variable that numerates the repeated measurements.


Running MAKEWIDE to transpose to a one-row-per-subject data mart.

                                           h    h    h    h
                                           e    e    e    e
                       h    h    h    h    a    a    a    a
                       i    i    i    i    m    m    m    m
                   D   s    s    s    s    o    o    o    o
                   e   t    t    t    t    g    g    g    g
                   p   a    a    a    a    l    l    l    l
                   l   m    m    m    m    o    o    o    o
           D       e   i    i    i    i    b    b    b    b
 O         r       t   n    n    n    n    i    i    i    i
 b  I      u       e   e    e    e    e    n    n    n    n
 s  D      g       d   0    1    3    5    0    1    3    5
 1  1 Morphine     N 0.04 0.20 0.10 0.08 14.7 14.0 14.2 14.1
 2  2 Morphine     N 0.02 0.06 0.02 0.02 14.4 14.5 14.2 14.2
 3  3 Morphine     N 0.07 1.40 0.48 0.24 14.4 14.2 14.9 14.2
 4  4 Morphine     N 0.17 0.57 0.35 0.24 15.0 14.9 14.3 14.3
 5  5 Morphine     Y 0.10 0.09 0.13 0.14 14.5 14.7 14.0 14.2
 6  6 Morphine     Y 0.12 0.11 0.10  .   14.4 14.5 14.9 15.0
 7  7 Morphine     Y 0.07 0.07 0.06 0.07 14.3 14.5 14.0 14.1
 8  8 Morphine     Y 0.05 0.07 0.06 0.07 14.3 14.1 14.7 14.2
 9  9 Trimethaphan N 0.03 0.62 0.31 0.22 14.1 14.0 14.1 14.4
10 10 Trimethaphan N 0.03 1.05 0.73 0.60 14.1 14.7 14.5 14.3
11 11 Trimethaphan N 0.07 0.83 1.07 0.80 14.6 15.0 14.2 14.0
12 12 Trimethaphan N 0.09 3.13 2.06 1.23 14.5 14.4 14.3 14.1
13 13 Trimethaphan Y 0.10 0.09 0.09 0.08 14.7 14.3 14.2 14.6
14 14 Trimethaphan Y 0.08 0.09 0.09 0.10 14.9 14.2 14.4 14.1
15 15 Trimethaphan Y 0.13 0.10 0.12 0.12 14.7 14.7 15.0 14.5
16 16 Trimethaphan Y 0.06 0.05 0.05 0.05 14.8 14.9 14.7 14.5


Running macro MAKELONG, to create a multiple-row-per-subject data mart


Obs ID Drug Depleted histamine measurement heamoglobin

 1   1  Morphine         N         0.04            0            14.7
 2   1  Morphine         N         0.20            1            14.0
 3   1  Morphine         N         0.10            3            14.2
 4   1  Morphine         N         0.08            5            14.1
 5   2  Morphine         N         0.02            0            14.4
 6   2  Morphine         N         0.06            1            14.5
 7   2  Morphine         N         0.02            3            14.2
 8   2  Morphine         N         0.02            5            14.2
 9   3  Morphine         N         0.07            0            14.4
10   3  Morphine         N         1.40            1            14.2
11   3  Morphine         N         0.48            3            14.9
12   3  Morphine         N         0.24            5            14.2
13   4  Morphine         N         0.17            0            15.0
14   4  Morphine         N         0.57            1            14.9
15   4  Morphine         N         0.35            3            14.3
16   4  Morphine         N         0.24            5            14.3
17   5  Morphine         Y         0.10            0            14.5
18   5  Morphine         Y         0.09            1            14.7
19   5  Morphine         Y         0.13            3            14.0