Chapter Contents

Previous

Next

Transposing Data Sets

The TRANSPOSE procedure is used to transpose data sets from one form to another. The TRANSPOSE procedure can transpose variables and observations, or transpose variables and observations within BY groups. This section discusses some applications of the TRANSPOSE procedure relevant to time series data sets. Refer to the SAS Procedures Guide for more information on PROC TRANSPOSE.

Transposing from Interleaved to Standard Time Series Form

Observations with _TYPE_=ACTUAL are stored in the new variable ACTUAL; observations with _TYPE_=FORECAST are stored in the new variable FORECAST; and so forth. Note that the method used in this example only works for a single variable.

   title "Original Data Set";
   proc print data=foreout;
      where date > '1may1991'd & date < '1oct1991'd;
   run;
   
   proc transpose data=foreout out=trans(drop=_name_ _label_);
      var cpi;
      id _type_;
      by date;
      where date > '1may1991'd & date < '1oct1991'd;
   run;
   
   title "Transposed Data Set";
   proc print data=trans;
   run;

The TRANSPOSE procedure adds the variables _NAME_ and _LABEL_ to the output data set. These variables contain the names and labels of the variables that were transposed. In this example, there is only one transposed variable, so _NAME_ has the value CPI for all observations. Thus, _NAME_ and _LABEL_ are of no interest and are dropped from the output data set using the DROP= data set option. (If none of the variables transposed have a label, PROC TRANSPOSE does not output the _LABEL_ variable and the DROP=_LABEL_ option produces a warning message. You can ignore this message, or you can prevent the message by omitting _LABEL_ from the DROP= list.)

The original and transposed data sets are shown in Figure 2.23. (The observation numbers shown for the original data set reflect the operation of the WHERE statement.)

Transposing Cross-sectional Dimensions

   title "Original Data Set";
   proc print data=cpicity;
   run;
   
   proc sort data=cpicity out=temp;
      by date city;
   run;
   
   proc transpose data=temp out=citycpi(drop=_name_ _label_);
      var cpi;
      id city;
      by date;
   run;
   
   title "Transposed Data Set";
   proc print data=citycpi;
   run;

The names of the variables in the transposed data sets are taken from the city names in the ID variable CITY. The original and the transposed data sets are shown in Figure 2.24.

The following statements transpose the CITYCPI data set back to the original form of the CPICITY data set. The variable _NAME_ is added to the data set to tell PROC TRANSPOSE the name of the variable in which to store the observations in the transposed data set. (If the (DROP=_NAME_ _LABEL_) option were omitted from the first PROC TRANSPOSE step, this would not be necessary. PROC TRANSPOSE assumes ID _NAME_ by default.)

The NAME=CITY option in the PROC TRANSPOSE statement causes PROC TRANSPOSE to store the names of the transposed variables in the variable CITY. Because PROC TRANSPOSE recodes the values of the CITY variable to create valid SAS variable names in the transposed data set, the values of the variable CITY in the retransposed data set are not the same as the original. The retransposed data set is shown in Figure 2.25.

   data temp;
      set citycpi;
      _name_ = 'CPI';
   run;
   
   proc transpose data=temp out=retrans name=city;
      by date;
   run;
   
   proc sort data=retrans;
      by city date;
   run;
   
   title "Retransposed Data Set";
   proc print data=retrans;
   run;

Chapter Contents

Previous

Next

Top

Copyright © 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.