to get the year summary beneath the product/year numbers and then use a simple data step to derive the final calculated variable.
2. By using the compute functionality of PROC REPORT to derive the calculated column (there are quite a few of these - many more than in the sample above). However, I can't see an obvious way to replicate the ALL functionality of PROC TABULATE to produce the year only summary in the same table so have had to use 2 calls to PROC REPORT to get the data at the correct level and then concatenate the two output datasets.
Neither of these is a big deal but it would be great to know if I've missed something and this could all be done in a single procedure call. Have found several SUGI papers and other postings on the forum that imply that this mightn't possible given what the different procedures are designed to do but would like to check to confirm if this is the case.
look for the section entitled "adding multiple summary rows" -- basically, you make some "dummy" variables that allow you to perform multiple "BREAK AFTER" statements. Then with a compute block, you can change the row title on the summary line, if necessary.
I understand a bit better now. I think you'd probably need to have 2 proc reports and then, even with noheaders on the second/year table, there would still be a physical break between the "top" table and the "year" table.
Proc TABULATE does allow you to put multiple smaller tables into one bigger table, so from that standpoint, it's easier.
I'd probably "presummarize" with PROC MEANS and then I'd use the _TYPE_ variables in the output data set to organize the summary lines the way I wanted with PROC REPORT or even PROC PRINT. The advantage of PROC REPORT over PROC PRINT would be the ability of the ORDER variables to be in descending or ORDER=DATA order. Generally the _TYPE_ with lower values are the overall summaries and they would normally order to the top of the report, not the bottom (for example _TYPE_='00' is the overall summary from PROC MEANS).
If I've understood the paper in the link correctly, it advises the creation of one new "dummy" variable in the source dataset for each additional summary row that I want to create. However, in my example I don't know how many distinct values of year there will be in the data. So before running the proc report, the program would have to include additional code containing a query to find this out and then do some macro processing to generate the new variables on a second pass through the data (plus have a macro to generate statements in the proc report itself). Not sure that this is more efficient than the multiple procedure call. However, even with this I still don't get how to build the summary by year only - this seems a different task to that being illustrated - but apologies if I've missed something crucial.
This may not be what you want but I think it produces the output you desire. Seems to me the only problem is the TOTAL which I don't believe PROC REPORT will produce using BREAK statements, but if you create a total ID group you can get the desired output.
proc plan noprint seed=469844687;
y1 = 1 of 100
y2 = 1 of 200
output out=plan id cvals=('A' 'B' 'C') year nvals=(2006 2007 2008);
data planV / view=planV;
ID = 'T';
proc report nowd list headline;
columns id year y1 y2 y3;
define id / group;
define year / group;
y3 = sum(y1.sum,y2.sum);