TRANSPOSE Procedure

Example 4: Transposing BY Groups

Features:

BY statement

VAR statement

Other features:

Data set option: RENAME=

This example illustrates transposing BY groups and selecting variables to transpose.

Program

options nodate pageno=1 linesize=80 pagesize=40;

data fishdata;
   infile datalines missover;
   input Location & $10. Date date7.
         Length1 Weight1 Length2 Weight2 Length3 Weight3
         Length4 Weight4;
   format date date7.;
   datalines;
Cole Pond   2JUN95 31 .25 32 .3  32 .25 33 .3
Cole Pond   3JUL95 33 .32 34 .41 37 .48 32 .28
Cole Pond   4AUG95 29 .23 30 .25 34 .47 32 .3
Eagle Lake  2JUN95 32 .35 32 .25 33 .30
Eagle Lake  3JUL95 30 .20 36 .45
Eagle Lake  4AUG95 33 .30 33 .28 34 .42
;

proc transpose data=fishdata
     out=fishlength(rename=(col1=Measurement));

   var length1-length4;

   by location date;
run;

proc print data=fishlength noobs;
   title 'Fish Length Data for Each Location and Date';
run;

Program Description

Set the SAS system options. The NODATE option suppresses the display of the date and time in the output. PAGENO= specifies the starting page number. LINESIZE= specifies the output line length, and PAGESIZE= specifies the number of lines on an output page.

options nodate pageno=1 linesize=80 pagesize=40;

Create the FISHDATA data set. The data in FISHDATA represents length and weight measurements of fish that were caught at two ponds on three separate days. The raw data is sorted by Location and Date.

data fishdata;
   infile datalines missover;
   input Location & $10. Date date7.
         Length1 Weight1 Length2 Weight2 Length3 Weight3
         Length4 Weight4;
   format date date7.;
   datalines;
Cole Pond   2JUN95 31 .25 32 .3  32 .25 33 .3
Cole Pond   3JUL95 33 .32 34 .41 37 .48 32 .28
Cole Pond   4AUG95 29 .23 30 .25 34 .47 32 .3
Eagle Lake  2JUN95 32 .35 32 .25 33 .30
Eagle Lake  3JUL95 30 .20 36 .45
Eagle Lake  4AUG95 33 .30 33 .28 34 .42
;

Transpose the data set. OUT= puts the result of the transposition in the FISHLENGTH data set. RENAME= renames COL1 in the output data set to Measurement.

proc transpose data=fishdata
     out=fishlength(rename=(col1=Measurement));

Specify the variables to transpose. The VAR statement limits the variables that PROC TRANSPOSE transposes.

   var length1-length4;

Organize the output data set into BY groups. The BY statement creates BY groups for each unique combination of values of Location and Date. The procedure does not transpose the BY variables.

   by location date;
run;

Print the FISHLENGTH data set. The NOOBS option suppresses the printing of observation numbers.

proc print data=fishlength noobs;
   title 'Fish Length Data for Each Location and Date';
run;

Output

The following data set is the output data set, FISHLENGTH. For each BY group in the original data set, PROC TRANSPOSE creates four observations, one for each variable that it is transposing. Missing values appear for the variable Measurement (renamed from COL1) when the variables that are being transposed have no value in the input data set for that BY group. Several observations have a missing value for Measurement. For example, in the last observation, a missing value appears because the input data contained no value for Length4 on 04AUG95 at Eagle Lake.

Fish Length Data

                 Fish Length Data for Each Location and Date                  1

                 Location        Date    _NAME_     Measurement

                Cole Pond     02JUN95    Length1         31
                Cole Pond     02JUN95    Length2         32
                Cole Pond     02JUN95    Length3         32
                Cole Pond     02JUN95    Length4         33
                Cole Pond     03JUL95    Length1         33
                Cole Pond     03JUL95    Length2         34
                Cole Pond     03JUL95    Length3         37
                Cole Pond     03JUL95    Length4         32
                Cole Pond     04AUG95    Length1         29
                Cole Pond     04AUG95    Length2         30
                Cole Pond     04AUG95    Length3         34
                Cole Pond     04AUG95    Length4         32
                Eagle Lake    02JUN95    Length1         32
                Eagle Lake    02JUN95    Length2         32
                Eagle Lake    02JUN95    Length3         33
                Eagle Lake    02JUN95    Length4          .
                Eagle Lake    03JUL95    Length1         30
                Eagle Lake    03JUL95    Length2         36
                Eagle Lake    03JUL95    Length3          .
                Eagle Lake    03JUL95    Length4          .
                Eagle Lake    04AUG95    Length1         33
                Eagle Lake    04AUG95    Length2         33
                Eagle Lake    04AUG95    Length3         34
                Eagle Lake    04AUG95    Length4          .