BookmarkSubscribeRSS Feed
wcp_fnfg
Obsidian | Level 7

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
7 REPLIES 7
BMiller
Obsidian | Level 7
I'd use other techniques to restructure the data into a simple summary dataset then use proc report to output.
ballardw
Super User

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;
ChrisHemedinger
Community Manager

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;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Ksharp
Super User

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

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.

ballardw
Super User

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.

wcp_fnfg
Obsidian | Level 7

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.

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
  • 7 replies
  • 1590 views
  • 2 likes
  • 5 in conversation