Date:         Thu, 14 Apr 2005 23:28:32 -0400
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: Transpose V to H
Comments: To: sas-l@uga.edu

Thomas wrote:

> If I have a dataset as belows, may I know how to transpose the column
> variables (vertical) into row (horizontal) by ID efficiently?


Yet another way. Compute the column names, sort and transpose.

data raw;
input
ID     YEAR   Relative     X1     X2;
datalines4;
1      1999   -1          90      4.6
1      2000   0           40      2.5
1      2001   1           45      2
1      2002   2           70      5
2      1996   -3          80      5.5
2      1997   -2          100     8
2      1998   -1          99      7.8
2      1999   0           150     10
2      2000   1           110     8.5
2      2001   2           100     8
2      2002   3           88      4
3      2000   -2          .       .
3      2001   -1          .       .
3      2002   0           20      0.5
3      2003   1           25      1
3      2004   2           .       .
;;;;

data foo;
  set raw;
  year = year - relative;

  length _name_ $32;

  if relative = 0 then _name_ = 'X1';
  else
  if relative < 0 then _name_ = 'X1_MINUS' || put(-relative,4.-L);
  else
                       _name_ = 'X1_PLUS'  || put(+relative,4.-L);
run;

proc sort data=foo;
  by id year;
run;

proc transpose data=foo out=foo2(drop=_name_);
  by id year;
  var x1;
  id _name_;
run;

--
Richard A. DeVenezia -- Learn how to customize SAS Explorer
http://www.devenezia.com/downloads/sas/actions/