BookmarkSubscribeRSS Feed
metalray
Calcite | Level 5

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

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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;

metalray
Calcite | Level 5

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.

Cynthia_sas
SAS Super FREQ

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;

metalray
Calcite | Level 5

Thanks Cynthia!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3235 views
  • 1 like
  • 2 in conversation