topic Re: Proc report and compute for sub-total headings in SAS Programming

Proc report and compute for sub-total headings

metalray — Wed, 16 Jan 2013 12:22:53 GMT

Hello,

assuming I have a proc report that groups by CarType in Order to create nice
sub-totals by car type. I also need to CarType for which the sub-total
is created in a kind of "heading" for the sub-total.

Like this

CarType Value
A 45
A 2
A 3
Total for A 50

CarType Value
B 45
B 2
Total for B 47
Grand Total 97

The "Total for.." how do I do that in a compute statement?

Thanks

Re: Proc report and compute for sub-total headings

Cynthia_sas — Wed, 16 Jan 2013 17:56:55 GMT

Hi:

What PROC REPORT code have you tried already? I am in class this afternoon and not in a position to test code, but if you were using REGION as your BREAK variable, then your COMPUTE block would be something like this.

cynthia

... more code ...

break after region/ summarize;

rbreak after /summarize;

compute after region;

brkline = 'Total for '||trim(region);

region=brkline;

endcomp;

compute after;

region = 'Grand Total';

endcomp;

Re: Proc report and compute for sub-total headings

metalray — Thu, 17 Jan 2013 12:48:39 GMT

Hello Cynthia,

Thanks, that is like what I was looking for.

There is just one problem. Lets look at this example:

PROC REPORT DATA=SASHELP.CLASS LS=80 PS=44 SPLIT="/" NOCENTER BOX;
COLUMN SEX AGE NAME AGE=_A1 HEIGHT WEIGHT;
DEFINE SEX / ORDER FORMAT= $1. ;
DEFINE AGE / ORDER FORMAT= BEST9. ;
DEFINE NAME / ORDER FORMAT= $8. ;
DEFINE _A1 / SUM FORMAT= BEST9. ;
DEFINE HEIGHT / SUM FORMAT= 8.2 ;
DEFINE WEIGHT / SUM FORMAT= 8.2 ;
BREAK AFTER SEX / SUMMARIZE;
BREAK AFTER AGE / SUMMARIZE;

compute after AGE;
brkline = 'Sum of Age '||trim(AGE);
AGE=brkline;
endcomp;

RUN;

If I now want to create a sub-heading that says "Sum of Age ...."
for the sub-totals, SAS then tries to write "Sum of Age ...."
into the AGE column, which does not work, because its a number.
I need this "Sum of Age ...." to the very left, I dont mind adding a new line for that.

Re: Proc report and compute for sub-total headings

Cynthia_sas — Thu, 17 Jan 2013 15:34:14 GMT

Hi:

Basically you can do what you want 1 of 2 ways. (BTW, this "disconnect" will also happen if you have a character variable that has a really small length, too).

1) you create a character variable that's big enough for your subtotal string using a DATA step before PROC REPORT

2) you create a character variable that's big enough for your subtotal string using COMPUTED items in PROC REPORT

3) sometimes, you can use PRETEXT= with ODS, but if you are using LISTING, this method won't work for you

So the code below only shows #1 and #2. I generally stick with those 2 methods anyway, they are easier to explain to beginners. I didn't really understand your code -- why you are using AGE for ORDER and then also using it for SUM -- didn't make sense and the break didn't look reasonable. So I just created a simplified example.

cynthia

data class;
length show_age $40;
set sashelp.class;
show_age = put(age,2.0);
run;

ods html file='c:\temp\subtotal.html';
PROC REPORT DATA=CLASS
     nowd NOCENTER;
title '1) use temp dataset with new character variable';
COLUMN AGE show_age SEX NAME HEIGHT WEIGHT;
DEFINE AGE / ORDER FORMAT= BEST9. /* NOPRINT */;
define show_age / order 'Age' right
       style(column)={just=r};
DEFINE SEX / ORDER FORMAT= $1. ;
DEFINE NAME / ORDER FORMAT= $8. ;
DEFINE HEIGHT / SUM FORMAT= 8.2 ;
DEFINE WEIGHT / SUM FORMAT= 8.2 ;
BREAK AFTER AGE / SUMMARIZE;
compute show_age;
if upcase(_break_)='AGE' then do;
    brkline = 'Sum of Age '||trim(put(age,2.0));
    show_age = brkline;
end;
endcomp;
compute after age;
line ' ';
endcomp;
RUN;

PROC REPORT DATA=SASHELP.CLASS
     nowd NOCENTER;
title '2) use sashelp.class and compute new item in proc report';
COLUMN AGE display_age SEX NAME HEIGHT WEIGHT;
DEFINE AGE / ORDER FORMAT= BEST9. /* NOPRINT */;
define display_age / computed 'Age' right
       style(column)={just=r};
DEFINE SEX / ORDER FORMAT= $1. ;
DEFINE NAME / ORDER FORMAT= $8. ;
DEFINE HEIGHT / SUM FORMAT= 8.2 ;
DEFINE WEIGHT / SUM FORMAT= 8.2 ;
BREAK AFTER AGE / SUMMARIZE;
compute before age;
hold = age;
endcomp;
compute display_age / character length=40;
if age ne . then display_age = put(age,2.0);
else display_age = ' ';
if upcase(_break_) = 'AGE' then do;
     brkline = 'Sum of Age '||trim(put(hold,2.0));
     display_age = brkline;
end;
endcomp;
compute after age;
line ' ';
endcomp;
RUN;
ods html close;

Re: Proc report and compute for sub-total headings

metalray — Mon, 21 Jan 2013 16:38:56 GMT

Thanks Cynthia!