I transposed my data where the date (in quarters as 1997-1 to 2013-3 format) are now as columns. the problem the columns are not sorted, and I don't want to use date in my BY.
HAVE
Region Date Area Sub_Area Var1 Var2 ... Var 25
Tor 1997-1 All All 10 12 6745
Tor 1997-2 B All 5 7 6372
... ....
Tor 2013-3 All G 7 23 6523
..
Van 1997-1 All H 12 21 3345
and so on up to 2013-3 with many other Regions
WANT
Region Area Sub_Area _Name_ 1997-1 1997-2 ..... 2013-1
Tor All All Var1 10 5 7
Tor B All Var2 12 7 23
...
Tor All G Var25 6745 6372 6523
..
Van All H Var1 12
Van All H Var2 21
Van All H Var25 3345
Try this,
data HAVE;
input Region $ Date $ Area $ Sub_Area $ Var1 Var2 Var25 8;
datalines;
Tor 1997-1 All All 10 12 6745
Tor 1997-2 B All 5 7 6372
Tor 2013-3 All G 7 23 6523
Van 1997-1 All H 12 21 3345
;
run;
proc sort data=have;
by region date area sub_area;
run;
proc transpose data=have out=want(drop=date);
by region date area sub_area;
id date;
var var1 var2 var25;
run;
This didn't work.. It created a lot of dups and put the data diagonally. it also didn't sort the column (date).
Interesting!!!
Here is what I got from running the sample code I posted, and I thought it looked like what you originally posted as "WANT"
The new columns are created in the order they are encountered in the data. If you have gaps in the DATE variable you can fill those before you transpose or you can reorder the columns afterward.
How can I reorder the columns ?, there are about 25 of them.
This article helped me figure it out...
http://support.sas.com/resources/papers/proceedings13/319-2013.pdf
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.