PROC REPORT- Rows to Column, Column to Rows

[RAVI2000]

How can you make column to appear as row and row in column using PROC report?

If I use ACROSS, then I can only transpose rows to columns. Is there any other option where we can also transpose columns to rows in PROC REPORT?

[ballardw]

A more concrete example with example data and proc report code would be helpful.

Depending on exactly want you are doing this sounds like it may be possible to do with proc Tabulate as long you aren't don't use any Compute blocks and some of the fancier proc report layout options.

Here is a brief example of "switching" rows to columns and vice versa a couple of ways with proc tabulate.

You should have the SASHELP.CLASS data set available to test this code.

Proc tabulate data=sashelp.class;
   class sex age;
   var height weight;
   table sex,
         age*(height weight) * mean
   ;
   table age,
         sex*(height weight) * mean
   ;
   table sex*(height weight) * mean,
         age
   ;
   table age*(height weight) * mean,
         sex
   ;
   table sex*age,
         (height weight) * mean
   ;
run;

Proc tabulate has the concept of a page, row and column dimension. I am not using page.

The Comma separates the dimensions. The first phrase is the Row dimension (which in the 3rd and 4th examples is nested and can stack things in a manner proc report doesn't), the second is the Column dimension.

There are some caveats. The procedure by default will drop any record where any of the CLASS variables are missing. You can't request statistics in both dimensions (trying to the the max in one row of the column mean for example).

You will see that one big difference is creating multiple tables of different structures with one procedure call is possible which proc report doesn't do (or at least not easily).

[RAVI2000]

This was really helpful @ballardw. I learnt a new concept here.
So I thought, like an ACROSS statement there might be any option in proc report to convert rows to columns. Seems like there isn't any.
WITHIN PROC REPORT:
First we separately transpose rows to columns using a data step or proc transpose and the use the final data in proc report.
WITHIN PROC TABULATE:
We can do rows to columns and vice versa except for the customized output options.

[Cynthia_sas · Accepted solution]

Hi, I'm not sure what you mean by "columns to rows" -- typically, columns are variables with values, like AGE, GENDER, COUNTRY, etc. To turn those columns into "rows", you would need to restructure the data outside of PROC REPORT and then use PROC REPORT on the new restructured dataset. To a certain extent, PROC TABULATE would allow you to get descriptive statistics for each column in a row-wise fashion. but to use PROC REPORT on basic data (like SASHELP.CLASS) you'd need to restructure the data.

For example, compare this PROC REPORT with PROC TABULATE on SASHELP.CLASS:

But if I restructure the data, then I can get this from PROC REPORT which is like (but not exactly like) TABULATE. If you don't need any computed columns or customized break lines, then TABULATE is probably easier for stacking columns as you seem to want. But once the data is restructured, then REPORT works quite nicely:

Cynthia

[RAVI2000]

I understand your explanation. Is there any way that i can make HEIGHT variable transposed into rows? and age into column only by using within PROC report statements and options? Not creating any additional restructured data step.

[ballardw]

---

@RAVI2000 wrote:
I understand your explanation. Is there any way that i can make HEIGHT variable transposed into rows? and age into column only by using within PROC report statements and options? Not creating any additional restructured data step.

---

Provide example data and what the report needs to look like.

Otherwise we really can't tell what is needed for a specific purpose.

Really. The SAS procedures Tabulate and Report are very powerful but like any software have limits and requirements. Without seeing what the data looks like it is not possible to tell what may need to be done to provide a specific outcome. Not seeing the outcome makes this even harder.

[Cynthia_sas]

Hi:
I honestly don't understand what you want. SASHELP.CLASS for example, has 19 rows or observations -- 1 for each student. And, in those 19 rows, there are 17 unique values for HEIGHT (because 2 students are both 62.5 and 2 students are 66.5 as values for HEIGHT). So if HEIGHT got turned into rows, you would have 17 rows of data going down the rows. In SASHELP.CLASS, there are again, 19 rows. For those 19 rows, there are 6 unique values for AGE, because the ages fall into the values 11, 12,13, 14, 15, 16. So in the table I think you're describing, you'd have 6 columns on the report and 17 rows. What would you envision in the cells? About the only thing you could get in the cells would be a count, which might look sort of odd:

Cynthia

[RAVI2000]

Thank you so much for the clarification Cynthia. It cleared my doubt. This is what I was looking for.