topic Re: proc transpose in SAS Procedures

proc transpose

deleted_user — Thu, 03 Dec 2009 12:02:18 GMT

I'm having some difficulty with a proc transpose.

I want the id field to appear in my new output dataset in ascending order.

i.e.

accno year variable

123 2007 10
123 2006 20
124 2007 30
124 2005 10
124 2006 20

and I want to transpose it to:

2005 2006 2007
123 20 10
124 10 20 30

but the transpose is putting the columns in a different order. Is there an easy way to sort this without me creating new datastep/sql to re-order the columns post-creation?

ta

Re: proc transpose

Bill — Thu, 03 Dec 2009 14:33:29 GMT

and the proc transpose code that you used was ...

Re: proc transpose

sbb — Thu, 03 Dec 2009 14:55:27 GMT

SAS assigns transposed column names as the data is encountered. So you must sort your input data in the desired sequence ahead of the PROC TRANSPOSE execution -- however since you must use a BY statement with your PROC TRANSPOSE code, then you must also coded the NOSORTED option on the BY statement with PROC TRANSPOSE so that SAS will not complain that the data is not in sorted order (based on the BY variable list).

Scott Barry
SBBWorks, Inc.

Re: proc transpose

sbb — Thu, 03 Dec 2009 15:00:21 GMT

After running a test and reading the DOC, I now see that coding the NOTSORTED option with the sample data sorted by YEAR will result in multiple observations for the BY statement variable list since related observations are not contiguous.

As we have discussed previously, why do you need to have the variables (stored internally) in numeric sequence?

Scott Barry
SBBWorks, Inc.

Re: proc transpose

Flip — Thu, 03 Dec 2009 15:10:31 GMT

SAS creates the columns in the order they are encountered. Since you have no obs for 2005 for the first accno it will be created last. You could create an obs with variable = . for 2005 then sort prior to execution.

Easier is to simply specify the order to display the columns and not worry about the internal order.