```Date: Thu, 14 Apr 2005 22:17:42 -0400 Reply-To: "Howard Schreier " Sender: "SAS(r) Discussion" From: "Howard Schreier " 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 wrote: >On Thu, 14 Apr 2005 02:52:56 -0400, Thomas 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 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 . . >*/ ```

Back to: Top of message | Previous page | Main SAS-L page