topic Re: Proc Report summarize after break variable in SAS Procedures

Proc Report summarize after break variable

kcskaiser — Wed, 14 Jan 2015 17:08:11 GMT

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.

Re: Proc Report summarize after break variable

ballardw — Wed, 14 Jan 2015 17:37:40 GMT

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

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

Re: Proc Report summarize after break variable

kcskaiser — Wed, 14 Jan 2015 17:51:01 GMT

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.

Re: Proc Report summarize after break variable

Cynthia_sas — Wed, 14 Jan 2015 19:42:55 GMT

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/SUMMARIZE;) 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;

Re: Proc Report summarize after break variable

kcskaiser — Wed, 14 Jan 2015 21:02:26 GMT

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.