topic Re: proc report dynamic sub total heading in ODS and Base Reporting

proc report dynamic sub total heading

metallon — Wed, 20 May 2015 13:59:01 GMT

Hi SAS Experts,

I wonder how I can have the name of the actual total-ing species in the sub heading instead of writing "total SPECIES".

i.e. Total Pigs, Total Horses

proc report

....

COMPUTE CATEGORIE ;

IF _BREAK_ = 'SPECIES' THEN

SPECIES ='total SPECIES';

ELSE IF _BREAK_ ='ResultCat' THEN

ResultCat = 'total R.Cat';

ELSE IF _BREAK_ ='CATEGORIE' THEN

CATEGORIE = 'total Cat.';

ENDCOMP;

A similar problem has been encountered here:

https://communities.sas.com/message/150738

I tried CATEGORIE = 'Sum of'||trim(CATEGORIE);

..but that did not work.

Re: proc report dynamic sub total heading

RW9 — Wed, 20 May 2015 14:10:28 GMT

Personally I would do calculations etc. and set the dataset up as you want to see it before the proc report call. Then you can put whatever you want in any place you want, i.e. full control.

Re: proc report dynamic sub total heading

Cynthia_sas — Wed, 20 May 2015 18:51:03 GMT

Hi:

Did you run the code shown in the posting you referenced. It still works in PROC REPORT. So I don't know why you are having issues. I generally do this for the BREAK statement, since comparisons are case sensitive and RESULTCAT is not the same as ResultCat or resultcat:

if upcase(_break_) = 'RESULTCAT' then ....

cynthia

sorry, in class and SAS machine not on internet and machine on internet doesn't have SAS. So no nice pictures..

Re: proc report dynamic sub total heading

metallon — Fri, 22 May 2015 13:21:50 GMT

Hi Cynthia,

I tried your code but wanted to display a character variable like Name. The below version does not work anymore. 😕

PROC REPORT DATA=SASHELP.CLASS

nowd NOCENTER;

COLUMN AGE display_age SEX NAME ;

DEFINE AGE / ORDER FORMAT= BEST9. /* NOPRINT */;

define display_age / computed 'Name' right

style(column)={just=r};

DEFINE SEX / ORDER FORMAT= $1. ;

DEFINE NAME / ORDER FORMAT= $8. ;

BREAK AFTER NAME / SUMMARIZE;

compute before NAME;

hold = NAME;

endcomp;

compute display_age / character length=40;

if NAME ne . then display_age = put(NAME,$8.);

else display_age = ' ';

if upcase(_break_) = 'NAME' then do;

brkline = 'Sum of NAME '||trim(put(hold,$8.));

display_age = brkline;

end;

endcomp;

compute after NAME;

line ' ';

endcomp;

RUN;

Re: proc report dynamic sub total heading

Cynthia_sas — Fri, 22 May 2015 16:50:21 GMT

Hi:

It looks like you did NOT use the same code. When I run the code from the posting you referenced, both program 1

and program 2 work for me.

It looks to me as though you are trying to use NAME in your subtotal line. The challenge is two-fold -- PROC REPORT works from Left to Right when it builds a REPORT row. So, at the point in time when it is writing AGE and DISPLAY_AGE on the report, it has NO visibility of NAME. So when PROC REPORT is executing the COMPUTE block for DISPLAY_AGE, it doesn't know what the NAME is. Using NAME in your break (BREAK AFTER NAME/SUMMARIZE) doesn't make sense in your posted code, because there are 19 NAMES and the SUM for each NAME will appear underneath EACH NAME, as shown here by running your posted code (The only thing I changed in your code was that I changed the header for Display_AGE back to Display_AGE instead of NAME.)

So, I don't understand what it is you're trying to do. You want to call the variable Display_Age, but have it display the name? Somehow your real data must be different from SASHELP.CLASS. I am just not getting why you made the changes you made.

Here's a different example using SASHELP.SHOES. As you look at the picture, you will see that I am displaying PRODUCT in 3 different ways on the report. The first column for PRODUCT is the original variable from the dataset. It isn't "wide" enough for my entire text string to show on the break line. So, the 2nd column DISPLAY_PRODUCT1 used the PRODUCT item primarily to compute a custom break line. Then, DISPLAY_PRODUCT2 column shows how to "fill-in" the empty rows so that PRODUCT repeats on each line. Is this more what you are looking for????

cynthia

Re: proc report dynamic sub total heading

metallon — Tue, 26 May 2015 12:46:35 GMT

Hi Cynthia,

You are right, I did not execute the same code because I do not need the same

output.

I think you understood what I am trying to do because the last screen print you posted is exactly

what I needed. Indeed my real data is different to SASHELP.CLASS.

