The SAS Output Delivery System and reporting techniques

Proc Report - Analysis in Rows?

Reply
Frequent Contributor
Posts: 84

Proc Report - Analysis in Rows?

Trying to get my sums in rows instead of columns, under an across, while using Proc Report (for formatting flexibility).

Group1Group2StatisticAcross1Across2Across3
NYUpstateSum of X102015
Sum of Y456
DownstateSum of X8910
Sum of Y123
PAPhiladelphiaETC....

If the code were tabulate, it'd look something like this:

proc tabulate data = sashelp.cars;

class make model type;

var msrp invoice;

table (make='')*(model='')*

(msrp='Sum MSRP' *sum=''

invoice='Sum Invoice' *sum=''),

(type='')/

box='' row=float;

run;

SAS Super FREQ
Posts: 8,868

Re: Proc Report - Analysis in Rows?

Hi:

  Proc REPORT doesn't do "stacking" in the ROW dimension like PROC TABULATE allows in its syntax. But, you CAN generate a report like you want. But you will need to "flip" the data around a bit. As a teaser, here's an example comparing TABULATE output from your code with REPORT (using only Acura to fit in one screen):

report_like_tab.png

Here's the code. I made a "helper" variable called SUBGRP in a DATA step program so that there would be a SUBGRP column on the report.

Cynthia


ods html file='c:\temp\flip_it.html';

proc tabulate data = sashelp.cars;

  where make = 'Acura'  ;

title '1) TABULATE';

class make model type;

var msrp invoice;

table (make='')*(model='')*

       (msrp='Sum MSRP' *sum='' invoice='Sum Invoice' *sum=''),

      (type='')/

  box='' row=float;

run;

data flip_it;

  set sashelp.cars;

  length subgrp $10;

  where make = 'Acura'  ;

  subgrp = 'MSRP';

  val = msrp;

  output;

  subgrp = 'Invoice';

  val = invoice;

  output;

run;

options missing=' ';

proc report data=flip_it nowd spanrows;

  title '2) REPORT';

  column make model subgrp val,type val=vtot;

  define make / group style(column)=Header;

  define model / group style(column)=Header;

  define subgrp / group order=data style(column)=Header;

  define type / across ' ';

  define val / sum f=comma10.2 ' ';

  define vtot / sum 'Total of Row' f=comma10.2;

  rbreak after / summarize;

run;

ods html close;

Frequent Contributor
Posts: 84

Re: Proc Report - Analysis in Rows?

Posted in reply to Cynthia_sas

Nice to see you again, Cynthia.

I suspected this would be the case, I was just hoping to avoid it.  I'll either set this up or figure out a compromise with Tabulate or something. 

Thanks.

Wes

Frequent Contributor
Posts: 84

Re: Proc Report - Analysis in Rows?

Posted in reply to Cynthia_sas

Any thoughts on how to highlight the Sports column say with a yellow background or with bold font?  I'm having trouble getting the format to carry through the whole column and not just highlight the header.

proc format;

value $light

'Sports' = yellow

other = white;

run;

proc tabulate data = sashelp.cars;

where make = 'Acura';

class make model type;

classlev type/style=[background = $light.];

var msrp invoice;

table (make='')*(model='')*

(msrp='Sum MSRP' *sum=''

invoice='Sum Invoice' *sum=''),

(type='')/

box='' row=float;

run;

Super User
Posts: 11,343

Re: Proc Report - Analysis in Rows?

25102 - Applying the same style across a row in the TABULATE procedure has an example. Or http://support.sas.com/resources/papers/proceedings13/366-2013.pdf

You can add the Parent to VAR variables and statistics as well. You may need to set STYLE_PRECEDENCE=Row as well.

Frequent Contributor
Posts: 84

Re: Proc Report - Analysis in Rows?

Coloring by row seems to be easy, but I cannot find a single example anywhere of the column coloring the data.     

Frequent Contributor
Posts: 84

Re: Proc Report - Analysis in Rows?

Page 18 here describes a solution.  However it dictates that the header style must always be carried.  I don't see a way to selectively carry the header attributes.

https://support.sas.com/resources/papers/stylesinprocs.pdf

Super User
Posts: 11,343

Re: Proc Report - Analysis in Rows?

I think it is time to more clearly describe your data and SHOW what you want the output to look like. Generate the html (or RTF) output without style overrides and then edit it to look as desired and post that. I think we're guessing as to what you want.

Frequent Contributor
Posts: 84

Re: Proc Report - Analysis in Rows?

There's not actually an option for me to attach a file.  So the "no style" output below has columns first\second\third.  The header background is beige and the data background is white.  In the second, styled output, the column third is all green, but the first\second columns' data background also carries the header background - they fill beige instead of remaining white.

ods tagsets.excelxp

file= "test.xml"

style=normal

options (SHEET_INTERVAL="none");

title 'no styles';

proc tabulate data = test;

class name category;

var val;

table name=' ',category=' '*val=' ';

keylabel sum=' ';

run;

proc format;

value $cat

/*'first'=yellow*/

/*'second'=red*/

'third'=green;

run;

title 'Green is styled intentionally, but first\second are unintentionally';

proc tabulate data=test;

class name category;

classlev category / s=[background=$cat.];

var val / style=<parent>;

keyword sum / style=<parent>;

keylabel sum=' ';

table name=' ',category=' '*val=' '*

{style=<parent> {foreground=black}};

run;

ods tagsets.excelxp close;

Ask a Question
Discussion stats
  • 8 replies
  • 638 views
  • 3 likes
  • 3 in conversation