topic Re: transpose quarters across loses sorting in SAS Procedures

transpose quarters across loses sorting

podarum — Thu, 19 Dec 2013 15:13:36 GMT

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

Re: transpose quarters across loses sorting

AhmedAl_Attar — Thu, 19 Dec 2013 15:34:42 GMT

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;

Re: transpose quarters across loses sorting

data_null__ — Thu, 19 Dec 2013 15:36:37 GMT

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.

Re: transpose quarters across loses sorting

podarum — Thu, 19 Dec 2013 15:42:35 GMT

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

Re: transpose quarters across loses sorting

AhmedAl_Attar — Thu, 19 Dec 2013 16:08:51 GMT

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"

Re: transpose quarters across loses sorting

podarum — Thu, 19 Dec 2013 16:13:37 GMT

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

Re: transpose quarters across loses sorting

podarum — Thu, 19 Dec 2013 16:47:08 GMT

This article helped me figure it out...

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