TRANSPOSE Procedure

Example 5: Naming Transposed Variables When the ID Variable Has Duplicate Values

Features:

PROC TRANSPOSE statement option: LET

This example shows how to use values of a variable (ID) to name transposed variables even when the ID variable has duplicate values.

Program

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

data stocks;
    input Company $14. Date $ Time $ Price;
    datalines;
Horizon Kites jun11 opening 29
Horizon Kites jun11 noon    27
Horizon Kites jun11 closing 27
Horizon Kites jun12 opening 27
Horizon Kites jun12 noon    28
Horizon Kites jun12 closing 30
SkyHi Kites   jun11 opening 43
SkyHi Kites   jun11 noon    43
SkyHi Kites   jun11 closing 44
SkyHi Kites   jun12 opening 44
SkyHi Kites   jun12 noon    45
SkyHi Kites   jun12 closing 45
;

proc transpose data=stocks out=close let;

   by company;

   id date;
 run;

proc print data=close noobs;
   title 'Closing Prices for Horizon Kites and SkyHi Kites';
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=64 pagesize=40;

Create the STOCKS data set. STOCKS contains stock prices for two competing kite manufacturers. The prices are recorded for two days, three times a day: at opening, at noon, and at closing. Notice that the input data set contains duplicate values for the Date variable.

data stocks;
    input Company $14. Date $ Time $ Price;
    datalines;
Horizon Kites jun11 opening 29
Horizon Kites jun11 noon    27
Horizon Kites jun11 closing 27
Horizon Kites jun12 opening 27
Horizon Kites jun12 noon    28
Horizon Kites jun12 closing 30
SkyHi Kites   jun11 opening 43
SkyHi Kites   jun11 noon    43
SkyHi Kites   jun11 closing 44
SkyHi Kites   jun12 opening 44
SkyHi Kites   jun12 noon    45
SkyHi Kites   jun12 closing 45
;

Transpose the data set. LET transposes only the last observation for each BY group. PROC TRANSPOSE transposes only the Price variable. OUT= puts the result of the transposition in the CLOSE data set.

proc transpose data=stocks out=close let;

Organize the output data set into BY groups. The BY statement creates two BY groups, one for each company.

   by company;

Name the transposed variables. The values of Date are used as names for the transposed variables.

   id date;
 run;

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

proc print data=close noobs;
   title 'Closing Prices for Horizon Kites and SkyHi Kites';
run;

Output

The following data set is the output data set, CLOSE.

Closing Prices

       Closing Prices for Horizon Kites and SkyHi Kites       1

              Company       _NAME_    jun11    jun12

           Horizon Kites    Price       27       30
           SkyHi Kites      Price       44       45