transpose quarters across loses sorting

[podarum]

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      

[AhmedAl_Attar]

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;

[podarum]

This didn't work..  It created a lot of dups and put the data diagonally.  it also didn't sort the column (date).

[AhmedAl_Attar]

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"

[data_null__]

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.

[podarum]

How can I reorder the columns ?, there are about 25 of them.

[podarum]

This article helped me figure it out...

http://support.sas.com/resources/papers/proceedings13/319-2013.pdf