topic Re: Proc Report: Groups in Columns, Vars in Rows? in ODS and Base Reporting

Proc Report: Groups in Columns, Vars in Rows?

wcp_fnfg — Thu, 28 Jan 2016 21:44:43 GMT

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.

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 ;

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

BMiller — Thu, 28 Jan 2016 22:55:11 GMT

I'd use other techniques to restructure the data into a simple summary dataset then use proc report to output.

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

ballardw — Thu, 28 Jan 2016 23:04:49 GMT

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;

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

Ksharp — Fri, 29 Jan 2016 01:31:50 GMT

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 ;

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

ChrisHemedinger — Fri, 29 Jan 2016 13:19:42 GMT

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.

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;

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

wcp_fnfg — Fri, 29 Jan 2016 14:15:02 GMT

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.

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

ballardw — Fri, 29 Jan 2016 15:45:54 GMT

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.

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

wcp_fnfg — Fri, 29 Jan 2016 16:18:52 GMT

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.