To add a few statistics to Howard's code:
I tested the same code on my machine and it got completed in 7.45 seconds
(log lines given below). It took a few minutes to open the new transposed
table however it did open correctly. Howard, I guess it failed to open for
you due to some RAM issues? Not sure if I am right on this.
NOTE: There were 100000 observations read from the data set WORK.LONG.
NOTE: The data set WORK.WIDE has 101 observations and 100001 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 7.45 seconds
cpu time 1.91 seconds
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard
Schreier <hs AT dc-sug DOT org>
Sent: Tuesday, December 12, 2006 12:33 PM
Subject: Re: Transpose columns to rows in SAS?
On Mon, 11 Dec 2006 18:04:35 -0800, Wei ZOU <wzou@UCDAVIS.EDU> wrote:
>Thank you very much, Atul. I will run the code and feed back to you the
>results. After transposing, I may have 100,000 variables but 100 cases.
>Not sure if SAS can handle this kind of dataset.
It can, but such an extremely wide data set is likely to be awkward for just
about any imaginable purpose. I just ran a test:
do id = 1 to 1e5;
retain v1-v100 0;
proc transpose data=long out=wide;
It completed in a few seconds, but now it took forever trying to bring WIDE
up in VIEWTABLE, then failed. That's what you can expect.
If you explain your actual purpose, I suspect that somebody will be able to
suggest an approach which avoids this step.
>> Try this and lemme know if it works:
>> proc transpose data=<dataset-name> out=<output-name> PREFIX=var;
>> ID id;
>> Var Time Height;
>> Its quite basic...i can enhance it if u need it...
>> Wei ZOU wrote:
>>> Hello, all:
>>> I am trying to change my data columns to data rows, i.e., variables <->
>>> cases. In Excel it is called "transpose". Since My data set is big,
>>> Excel cannot process it. Could you please tell me if I can do this using
>>> SAS Transpose in Analyst or code?
>>> An illustration of my original data set:
>>> id time height
>>> 1 100 1011
>>> 2 200 111
>>> 3 300 4222
>>> 4 400 3502
>>> 5 500 100
>>> 6 600 300
>>> I want to transpose it to
>>> var1 var2 var3 var4 var5 var6
>>> Thanks a lot in advance.
>>> Best regards,