Sorry for not explaning the changes I made. I inteded to display the name of the grouped

observations as you did in the last screen print.

The SASHELP.SHOES is exactly what I need!

Thanks for your help.

Bye

Re: proc report dynamic sub total heading

metallon — Tue, 26 May 2015 15:06:46 GMT

How did you manage to "the 2nd column DISPLAY_PRODUCT1 used the PRODUCT item primarily to compute a custom break line." ?

I used to code found here:

Removed the styling but there is no total line at all, let alone one that has the name of the variable that was totalled:

proc format;

value $gender 'F' = 'Female'

'M' = 'Male';

run;

data class;

length gender $20 char_age $10;

set sashelp.class;

char_age = put(age,2.0);

gender = put(sex,$gender.);

run;

proc report data = class nowd;

column sex gender age char_age name height weight;

define sex/order f=$gender. noprint;

define gender /order;

define age/order noprint;

define char_age / order;

compute gender;

if upcase(_break_) = 'GENDER' then do;

gender = 'Total '|| trim(gender);

end;

else if upcase(_break_) = 'CHAR_AGE' then gender = ' ';

endcomp;

compute char_age;

if upcase(_break_) = 'CHAR_AGE' then do;

Char_Age = 'Total '||trim(left(char_age));

end;

endcomp;

run;

It seems whatever I write next to my "trim" does not get displayed altough I have 3 break after´s.

It doesnt matter after which one I COMPUTE, there is no text shown of the variable displayed that is defined inside the "trim"

break after Species/ summarize SUPPRESS style=[backgroundcolor=#C8CCA8 fontsize=1 fontfamily=arial];

break after Animal/ summarize SUPPRESS style=[backgroundcolor=#F4F6E9 fontsize=1 fontfamily=arial];

break after Sample/ summarize SUPPRESS style=[backgroundcolor=#E5EAC1 fontsize=1 fontfamily=arial];

compute SAMPLECAT;

if upcase(_break_) = 'Species' then do;

SubstanceName= total for x '|| trim(put(Species,$char25.));

end;

if upcase(_break_) = 'Animal' then do;

SubstanceName= 'total for y '|| trim(put(Animal,$char25.));

end;

else if upcase(_break_) = Sample' then SAMPLECAT = 'total for z '|| trim(put(SAMPLECAT,$char25.));

endcomp;

Re: proc report dynamic sub total heading

Shubha — Tue, 26 May 2015 16:06:50 GMT

Can this be done for PROC TABULATE? i.e., Can the name of the actual total-ing species in the sub heading be displayed instead of writing "total SPECIES"?

Regards,

Shubha

Re: proc report dynamic sub total heading

Cynthia_sas — Tue, 26 May 2015 23:02:34 GMT

Hi:

Sorry, I was offline for a while. Here's the code that created the screen shot for SASHELP.SHOES. When I made the 2 "extra" variables in the COMPUTE block, note how I made the LENGTH= big enough to hold my whole string: (length=55). That's because the string that you assign at the break (as shown for PRODUCT column) will get truncated to the length of the variable. And, as you can see for PRODUCT, the string that I try to assign gets "cut off" and so, doesn't look right. I am guessing that your variable is not wide enough to hold the whole TOTAL string that you are trying to concatenate together.

cynthia

** sashelp.shoes has multiple obs per product/region;

ods html file='c:\temp\customize_break_diff_ways.html';
PROC REPORT DATA=sashelp.shoes nowd NOCENTER;
where region in ('Asia', 'Pacific', 'Canada');
title 'Different Example';
COLUMN product display_product1 display_product2 region n sales inventory returns;
define product / group /* noprint */;
define display_product1 / computed;
define display_product2 / computed;
define region / group;
define n / 'Count';
define sales / mean;
define inventory / mean;
define returns / mean;
** will use HOLDPROD for DISPLAY_PRODUCT2;
compute before product;
     length holdprod $55;
     holdprod = product;
endcomp;

break after product / summarize;

compute after product;
    product = catx(' ','Average for:', product);
    line ' ';
endcomp;

compute display_product1 / character length=55;
    display_product1 = product;
    if upcase(_break_) = 'PRODUCT' then
       display_product1=catx(' ','Average and Count for:', product);
endcomp;

compute display_product2 / character length=55;
    if product = ' ' then display_product2=holdprod;
    else display_product2 = product;
    if upcase(_break_) = 'PRODUCT' then
       display_product2=catx(' ','Average and Count for:', product);
endcomp;

RUN;
ods html close;

Re: proc report dynamic sub total heading

Cynthia_sas — Tue, 26 May 2015 23:05:58 GMT

No, you have to move to PROC REPORT to be able to customize the break line as shown in the screen shot for SASHELP.SHOES that was previously posted.

cynthia