The SAS Output Delivery System and reporting techniques

proc report dynamic sub total heading

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

proc report dynamic sub total heading

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.


Accepted Solutions
Solution
‎05-22-2015 12:50 PM
SAS Super FREQ
Posts: 8,739

Re: proc report dynamic sub total heading

Hi:

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

program_1_works.png

and program 2 work for me.

program_2_works.png

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.)

results_from_posted_code.png

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????

diff_example.png

cynthia

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: proc report dynamic sub total heading

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.

SAS Super FREQ
Posts: 8,739

Re: proc report dynamic sub total heading

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..

Frequent Contributor
Posts: 133

Re: proc report dynamic sub total heading

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;

Solution
‎05-22-2015 12:50 PM
SAS Super FREQ
Posts: 8,739

Re: proc report dynamic sub total heading

Hi:

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

program_1_works.png

and program 2 work for me.

program_2_works.png

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.)

results_from_posted_code.png

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????

diff_example.png

cynthia

Frequent Contributor
Posts: 133

Re: proc report dynamic sub total heading

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

Frequent Contributor
Posts: 133

Re: proc report dynamic sub total heading

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;

SAS Super FREQ
Posts: 8,739

Re: proc report dynamic sub total heading

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;

Occasional Contributor
Posts: 11

Re: proc report dynamic sub total heading

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

SAS Super FREQ
Posts: 8,739

Re: proc report dynamic sub total heading

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

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 973 views
  • 6 likes
  • 4 in conversation