The SAS Output Delivery System and reporting techniques

Proc Report: Groups in Columns, Vars in Rows?

Reply
Frequent Contributor
Posts: 84

Proc Report: Groups in Columns, Vars in Rows?

I am trying to get a proc report table (not tabulate, I need the formatting from report) that has the analysis variables stacked in rows under the columns, rather than as their own column.

 

Table Example.png 

 

This code gets everything in the table, but I don't want the analysis split under each column header.

 

proc report data=Sashelp.Class nowd ;
column Sex, (Weight height weight=n);
define Sex / across ' ' format=$sex. ;
define Age / across ;
define Weight / mean 'Mean Weight' ;
define Height / mean 'Mean Height' ;
define N / n 'Count' ;
run ;


Table Example.png
Occasional Contributor
Posts: 12

Re: Proc Report: Groups in Columns, Vars in Rows?

I'd use other techniques to restructure the data into a simple summary dataset then use proc report to output.
Super User
Posts: 11,343

Re: Proc Report: Groups in Columns, Vars in Rows?

Can you specify what formatting you are referencing?

 

Here is an example using a SAS supplied dataset of doing what you want with tabulate:

proc tabulate data=sashelp.class;
   class sex;
   var height weight;
   tables (height='Mean height' weight='Mean weight') * mean=''*f=best5.
           n='Count',
           Sex=''
           / row=float;
run;
Community Manager
Posts: 2,952

Re: Proc Report: Groups in Columns, Vars in Rows?

PROC TABULATE is better for summaries.  In fact, I used the Summary Tables task in SAS Enterprise Guide to produce this, which includes some special appearance formatting.  

 

tab.png

 

Code:

 

proc tabulate
data=sashelp.class
;
var weight height;
class sex / order=unformatted missing;
table /* row dimension */
    height={label="Mean Height"}*
        mean={label=""} 
        weight={label="Mean Weight"}*
        mean={label=""} 
        all={label="Count"}*
        n={label=""}*
         {style={font_weight=bold 
           font_style=roman 
           foreground=#ffffff background=#000080}},
        /* column dimension */
    sex={label=""}
    ;
run;
Super User
Posts: 10,018

Re: Proc Report: Groups in Columns, Vars in Rows?

Yeah. You need to change the table structure if you have to use proc report. But I strongly recommend to use proc tabulate . It is not good for proc report for summary statistics .

 

proc sql noprint;
create table n as
 select sex,count(sex) as n
  from sashelp.class
   group by sex;
quit;
data class;
 set sashelp.class(keep=sex weight rename=(weight=value) in=ina) 
     sashelp.class(keep=sex height rename=(height=value) in=inb)
     n(keep=sex n rename=(n=value) in=inc);
length group $ 20;
if ina then group='Mean Weight';
if inb then group='Mean Height';
if inc then group='Count';
run;

proc report data=Class nowd ;
column group Sex,value;
define group/group ' ' order=data;
define Sex / across ' ';
define value/analysis mean ' ';
run ;
Frequent Contributor
Posts: 84

Re: Proc Report: Groups in Columns, Vars in Rows?

Ksharp's got the closest solution, I think, but there's too many variables and formatting changes inside the metrics to go this route.  For example, if I wanted to report a percent in one row and a dollar in the next.

 

Proc Report works fine for summary reporting, except in this particular instance where ACROSS only groups the variables, rather than stacking them.  I'm not sure why that's not an option.

 

The tabulate options here won't work with formatting, as the output needs to go to ODS and tabulate's formats don't usually carry to ODS and excel unless they're tagattr, then there's limits there too.

 

Anyway, thanks everyone, I'll have to come up with a new solution.

Super User
Posts: 11,343

Re: Proc Report: Groups in Columns, Vars in Rows?

Then actual input data and desired output.

 

Tabulate does not like crossing statistics but different statistics in different rows or columns isn't an issue. Dollars are a matter of format. Admittedly percents might be an issue depending on percent of what but consider that Tabulate has specific pctn colpctn rowpctn pctsum colpctsum and rowpctsum summaries.

Frequent Contributor
Posts: 84

Re: Proc Report: Groups in Columns, Vars in Rows?

I have need to change format\metric\calculation per row, not within a row.  I appreciate the effort for the reply, but it doesn't help here.  Thanks.

Ask a Question
Discussion stats
  • 7 replies
  • 698 views
  • 2 likes
  • 5 in conversation