DATA Step, Macro, Functions and more

How to transpose SAS file

Accepted Solution Solved
Reply
Super Contributor
Posts: 297
Accepted Solution

How to transpose SAS file

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;
 


Accepted Solutions
Solution
‎06-01-2017 10:31 PM
Super User
Super User
Posts: 6,495

Re: How to transpose SAS file

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;

 

View solution in original post


All Replies
Super User
Posts: 5,254

Re: How to transpose SAS file

Not all char variables, right? Because that would make little sense.
Please share a sample desired output to describe you inquiry.
Data never sleeps
Super User
Posts: 17,734

Re: How to transpose SAS file


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...

Super Contributor
Posts: 297

Re: How to transpose SAS file

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

Super User
Posts: 17,734

Re: How to transpose SAS file


ybz12003 wrote:

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


Why is this relevant? 

PROC Star
Posts: 7,356

Re: How to transpose SAS file

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

 

Solution
‎06-01-2017 10:31 PM
Super User
Super User
Posts: 6,495

Re: How to transpose SAS file

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;

 

Super Contributor
Posts: 297

Re: How to transpose SAS file

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.

PROC Star
Posts: 7,356

Re: How to transpose SAS file

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

 

Super User
Super User
Posts: 6,495

Re: How to transpose SAS file

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.

Super Contributor
Posts: 297

Re: How to transpose SAS file

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.


Column1.pngColumn2.png
Super User
Posts: 17,734

Re: How to transpose SAS file

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?

PROC Star
Posts: 7,356

Re: How to transpose SAS file

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

 

Occasional Contributor
Posts: 13

Re: How to transpose SAS file

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 243 views
  • 7 likes
  • 6 in conversation