topic Re: How to transpose SAS file in SAS Programming

How to transpose SAS file

ybz12003 — Sat, 27 May 2017 01:49:55 GMT

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;

Re: How to transpose SAS file

LinusH — Sat, 27 May 2017 02:47:35 GMT

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

Re: How to transpose SAS file

Reeza — Sat, 27 May 2017 03:34:25 GMT

@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#p19h5tsoknssr4n18gbl9xle0kq1.htm

Re: How to transpose SAS file

ybz12003 — Sat, 27 May 2017 15:12:39 GMT

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

Re: How to transpose SAS file

Reeza — Sat, 27 May 2017 15:18:11 GMT

@ybz12003 wrote:

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

Why is this relevant?

Re: How to transpose SAS file

art297 — Sat, 27 May 2017 15:57:56 GMT

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

Re: How to transpose SAS file

Tom — Sat, 27 May 2017 17:37:53 GMT

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;

Re: How to transpose SAS file

ybz12003 — Mon, 29 May 2017 22:22:43 GMT

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.

Re: How to transpose SAS file

art297 — Mon, 29 May 2017 22:30:30 GMT

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

Re: How to transpose SAS file

ybz12003 — Mon, 29 May 2017 23:01:24 GMT

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.

Re: How to transpose SAS file

Reeza — Tue, 30 May 2017 00:17:06 GMT

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?

Re: How to transpose SAS file

art297 — Tue, 30 May 2017 03:02:15 GMT

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

Re: How to transpose SAS file

Tom — Tue, 30 May 2017 03:27:35 GMT

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.

Re: How to transpose SAS file

kanivan51 — Tue, 30 May 2017 06:28:59 GMT

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;