Help using Base SAS procedures

transpose quarters across loses sorting

Reply
Super Contributor
Posts: 399

transpose quarters across loses sorting

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      

Regular Contributor
Posts: 213

Re: transpose quarters across loses sorting

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;

Super Contributor
Posts: 399

Re: transpose quarters across loses sorting

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

Regular Contributor
Posts: 213

Re: transpose quarters across loses sorting

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"

transposed_want.gif

Respected Advisor
Posts: 3,780

Re: transpose quarters across loses sorting

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.

Super Contributor
Posts: 399

Re: transpose quarters across loses sorting

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

Super Contributor
Posts: 399

Re: transpose quarters across loses sorting

Ask a Question
Discussion stats
  • 6 replies
  • 266 views
  • 0 likes
  • 3 in conversation