Date: Mon, 24 Mar 2003 15:33:56 -0500
Reply-To: "Matthew P. Wolf" <wolf.m@EI.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Matthew P. Wolf" <wolf.m@EI.COM>
Subject: Pivot Table in SAS
I have the following data set:
Date Unit Output
-------- -------- ------
01/01/01 Unit 001 1200
01/01/01 Unit 002 1100
01/01/01 Unit 003 1230
01/01/01 Unit 004 940
01/02/01 Unit 001 1212
01/02/01 Unit 002 1134
01/02/01 Unit 003 1125
01/02/01 Unit 004 803
01/03/01 Unit 001 1214
01/03/01 Unit 002 1145
01/03/01 Unit 003 1947
01/03/01 Unit 004 842
.
.
.
12/29/01 Unit 001 1274
12/29/01 Unit 002 1023
12/29/01 Unit 003 1236
12/29/01 Unit 004 1232
12/30/01 Unit 001 1224
12/30/01 Unit 002 1358
12/30/01 Unit 003 1288
12/30/01 Unit 004 1853
12/31/01 Unit 001 1768
12/31/01 Unit 002 1468
12/31/01 Unit 003 1243
12/31/01 Unit 004 1885
I would like to manipulate it in the same way a PivotTable would allow me
to do in Excel. The dataset has over 700,000 records or I would do it in
Excel.
The data should look like this when finished:
Date Unit 001 Unit 002 Unit 003 Unit 004
-------- -------- -------- -------- --------
01/01/01 1200 1100 1230 940
01/02/01 1212 1134 1125 803
01/03/01 1214 1145 1947 842
.
.
.
12/29/01 1274 1023 1236 1232
12/30/01 1224 1358 1288 1853
12/31/01 1768 1468 1243 1885
Using the following code, I can get 90% of what I want:
proc transpose data=input out=output;
by Date;
var Output;
run;
This code gives the following output:
Date NAME OF COL1 COL2 COL3 COL4
FORMER
VARIABLE
-------- -------- -------- -------- -------- --------
01/01/01 Output 1200 1100 1230 940
01/02/01 Output 1212 1134 1125 803
01/03/01 Output 1214 1145 1947 842
.
.
.
12/29/01 Output 1274 1023 1236 1232
12/30/01 Output 1224 1358 1288 1853
12/31/01 Output 1768 1468 1243 1885
I've tried running "ID Unit;" in the above code to label the columns, but
SAS ruturns multiple errors stating that the "ID value occurs twice in the
same BY variable".
I'm stumpped, can someone help?
TIA.
