Help using Base SAS procedures

proc transpose

Reply
N/A
Posts: 0

proc transpose

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
Super Contributor
Posts: 292

Re: proc transpose

Posted in reply to deleted_user
and the proc transpose code that you used was ...
Super Contributor
Super Contributor
Posts: 3,174

Re: proc transpose

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: proc transpose

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.
Super Contributor
Posts: 359

Re: proc transpose

Posted in reply to deleted_user
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.
Ask a Question
Discussion stats
  • 4 replies
  • 140 views
  • 0 likes
  • 4 in conversation