Date:         Sat, 22 Feb 2003 08:24:20 -0500
Reply-To:     Robert Virgile <virgile@RCN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Robert Virgile <virgile@RCN.COM>
Subject:      Re: Proc Transpose Efficiency Question
Comments: To: RICH0850 <rich0850@AOL.COM>
In-Reply-To:  <20030221220419.09262.00000092@mb-mh.aol.com>
Content-Type: text/plain; charset="us-ascii"

Richard,

It's not an easy problem, and will require two passes through the data.
Using:

ID DATE;

requires that the first pass through the data obtains a list of all the
date values.  Proc SQL would be a good tool for the job.  The format for
the list will depend the internal values of DATE (is it numeric, is it
character, does it take on a known range of values, etc.), and possibly
the number and names of the variables being transposed.

The fastest solution may depend on how much memory can be devoted to the
task.  Give us a little more to work with ... the number of variables
being transposed and the format/range of the variable DATE.  There is
also an outside chance that describing the rest of the application
(where are you ultimately headed) will suggest an entirely different
approach.

Good luck.

Bob V.




-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@listserv.uga.edu] On Behalf Of
RICH0850
Sent: Friday, February 21, 2003 10:04 PM
To: SAS-L@listserv.uga.edu
Subject: Proc Transpose Efficiency Question

Dear Group:

We have a relatively large SAS dataset (1 million obs or so). We
transpose it
with the following simple statements:

PROC TRANSPOSE;
 BY (15 VARIABLES);
 ID DATE;

As the number of obs and BY variables in the dataset grows the TRANSPOSE
cpu
time grows with it (seems in a more than linear fashion).  Let's not
even talk
about wall-clock time.

Now we like PROC TRANSPOSE.  The "ID DATE" statement keeps everything
properly
aligned by date.  The proc is simple and easy to use.  But we are
getting
killed on through-put.

Is there a data step solution that will provide us with the same results
that
TRANSPOSE provides (in our meager need for alignment by date) without
the
overhead existent in a proc?

Thanks -- Richard