Date:         Thu, 17 Dec 2009 12:13:31 -0800
Reply-To:     Sdlentertd <sdlentertd@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sdlentertd <sdlentertd@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: Transpose function?
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On Dec 17, 1:06 pm, "Richard A. DeVenezia" <rdevene...@gmail.com>
wrote:
> On Dec 17, 2:06 pm, Sdlentertd <sdlente...@gmail.com> wrote:
>
>
>
>
>
> > I have a SAS dataset with these columns:
> > loc1 member1 rx1 date1                loc2 member2 rx2     date2
> > NY   M025     0545   01SEP2009    MN    J545      0689    05SEP2009
> > TN   N123      0145   02SEP2009     AR    J654      1234  06SEP2009
>
> > I need to put all those columns into rows and the main columns will be
> > Loc     Member    rx     date
> > NY     M025        0545   01SEP2009
> > TN     J545      0689       05SEP2009
> > MN    N123       0145     02SEP2009
> > AR    J654      1234      06SEP2009
>
> > I am looking to transpose those columns into rows; and the values
> > would match appropriate columns
>
> > There are like 50 original columns and all of them have 1 through 10
> > at the end of the variable name  .
> > Thank you
>
> There are two common ways to transpose information in a table
> - Proc TRANSPOSE
> - DATA Step and Arrays
> Some uncommon ways would include
> - DATA Step and Hash object
>
> Since your original data does not appear to already have categorical
> variables for grouping, the array approach might be clear to you.
>
> -----------------------
> data have;
> input
>   loc1 $ member1 $ rx1 date1 date9.
>   loc2 $ member2 $ rx2 date2 date9.
> ;
> format date1 date2 date9.;
> datalines;
> NY M025 0545 01SEP2009 MN J545 0689 05SEP2009
> TN N123 0145 02SEP2009 AR J654 1234 06SEP2009
> run;
>
> data need;
> input
>   Loc $ Member $ rx date date9. ;
>   format date;
> datalines;
> NY M025 0545 01SEP2009
> TN J545 0689 05SEP2009
> MN N123 0145 02SEP2009
> AR J654 1234 06SEP2009
> run;
>
> data want;
>   set have;
>   array _loc loc:;
>   array _member member:;
>   array _rx rx:;
>   array _date date:;
>   rowid + 1;
>   do _n_ = 1 to dim(_loc);
>     group = _n_;
>     loc = _loc(_n_);
>     member = _member(_n_);
>     rx = _rx(_n_);
>     date = _date(_n_); format date date9.;
>     OUTPUT;
>   end;
>   keep loc member rx date rowid group;
> run;
>
> proc sort data=want out=want2;
>   by group rowid;
> run;
> -----------------------
>
> Also, arrays based approaches are often used when multiple columns are
> to be transposed in parallel.  The array way requires one pass,
> whereas an equivalent proc transpose way requires multiple passes and
> a merge.
>
> --
> Richard A. DeVenezia- Hide quoted text -
>
> - Show quoted text -

One problem: My date is in different format Date9. (unlike other
character fields) and it doesn't work in the array with other
variables.