The SAS Output Delivery System and reporting techniques

Replicating "ALL" functionality from PROC TABULATE in PROC REPORT

Occasional Contributor
Posts: 9

Replicating "ALL" functionality from PROC TABULATE in PROC REPORT

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.
Posts: 9,368

Re: Replicating "ALL" functionality from PROC TABULATE in PROC REPORT

Posted in reply to SuppersReady
You can add multiple summary Rows in PROC REPORT. The method is outlined in this paper:

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.

Or, using these techniques, you could probably do the entire report in DATA _NULL_:

Posts: 9,368

Re: Replicating "ALL" functionality from PROC TABULATE in PROC REPORT

Posted in reply to Cynthia_sas
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).

Occasional Contributor
Posts: 9

Re: Replicating "ALL" functionality from PROC TABULATE in PROC REPORT

Posted in reply to SuppersReady

Thanks for your help but I still don't get it.

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.
Respected Advisor
Posts: 3,852

Re: Replicating "ALL" functionality from PROC TABULATE in PROC REPORT

Posted in reply to SuppersReady
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;
id=3 ordered
year=3 ordered
y1 = 1 of 100
y2 = 1 of 200
output out=plan id cvals=('A' 'B' 'C') year nvals=(2006 2007 2008);
proc print;
data planV / view=planV;
set plan;
ID = 'T';
proc report nowd list headline;
columns id year y1 y2 y3;
define id / group;
define year / group;
compute y3;
y3 = sum(y1.sum,y2.sum);
Ask a Question
Discussion stats
  • 4 replies
  • 3 in conversation