BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;
 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

13 REPLIES 13
LinusH
Tourmaline | Level 20
Not all char variables, right? Because that would make little sense.
Please share a sample desired output to describe you inquiry.
Data never sleeps
Reeza
Super User

@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
Rhodochrosite | Level 12

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

Reeza
Super User

@ybz12003 wrote:

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


Why is this relevant? 

art297
Opal | Level 21

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
Super User Tom
Super User

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
Rhodochrosite | Level 12

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
Opal | Level 21

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
Super User Tom
Super User

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
Rhodochrosite | Level 12

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
Reeza
Super User

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
Opal | Level 21

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
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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