BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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      

6 REPLIES 6
AhmedAl_Attar
Rhodochrosite | Level 12

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
Quartz | Level 8

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
Rhodochrosite | Level 12

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

data_null__
Jade | Level 19

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
Quartz | Level 8

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 879 views
  • 0 likes
  • 3 in conversation