Date:         Thu, 14 Apr 2005 22:17:42 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Transpose V to H

Here's a maro-free approach. It uses Chang's format.

   data totranspose;
   set one;
   year = year - relative;
   array x (*) x : ;
   do i = 1 to dim(x);
      _name_ = compress(vname(x(i) ) || '_' || put(relative,myWords.) );
      xt = x(i);
      output;
      end;
   run;

   proc transpose data=totranspose out=two(drop=_name_);
   by id year;
   var xt;
   run;

On Thu, 14 Apr 2005 10:46:27 -0400, Chang Chung
<chang_y_chung@HOTMAIL.COM> wrote:

>On Thu, 14 Apr 2005 02:52:56 -0400, Thomas <tythong@YAHOO.COM> wrote:
>
>>Hi,
>>
>>Is it possible to apply SAS to the N-variable case? The example here is
X1
>>and X2 (Can X2 be transposed together with X1 into the expected output
>>format).
>>
>>Thanks!
>>Thomas
>>
>>On Thu, 14 Apr 2005 02:49:12 -0400, Thomas <tythong@YAHOO.COM> wrote:
>>
>>>Hi all,
>>>
>>>If I have a dataset as belows, may I know how to transpose the column
>>>variables (vertical) into row (horizontal) by ID efficiently?
>>>
>>>Thanks a lot!
>>>Thomas
>>>
>>>/*input eg*/
>>>ID     YEAR   Relative     X1     X2
>>>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           .       .
>>>
>>>/*expected output (BASED ON RELATIVE=0)*/
>>>ID YEAR X1_0 X1_MINUS1 X1_MINUS2 X1_MINUS3 X1_PLUS1 X1_PLUS2 X1_PLUS3
>>>1  2000  40   90        .         .          45      70       .
>>>2  1999  150  99        99        80         110     100      88
>>>3  2002  20   .         .         .          25      .        .
>
>Hi, Thomas,
>
>I am not sure if the given expected output is correct. I have a hunch that
>you wanted the output below. HTH.
>
>Cheers,
>Chang
>
>data one;
>  input ID YEAR Relative X1 X2;
>cards;
>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           .       .
>;
>run;
>
>
>/* assuming that the relative varies from -99 to 99 */
>proc format;
>  picture myWords
>    -99 -< 0 = '09' (prefix='MINUS')
>    0        = '9'
>    0<- 99   = '09' (prefix='PLUS')
>  ;
>run;
>
>/* assuming that there are &N of x^s */
>%macro newXNames(root, n,min,max);
>  %local i r;
>  %do i = 1 %to &n.;
>    %do j = &min. %to &max.;
>      %let r = &r. &root.&i._%left(%sysfunc(int(&j.),myWords.));
>    %end;
>  %end;
>  %*;&r.
>%mend;
>/* check */
>%put %newXNames(X,2,-3,3);
>
>
>%let n = 2;
>proc sort data=one;
>  by id year;
>run;
>data two(drop=_:);
>  array newX[1:&n.,-99:99] %newXNames(X,&n.,-99,99);
>  do until (last.id);
>    set one;
>    by id year;
>    array x[1:&n.] x1-x&n.;
>    if relative = 0 then _year = year;
>    do _i = 1 to &n.;
>      newX[_i,relative] = x[_i];
>    end;
>  end;
>  year = _year;
>  output;
>run;
>proc print data=two noobs;
>  var id year %newXNames(x,1,-3,3);
>run;
>/* on lst
>ID  YEAR X1_MINUS3 X1_MINUS2 X1_MINUS1 X1_0 X1_PLUS1 X1_PLUS2 X1_PLUS3
> 1  2000      .         .        90      40     45       70       .
> 2  1999     80       100        99     150    110      100      88
> 3  2002      .         .         .      20     25        .       .
>*/