Am new to producing reports and would appreciate any guidance on the following problem.
I need to produce a report that has the following columns:
Product Year SUM_var1 SUM_var2 calc_var1 = (SUM_var1/SUM_var2)
with the output data being...
A 2005 X X X
A 2006 X X X
A 2007 X X X
B 2005 X X X
B 2006 X X X
B 2007 X X X
C 2005 X X X
C 2006 X X X
C 2007 X X X
Total 2005 X X X
Total 2006 X X X
Total 2007 X X X
Which essentially contains analysis variables var1 and var summed by product and year, followed by a summary by year only. The final column is derived from the summed totals in the previous two.
Now, I've so far been able to do this in one of 2 ways:
1. By using the ALL functionality of proc tabulate, i.e.
table (street_product ALL='TOTAL')*loss_year, var1*sum='' var2*sum='';
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.