How to transpose SAS file

[ybz12003]

Hello:

I have a question.  I use "Column" data set at SAS help.  I try to transpose all the rows to the columns.  I found out only the numeric variables could be transposed.  How to do the whole data set including the character variables?  Thanks.

proc transpose data=sashelp.column
out=transp;
run;
 

[LinusH]

Not all char variables, right? Because that would make little sense.
Please share a sample desired output to describe you inquiry.

[Reeza]

---

@ybz12003 wrote:

Hello:

I have a question.  I use "Column" data set at SAS help.  I try to transpose all the rows to the columns.  I found out only the numeric variables could be transposed.  How to do the whole data set including the character variables?  Thanks.

proc transpose data=sashelp.column
out=transp;
run;
 

---

I'm being pedantic but if your question is how to include characters why are you stating that only numeric variables could be transposed. Clearly it can but you don't know, so the question is, how do you do something? Always be as clear as possible, stating something isn't possible means you've already assumed something, incorrectly in this case. 

RTM

If you omit the VAR statement, then the TRANSPOSE procedure transposes all numeric variables in the input data set that are not listed in another statement.

https://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#p19h5tsoknssr4n18gbl...

[ybz12003]

My data set has 500 variables, mixing with character and numeric variable.  

[Reeza]

---

@ybz12003 wrote:

My data set has 500 variables, mixing with character and numeric variable.  

---

Why is this relevant? 

[art297]

Not sure why you'd want to do that, but the following is a way to accomplish it.

This is the second time in the past 24 hours that I recommended the transpose macro (http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset ) as a solution for a transposition problem.

It really wasn't designed to accomplish a transposition that didn't involve a by variable but, in this case, MRACCESS could be used as a by variable since each record has a value of 0 for that variable.

After running the macro, the following would do what you want:

%transpose(data=sashelp.column, out=transp, by=mraccess, autovars=all)

Autovars is one of the features I've always wanted to see added to proc transpose. It has three possible values: NUM (the default), CHAR or ALL

Art, CEO, AnalystFinder.com

[Tom · Accepted solution]

Use the _ALL_ variable list.  If you don't tell it what variables it will assume you want to use _NUMERIC_ variables.

proc transpose data=have out=want;
  var _all_;
run;

If you include at least one character varriable then PROC TRANSPOSE will need to convert the numeric variables to character so the values can be put into a single unified column.

If you want to exclude the BY variables you will need to code for that yourself.

proc transpose data=have
   out=want(where=(upcase(_name_) not in ('VAR1' 'VAR2')))
;
  by var1 var2;
  var _all_;
run;

[ybz12003]

Hello:

I found out all of the column names were assigned to the 2nd row of the SAS data set.  The first row was assigned variables names as "COL1", "COL2", "COL3", etc.    How to eliminate the first row cause I would like to use the original column names?  Thanks.

[art297]

What do you want your resulting file to look like? Originally, you only asked how to transpose both character and numeric variables. Apparently, you want something more.

Art, CEO, AnalystFinder.com

[Tom]

The variables ("columns") created by PROC TRANSPOSE will have generated names like COL1, COL2, only if you don't tell PROC TRANSPOSE where to find the names.  Use the ID statement to tell PROC TRANSPOSE what variable in the original data has the names to use for the new variables.

[ybz12003]

Column1 is pre-transpose file,  Column2 is post-transpose file.   I would like to use the 2nd row of the post-transpose file as variable names.

[Reeza]

Note that when you transpose data, you can only have one type per column - so it will all have to be character variables. 

The data will be hard to use in this format. Are you really looking to have a dataset in this form or some type of report produced?

[art297]

Is the following what you are trying to do?

proc transpose data=sashelp.column out=column2 (drop=_:) let;
  var colname--objname;
  id colname;
run;

data column2;
  set column2 (firstobs=2);
run;

Art, CEO, AnalystFinder.com

[kanivan51]

Hi, ybz!

DATA HAVE;
INPUT NUMERIC_COL CHAR_COL $;
DATALINES;
1 A
2 B
3 C
;
RUN;

PROC TRANSPOSE DATA=HAVE OUT=WANT;
VAR _ALL_;
RUN;