Help using Base SAS procedures

Proc Report summarize after break variable

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Proc Report summarize after break variable

I'm doing my best to make this understandable.

SAS v9.2, E.G. v4.3

Data is sorted by id2.

Very simplified my code looks like this:

proc report data = logs3 nowd;

column id2

            rows

           rows=rowstot ;

define id2 / group noprint;

define rows / analysis;

define rowstot / sum noprint;

break after id2 / page;

compute after id2;

line @40 rowstot comma12.;

endcomp;


I want to add a summery line at the bottom of each of my groups. I can do this with a <compute after id2> block but then I can't guarentee the calculated variables (sum of row counts) used in the <line> will line up correctly with the column its reflecting. This is because rows to its left may be of different lengths.

I tried to use <rbreak after / summerize skip dol> but I have no control over the look of the by variable (id2). And the by line is ugly it might say something like <id2=BI long name here> when all I really want it to say is <long name here>.

What do I need to do to add the sum of my rows column to a new row broke at each id2 variable?

Thanks everyone.


Accepted Solutions
Solution
‎01-14-2015 02:42 PM
SAS Super FREQ
Posts: 8,743

Re: Proc Report summarize after break variable

Hi:

  The issue you're running into is that the line @40 will only work in the LISTING destination. So if you are working in EG, essentially, the @40 is ignored by the default EG destination or by any ODS destination other than listing -- the side effect of this, is that using the LINE statement nothing "lines up" column for column because, the ODS output is written in a proportional spaced font.

  RBREAK AFTER is used for a "grand total" that will only appear 1 place -- you can put it at the bottom of the report (after the last report row) with RBREAK AFTER. Or you can use RBREAK BEFORE to get it at the top of the report (before the first report row). The only circumstance where RBREAK after will give you multiple break lines on the report is if you have a BY statement in your code (which I didn't see in what you posted).
      

  But why are you writing your own value for ROWSTOT?  I would think that the simple SUMMARIZE option on your BREAK statement would work. (BREAK AFTER ID2/SUMMARIZESmiley Wink In fact, I don't actually get why you have ROWS and then use the ALIAS ROWSTOT unless you have WAY more complicated data. But the LINE @40 won't do what you want. I think that BREAK AFTER is a better choice for what you appear to be doing.
     

  You didn't post any data so I made some from SASHELP.CLASS. As you can see in the posted output. using SUMMARIZE on the BREAK gives me the same number under ROWS, ROWSTOT or in the LINE statement. I didn't bother to use NOPRINT because I wanted to show ALL the variables and it seemed more likely that your real data had more columns than just the 3 you showed, so I put NAME on the report. And because I put SubTotal and Grand Total on the summary lines, you can see which report row came from which statement.
    

Cynthia

data logs3;
  length id2 $15;
  set sashelp.class;
  where age in (12, 13, 14);
  id2 = age;
  rows = height;
  ** make a lot of obs for each id2;
  do i = 1 to 4;
    output;
  end;
run;

   
title; footnote;

proc report data=logs3 nowd;
  column id2 name rows rows=rowstot;
  define id2 / group;
  define name / group;
  define rows / analysis f=comma12.;
  define rowstot / sum 'rowstot alias' f=comma12.;
  break after id2 / page summarize;
  rbreak after /summarize;
  compute after id2;
    id2 = 'SubTotal';
    line '----> value for rowstot is: ' rowstot comma12.;
  endcomp;
  compute after;
    id2 = 'Grand Total';
  endcomp;
run;


why_not_use_summarize_option.png

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Proc Report summarize after break variable

This is because rows to its left may be of different lengths.

Use style overrides to specify an explicit length for the offending columns

Contributor
Posts: 25

Re: Proc Report summarize after break variable

This would work, thank you. But it means that in some cases a variable whose value is only 2 bytes wide will take up 35 bytes of space. One of the variables to the left varies from 2 to 35 wide.

Good answer but in this case it won't work without having to do a lot of if/then coding.

I'd rather be able to easily emulate what rbreak after / summerize does.

Thank you.

Solution
‎01-14-2015 02:42 PM
SAS Super FREQ
Posts: 8,743

Re: Proc Report summarize after break variable

Hi:

  The issue you're running into is that the line @40 will only work in the LISTING destination. So if you are working in EG, essentially, the @40 is ignored by the default EG destination or by any ODS destination other than listing -- the side effect of this, is that using the LINE statement nothing "lines up" column for column because, the ODS output is written in a proportional spaced font.

  RBREAK AFTER is used for a "grand total" that will only appear 1 place -- you can put it at the bottom of the report (after the last report row) with RBREAK AFTER. Or you can use RBREAK BEFORE to get it at the top of the report (before the first report row). The only circumstance where RBREAK after will give you multiple break lines on the report is if you have a BY statement in your code (which I didn't see in what you posted).
      

  But why are you writing your own value for ROWSTOT?  I would think that the simple SUMMARIZE option on your BREAK statement would work. (BREAK AFTER ID2/SUMMARIZESmiley Wink In fact, I don't actually get why you have ROWS and then use the ALIAS ROWSTOT unless you have WAY more complicated data. But the LINE @40 won't do what you want. I think that BREAK AFTER is a better choice for what you appear to be doing.
     

  You didn't post any data so I made some from SASHELP.CLASS. As you can see in the posted output. using SUMMARIZE on the BREAK gives me the same number under ROWS, ROWSTOT or in the LINE statement. I didn't bother to use NOPRINT because I wanted to show ALL the variables and it seemed more likely that your real data had more columns than just the 3 you showed, so I put NAME on the report. And because I put SubTotal and Grand Total on the summary lines, you can see which report row came from which statement.
    

Cynthia

data logs3;
  length id2 $15;
  set sashelp.class;
  where age in (12, 13, 14);
  id2 = age;
  rows = height;
  ** make a lot of obs for each id2;
  do i = 1 to 4;
    output;
  end;
run;

   
title; footnote;

proc report data=logs3 nowd;
  column id2 name rows rows=rowstot;
  define id2 / group;
  define name / group;
  define rows / analysis f=comma12.;
  define rowstot / sum 'rowstot alias' f=comma12.;
  break after id2 / page summarize;
  rbreak after /summarize;
  compute after id2;
    id2 = 'SubTotal';
    line '----> value for rowstot is: ' rowstot comma12.;
  endcomp;
  compute after;
    id2 = 'Grand Total';
  endcomp;
run;


why_not_use_summarize_option.png
Contributor
Posts: 25

Re: Proc Report summarize after break variable

This worked.

1) I removed the alias for rowstot

2) I removed the define for rowstot

3) I modified my compute after id2 block by removing any reference to rowstot

4) I added a: break after id2 / page summarize;

I did not know I could do step 4 until I tried it at your suggestion. I thought it had to be done in a rbreak, which didn't work.

Thank you so much for the help.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1921 views
  • 0 likes
  • 3 in conversation