search: Search support.sas.com KNOWLEDGE BASE     System Requirements     Install Center     Product Documentation     Papers     Samples & SAS Notes     Focus Areas SUPPORT     License Assistance     Administrator Services     Downloads & Hot Fixes LEARNING CENTER     Bookstore     Training     Certification     SAS Learning Edition     Higher Education Resources     SAS OnDemand For Academics COMMUNITY     User Groups     Events     E-Newsletters

• Print |
• E-mail  |
• Bookmark

This sample deals with the case when data needs to the re-structured from a multiple-row-per-subject structure into a one-row-per-subject structure. 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 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

See also SAS Sample 32121 - Transposing a one-row-per-subject table to a multiple-row-per-subject table using the %MAKELONG macro transposes from a WIDE to a LONG structure.

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

Parameters of the 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.

This sample was authored by Gerhard Svolba. His book, Data Preparation for Analytics is available from the SAS Publishing online bookstore.

---

/***********************************************************************************************************************
Part I - Definition of Macros

	MAKEWIDE_BASIS
	MAKEWIDE


FOR MORE RESSOURCES ON DATA PREPARATION FOR ANALYTICS CHECK OUT:
http://www.sascommunity.org/wiki/Data_Preparation_for_Analytics

Dr. Gerhard Svolba
May 2008

************************************************************************************************************************/



%MACRO MAKEWIDE (DATA=,OUT=out,COPY=,ID=,
                 VAR=, TIME=time);

*** Macro that transposes from a LONG to a WIDE structure;

/*** PARAMETERS *******************************************************
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.
**********************************************************************/

%MACRO MAKEWIDE_BASIS (DATA=,OUT=out,COPY=,ID=,
                 VAR=, TIME=time);
*** Macro that transposes one variable per by-group;
*** Dr. Gerhard Svolba, May 2nd 2008 - Rel 2.1; 
PROC TRANSPOSE DATA   = &data
               PREFIX = &var
               OUT    = &out(DROP = _name_);
 BY  &id ©
 VAR &var;
 ID  &time;
RUN;
%MEND;

*** Calculate number of variables;
%LET c=1; 
%DO %WHILE(%SCAN(&var,&c) NE); 
    %LET c=%EVAL(&c+1);
%END;
%LET nvars=%EVAL(&c-1);

%IF &nvars=1 %then %do; %*** macro is  called with only one variable;  
   %MAKEWIDE_BASIS(data=&data, out = &out, copy=&copy, id=&id, var=&var,time=&time);
%END; %** end: only 1 variable;

%ELSE %DO; ** more then 2 vars;

 %DO i = 1 %TO &nvars;
    %MAKEWIDE_BASIS(data=&data, out = _mw_tmp_&i., copy=&copy, id=&id, var=%scan(&var,&i),time=&time);
 %END; *** end do loop;

 data &out;
  merge %do i = 1 %to &nvars; _mw_tmp_&i. %end; ;
  by &id;
 run;


%END;


%MEND;



/***********************************************************************************************************************
Part II - CREATE DEMO DATASETS
************************************************************************************************************************/


data dogs_long; 
      input ID  Drug $14. Depleted $ Measurement Histamine Heamoglobin; 
      datalines; 
1   Morphine       N    0    0.04      14.7
1   Morphine       N    1    0.2       14
1   Morphine       N    3    0.1       14.2
1   Morphine       N    5    0.08      14.1
2   Morphine       N    0    0.02      14.4
2   Morphine       N    1    0.06      14.5
2   Morphine       N    3    0.02      14.2
2   Morphine       N    5    0.02      14.2
3   Morphine       N    0    0.07      14.4
3   Morphine       N    1    1.4       14.2
3   Morphine       N    3    0.48      14.9
3   Morphine       N    5    0.24      14.2
4   Morphine       N    0    0.17      15
4   Morphine       N    1    0.57      14.9
4   Morphine       N    3    0.35      14.3
4   Morphine       N    5    0.24      14.3
5   Morphine       Y    0    0.1       14.5
5   Morphine       Y    1    0.09      14.7
5   Morphine       Y    3    0.13      14
5   Morphine       Y    5    0.14      14.2
6   Morphine       Y    0    0.12      14.4
6   Morphine       Y    1    0.11      14.5
6   Morphine       Y    3    0.1       14.9
6   Morphine       Y    5    .         15
7   Morphine       Y    0    0.07      14.3
7   Morphine       Y    1    0.07      14.5
7   Morphine       Y    3    0.06      14
7   Morphine       Y    5    0.07      14.1
8   Morphine       Y    0    0.05      14.3
8   Morphine       Y    1    0.07      14.1
8   Morphine       Y    3    0.06      14.7
8   Morphine       Y    5    0.07      14.2
9   Trimethaphan   N    0    0.03      14.1
9   Trimethaphan   N    1    0.62      14
9   Trimethaphan   N    3    0.31      14.1
9   Trimethaphan   N    5    0.22      14.4
10  Trimethaphan   N    0    0.03      14.1
10  Trimethaphan   N    1    1.05      14.7
10  Trimethaphan   N    3    0.73      14.5
10  Trimethaphan   N    5    0.6       14.3
11  Trimethaphan   N    0    0.07      14.6
11  Trimethaphan   N    1    0.83      15
11  Trimethaphan   N    3    1.07      14.2
11  Trimethaphan   N    5    0.8       14
12  Trimethaphan   N    0    0.09      14.5
12  Trimethaphan   N    1    3.13      14.4
12  Trimethaphan   N    3    2.06      14.3
12  Trimethaphan   N    5    1.23      14.1
13  Trimethaphan   Y    0    0.1       14.7
13  Trimethaphan   Y    1    0.09      14.3
13  Trimethaphan   Y    3    0.09      14.2
13  Trimethaphan   Y    5    0.08      14.6
14  Trimethaphan   Y    0    0.08      14.9
14  Trimethaphan   Y    1    0.09      14.2
14  Trimethaphan   Y    3    0.09      14.4
14  Trimethaphan   Y    5    0.1       14.1
15  Trimethaphan   Y    0    0.13      14.7
15  Trimethaphan   Y    1    0.1       14.7
15  Trimethaphan   Y    3    0.12      15
15  Trimethaphan   Y    5    0.12      14.5
16  Trimethaphan   Y    0    0.06      14.8
16  Trimethaphan   Y    1    0.05      14.9
16  Trimethaphan   Y    3    0.05      14.7
16  Trimethaphan   Y    5    0.05      14.5
   ; 
run;

/***********************************************************************************************************************
Part III - Example, Use Macro MAKEWIDE, to create a one-row-per-subject dataset
************************************************************************************************************************/


%MAKEWIDE(data        = dogs_long, 
          out         = dogs_wide_out,
		  id          = id,
          copy        = drug depleted,
		  var         = histamine heamoglobin,
		  time        = measurement)


proc print data = dogs_wide_out;
run;

/**************************************************************************
Part II - Use Macro MAKEWIDE, to create a one-row-per-subject dataset
**************************************************************************/


                                            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

This sample shows how data can be re-structured from a multiple-row-per-subject structure into a one-row-per-subject structure.

Type:

Sample

Date Modified:	2009-11-17 13:17:46
Date Created:	2008-05-15 15:11:39

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	z/OS
		OpenVMS VAX
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows XP 64-bit Edition
		Microsoft® Windows® for x64
		OS/2
		Microsoft Windows 95/98
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Professional
		Microsoft Windows NT Workstation
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows XP Professional
		Windows Millennium Edition (Me)
		Windows Vista
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
		Solaris for x64
		Tru64 UNIX