Providing software solutions since 1976

• Log in
• Create Profile
• Welcome
  [Log out]
• Edit Profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

For this purpose %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

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

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

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

	MAKELONG_BASIS
	MAKELONG


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 MAKELONG(DATA=,OUT=,COPY=,ID=,ROOT=,MEASUREMENT=Measurement);


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


 
*** Define a help macro; 
%MACRO MAKELONG_BASIS(DATA=,OUT=,COPY=,ID=,ROOT=,MEASUREMENT=Measurement);
*** Macro that transposes one variable per by-group;
*** Dr. Gerhard Svolba, May 2nd 2008 - Rel 2.1; 
PROC TRANSPOSE DATA = &data(keep = &id &copy &root.:)
               OUT  = &out(rename = (col1 = &root))
               NAME = _measure;
 BY &id ©
RUN;
*** Create variable with measurement number;
DATA &out;
 SET &out;
 FORMAT &measurement 8.;
 _measure=upcase(_measure);
 &Measurement = INPUT(TRANWRD(_measure,upcase("&root"),''),8.);
 DROP _measure;
RUN;
%MEND;


*** Calculate number of variables;
%LET c=1; 
%DO %WHILE(%SCAN(&root,&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;  
   %MAKELONG_BASIS(data=&data, out = &out, copy=&copy, id=&id, root=&root,measurement=&measurement);
%END; %** end: only 1 variable;

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

 %DO i = 1 %TO &nvars;
    %MAKELONG_BASIS(data=&data, out = _mw_tmp_&i., copy=&copy, id=&id, root=%scan(&root,&i),MEASUREMENT=&Measurement);
 %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_wide; 
      input ID  Drug $14. Depleted $ Histamine0 Histamine1 Histamine3 Histamine5 Heamoglobin0 Heamoglobin1 Heamoglobin3 Heamoglobin5; 
      datalines; 
1   Morphine       N    0.04 0.2  0.1  0.08 14.7 14   14.2 14.1
2   Morphine       N    0.02 0.06 0.02 0.02 14.4 14.5 14.2 14.2
3   Morphine       N    0.07 1.4  0.48 0.24 14.4 14.2 14.9 14.2
4   Morphine       N    0.17 0.57 0.35 0.24 15   14.9 14.3 14.3
5   Morphine       Y    0.1  0.09 0.13 0.14 14.5 14.7 14   14.2
6   Morphine       Y    0.12 0.11 0.1  .    14.4 14.5 14.9 15
7   Morphine       Y    0.07 0.07 0.06 0.07 14.3 14.5 14   14.1
8   Morphine       Y    0.05 0.07 0.06 0.07 14.3 14.1 14.7 14.2
9   Trimethaphan   N    0.03 0.62 0.31 0.22 14.1 14   14.1 14.4
10  Trimethaphan   N    0.03 1.05 0.73 0.6  14.1 14.7 14.5 14.3
11  Trimethaphan   N    0.07 0.83 1.07 0.8  14.6 15   14.2 14
12  Trimethaphan   N    0.09 3.13 2.06 1.23 14.5 14.4 14.3 14.1
13  Trimethaphan   Y    0.1  0.09 0.09 0.08 14.7 14.3 14.2 14.6
14  Trimethaphan   Y    0.08 0.09 0.09 0.1  14.9 14.2 14.4 14.1
15  Trimethaphan   Y    0.13 0.1  0.12 0.12 14.7 14.7 15   14.5
16  Trimethaphan   Y    0.06 0.05 0.05 0.05 14.8 14.9 14.7 14.5
   ; 
run;



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

%MAKELONG(data        = dogs_wide, 
          out         = dogs_long_out,
		  id          = id,
          copy        = drug depleted,
		  root        = histamine heamoglobin,
		  measurement = measurement);

proc print data = dogs_long_out;
run;

/**************************************************************************
Part II - Use Macro MAKELONG, to create a multiple-row-per-subject dataset
**************************************************************************/


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

Operating System and Release Information