Hi:
How you "hide" columns or calculated items, but still have them available on the report is to use the NOPRINT option on the DEFINE statement. Just as you are using NOPRINT for the YEAR item, you would also use NOPRINT for the QTRGOAL and CALCPCT columns. But, you still need them so don't take them out of the COLUMN statement.
The LINE statement will do what you want (to write extra summary lines), but if your output is going to HTML, RTF or PDF output, there is no guarantee that the LINE statement "extra" information will line up underneath the SALES column the way you want. (only in the LISTING destination -- can you position your text strings written with the LINE statement. With other ODS destinations, your choices are to LEFT, CENTER or RIGHT justify the LINE text strings.)
That means you must use other techniques to generate the extra lines. This paper (on pages 7 and
😎 shows how to generate extra summary lines using PROC REPORT:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf
The difference between what is in this paper and what you want to do is
1) the example only shows extra break lines at the end using "extra" variables named FLAG1 and FLAG2. You would have to create FLAG1 and FLAG2 so they were unique for every QUARTER.
2) Since you are using ACROSS items, you will have to use ABSOLUTE column names such as _c6_, _c7_, etc in your COMPUTE block. There are also examples of ABSOLUTE column names in the above paper.
The thing is that even if you use NOPRINT with items such as FLAG1 and FLAG2 or your QTRGOAL and CALCPCT columns, they still occupy a position on every report row and will get an absolute column number. To review, for example, the absolute column numbers that PROC REPORT would assign in the above code that I posted, you would use the OUT= option
[pre]
Proc report data=work.newgoal nowd ls=150 out=goalcols;
[/pre]
and then PROC REPORT will create an output dataset (WORK.GOALCOLS) that shows the absolute column numbers (with flag1 and flag2 added):
[pre]
year flag1 flag2 qtr week_end _C6_ _C7_ _C8_ _C9_ _C10_ _C11_ _BREAK_
2010 3 3 3 07/03 113754.5 3551296.25 0.03203 118793.0 4503190 0.02638
2010 3 3 3 07/10 256828.5 3551296.25 0.07232 291422.5 4503190 0.06471
2010 3 3 3 07/17 227376.5 3551296.25 0.06403 266476.5 4503190 0.05918
2010 3 3 3 07/24 231753.0 3551296.25 0.06526 277350.0 4503190 0.06159
2010 3 3 3 07/31 236743.5 3551296.25 0.06666 286673.5 4503190 0.06366
2010 3 3 3 08/07 265156.5 3551296.25 0.07466 309149.5 4503190 0.06865
2010 3 3 3 08/14 222688.0 3551296.25 0.06271 289689.0 4503190 0.06433
2010 3 3 3 . 1554300.5 3551296.25 0.43767 1839554.0 4503190 0.40850 qtr
2010 3 3 . . 1554300.5 3551296.25 0.43767 1839554.0 4503190 0.40850 flag2
2010 3 . . 1554300.5 3551296.25 0.43767 1839554.0 4503190 0.40850 flag1
[/pre]
So, you can see that _c6_ is the SALES column for REP 100 and _c7_ is the QTRGOAL for REP 100 and _c8_ is the CALCPCT for REP100. Then _c9_ is the SALES column for REP 200, _c10_ is the QTRGOAL for REP 200 and _c11_ is the CALCPCT column for REP 200. So, basically you have 3 report items for each sales rep going ACROSS.
Before you create your multiple summary lines, it is essential to understand how the absolute column numbers work. The reason is that you will need to assign the absolute column for QTRGOAL to the column for SALES at the break in a COMPUTE block. In the paper cited above, there were no ACROSS items in the example -- so simple report item names could be used. So you now have to be comfortable with 2 new techniques.
cynthia
** Code that created above output;
[pre]
data newgoal;
length flag1 flag2 $20;
infile datalines dlm=',';
input year qtr week_end : mmddyy10. rep $ sales : comma12. qtrgoal : comma12.;
flag1 = put(qtr,1.0);
flag2 = put(qtr,1.0);
return;
datalines;
2010,3,7/3/2010,100,113754.50,3551296.25
2010,3,7/3/2010,200,118793.00,4503190.00
2010,3,7/10/2010,100,256828.50,3551296.25
2010,3,7/10/2010,200,291422.50,4503190.00
2010,3,7/17/2010,100,227376.50,3551296.25
2010,3,7/17/2010,200,266476.50,4503190.00
2010,3,7/24/2010,100,231753.00,3551296.25
2010,3,7/24/2010,200,277350.00,4503190.00
2010,3,7/31/2010,100,236743.50,3551296.25
2010,3,7/31/2010,200,286673.50,4503190.00
2010,3,8/7/2010,100,265156.50,3551296.25
2010,3,8/7/2010,200,309149.50,4503190.00
2010,3,8/14/2010,100,222688.00,3551296.25
2010,3,8/14/2010,200,289689.00,4503190.00
;
run;
ods listing;
Proc report data=work.newgoal nowd ls=150 out=goalcols;
title '3) Calc Percent and show Abs Cols';
Columns year flag1 flag2 qtr week_end rep,(Sales QtrGoal calcpct);
define Year / group noprint;
define flag1 / group noprint;
define flag2 / group noprint;
define Qtr / group "Fiscal Qtr";
define week_end /group format=YYmmdd9.;
define rep / group across;
define sales/ analysis f=dollar16.2;
define qtrgoal /mean f=dollar16.2 ;
define calcpct /computed f=percent9.2 ;
compute calcpct;
** use absolute column numbers for ACROSS items;
_c8_ = _c6_ / _c7_;
_c11_ = _c9_ / _c10_;
endcomp;
break after flag1 /summarize;
break after flag2 / summarize;
break after qtr /Summarize style={font_weight=bold};
run;
proc print data=goalcols noobs;
title '3b) what are absolute columns';
format week_end mmddyy5.;
run;
[/pre]