LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2005, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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 . . >*/


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