Inserting custom text for sub-totals using noprint variables in proc report

[SAS09]

Hoping that one of you can help me with this problem in Proc Report:

I am trying to report on partial subtotals for a variable- for example if age_groups has 10 groupings and I want sub-totals for only 5 how would I do it.

I tried to define a new variable in the dataset using the 5 age groupings and defined it in proc report using order and noprint options.

However I am unable to insert the text -Subtotal" using a compute block. The "Grand Total" text was not a problem.

Any insights would be greatly appreciated!

[Cynthia_sas]

Hi:
This question might be better asked in the ODS and Base Reporting procedure forum, because PROC REPORT is one of the Base Reporting procedures.

You can only BREAK (generate subtotals) on a report item that has been defined as an order or group item on the report. I'm not sure what you want to do.
If you have 2 Main groups (aaa and bbb) and then 6 sub groups for each main group, this is one possible summary report:
[pre]
Main Sub Total
aaa a1 5
a2 5
a3 5
a4 5
a5 5
a6 5
aaa 30

bbb a1 15
a2 15
a3 15
a4 15
a5 15
a6 15
bbb 90
Grand total 120
[/pre]

PROC REPORT only allows you to change a value at a "break" point. So in order to get the line with aaa ..... 30 or bbb....90, you would have needed a statement like this:
[pre]BREAK AFTER MAIN/ SUMMARIZE; [/pre]

If you only want to "subtotal" on a1, a2 and a3, for example, PROC REPORT will not allow you to break after those 3 group items because the break for MAIN will summarize ALL the values.

Can you explain, using the little example of a report above what it is you want to accomplish??? Or show the code that you've tried.

cynthia

ps: to show indented code or to show a sample report with the numbers lined up, refer to this forum posting on how to use some special tags to delimit the "preformatted" text or code
http://support.sas.com/forums/thread.jspa?messageID=27609毙

[SAS09]

Cynthia,

Thank you for the feedback. Let me clarify what I was trying to do using your example:

Let's say that I wanted I sub-totals not at every change in the unique values of Main but at some predetermined breaks. In this example let's also assume that Main has more than 2 main groups - I would like to see the subtotal after bbb instead of at every change in the values of Main.

To do this I created another variable (Pre_Main) that grouped the values of Main in the order I wanted the subtotals to appear in the report. I then defined Pre_main as follows:

define Pre_main / group order noprint ;

define Main/group order;

Then I included the following statements but it inserted the line and the breaks at the correct places but failed to display the "Subtotal" text. The Grand Total test displayed without a problem.


break after Pre_Main/summarize;

rbreak after/summarize;

compute after Pre_Main;

Pre_Main='SubTotal';

line='';

endcomp;

compute after/style={font_weight=bold};

Main='Grand Total' ;

line='';

endcomp;

Anita

[Cynthia_sas]

Hi:
Well, you have several things happening that are all related to how PROC REPORT operates:
1) PROC REPORT works from LEFT to RIGHT placing items on each report ROW -- so that was a good idea to have a PRE_MAIN variable to influence the breaking for MAIN. But, stay tuned, this LEFT to RIGHT operation is going to come back down below.
2) NOPRINT means NOPRINT for EVERYTHING so, since PRE_MAIN is a NOPRINT item...when you do this in your COMPUTE block:
[pre]
compute after PRE_MAIN;
PRE_MAIN ='SubTotal';
line='';
endcomp;
[/pre]

You are assigning the string 'SubTotal' to a NOPRINT variable -- so you will not see anything. There's no problem with the LINE statement because the LINE statement executes after everything in the COMPUTE block executes and writes a blank line to the report.

3) PROC REPORT makes an automatic variable called _BREAK_ which you can test inside COMPUTE blocks. It holds the name of the variable or report item if a report row is coming from a BREAK or RBREAK statement. So, for example, on a "regular" report row, _BREAK_ would be blank, but at the break for PRE_MAIN, the value of _BREAK_ would be PRE_MAIN (the name of the variable). Again, stay tuned, this will come back later.

4) It is redundant to have "group order" in your DEFINE statement. A usage can only be 1 thing -- either GROUP or ORDER. (This is the list of possible usages -- GROUP, ORDER, ACROSS, DISPLAY and ANALYSIS or a STATISTIC name which implies a usage of ANALYSIS). In your case, you are getting an ORDER usage because that is the last usage listed in the DEFINE statement, but if you carefully review the output from this SASHELP.CLASS code, you can see that there -is- a difference in the 2 USAGES.

Test program to illustrate the difference between GROUP and ORDER usage:
[pre]
proc report data=sashelp.class nowd;
title '2 usages -- last specified will be used -- see 19 rows';
column sex age height;
define sex / group order ;
define age / sum;
define height / sum;
run;

proc report data=sashelp.class nowd;
title 'Only GROUP usage -- get 2 summarized report rows';
column sex age height;
define sex / group;
define age / sum;
define height / sum;
run;

proc report data=sashelp.class nowd;
title 'Only ORDER usage -- get 19 report rows';
column sex age height;
define sex / order;
define age / sum;
define height / sum;
run;
[/pre]

You might have meant to type:
[pre]
define Pre_MAIN / group order=data noprint ;
define MAIN/group order=data;
[/pre]

which would be OK, but just group order might get you the wrong report down the road -- so it would be better to fix this now.

Now, the -visible- item on the report row is MAIN, not PRE_MAIN. So you want the value for MAIN to change at the break for PRE_MAIN. But because PROC REPORT works from LEFT to RIGHT and because PRE_MAIN appears before MAIN in the COLUMN statement, you cannot change MAIN in the COMPUTE block for PRE_MAIN, but you can change the value for MAIN in a COMPUTE block for MAIN -- but you will have to test the value of the _BREAK_ variable:
[pre]
compute MAIN;
*** test the automatic _BREAK_ variable;
if upcase(_break_) = "PRE_MAIN" then do;
MAIN = 'SubTotal';
end;
endcomp;
[/pre]

So that should give you some things to work on. First, I'd recommend that you decide whether you want GROUP or ORDER for PRE_MAIN and MAIN and fix that and then move forward and change the COMPUTE blocks.

A good rule of thumb for how to decide between GROUP and ORDER is this:
--ORDER usage produces a detail report where you have 1 report row for every observation in your dataset or subset of data. So, for example, SASHELP.CLASS has 19 observations. With a usage of ORDER, I will see at least 19 report rows. If I have breaks, then the break lines will be added to the 19 detail rows.

--GROUP usage produces a summarized report, where 1 report row represents the summary of a GROUP of observations. So, for example, SASHELP.CLASS has 19 observations, but only 2 genders represented -- male and female. If I set the usage for the SEX variable to be GROUP, then I will see at least 2 report rows (one summarized row for MALE and one summarized row for FEMALE) -- If I have breaks, then the break lines will be added to the 2 grouped rows.

cynthia

[SAS09]

Thank you Cynthia!! It worked! And thank you for explaining the differences between the order and group usage options.