Date:         Thu, 17 Dec 2009 12:06:54 -0800
Reply-To:     "Richard A. DeVenezia" <rdevenezia@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@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, 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