The SAS Output Delivery System and reporting techniques

replace proc tabulate w proc report help

Reply
Super Contributor
Posts: 291

replace proc tabulate w proc report help

I decided to expand my skill set and try proc report instead tabulate. I need another column in the table and I thought it might be easier to just "report" than to prepare the data for tabulate output.

Just below is the tabulate and how far I've come with report. I'm stuck with the compute section. Help to get me going again would be appreciated.


proc tabulate data=warmpln format=comma7.0;
format WarmType $WType.;
class OpCd WarmType;
var Wt freq PceWt;
table All OpCd=' ',
(All WarmType='Warmer Type') *
(Wt='Weight in Tons' freq='Piece Count' PceWt)*sum=' ';
run;




PROC REPORT DATA=WORK.WARMPLN SPLIT="/" CENTER nowd;

COLUMN OpCd WarmType,(wt _FREQ_) allrow allcol pcewt;

DEFINE OpCd / GROUP FORMAT= $4. WIDTH=10 SPACING=2 LEFT "Operation Code " ;
DEFINE WarmType / ACROSS FORMAT= $WType. WIDTH=10 SPACING=2 LEFT "Warmer Type" ;
DEFINE wt / analysis SUM FORMAT= comma7. WIDTH=9 SPACING=2 RIGHT "Weight in Tons" ;
DEFINE _FREQ_ / analysis SUM FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "Piece Count" ;
DEFINE AllRow / computed Format= best9. width=9 spacing=2 Right "AllRow";
DEFINE AllCol / computed Format= best9. width=9 spacing=2 Right "AllCol";
define PceWt / computed Format= comma7. width=9 spacing=2 Right "PceWt"; ;

**need column totals (across WarmType) and row totals (summing OpCd levels);

compute AllRow;
AllRow=wt.sum;
endcomp;

*add a new column of wt/_freq_;
compute PceWt;
PceWt=wt/_freq_;
endcomp;

RUN;
SAS Super FREQ
Posts: 8,861

Re: replace proc tabulate w proc report help

Hi:
A BREAK statement should take care of a summary line at the bottom of the report. You also do not need a computed variable for the last column. Consider the following example. This is the final report in the LISTING window:
[pre]
Region Actual Sales
Country Product EAST WEST Grand Total
CANADA BED $27,196.00 $20,533.00 $47,729.00
CHAIR $25,200.00 $25,039.00 $50,239.00
DESK $25,020.00 $27,167.00 $52,187.00
SOFA $24,124.00 $26,011.00 $50,135.00
TABLE $25,945.00 $20,755.00 $46,700.00
CANADA $127,485.00 $119,505.00 $246,990.00

GERMANY BED $23,537.00 $22,597.00 $46,134.00
CHAIR $23,277.00 $23,828.00 $47,105.00
DESK $25,403.00 $23,099.00 $48,502.00
SOFA $26,214.00 $28,846.00 $55,060.00
TABLE $26,116.00 $23,081.00 $49,197.00
GERMANY $124,547.00 $121,451.00 $245,998.00

U.S.A. BED $23,137.00 $25,037.00 $48,174.00
CHAIR $27,378.00 $23,558.00 $50,936.00
DESK $23,193.00 $25,350.00 $48,543.00
SOFA $22,263.00 $21,130.00 $43,393.00
TABLE $22,258.00 $24,045.00 $46,303.00
U.S.A. $118,229.00 $119,120.00 $237,349.00

Total $370,261.00 $360,076.00 $730,337.00
[/pre]

The summary line marked "Total" was from an RBREAK statement. The summary line after each COUNTRY value was from a BREAK statement. The skipped line under each COUNTRY group was from a COMPUTE block with a LINE statement. The column marked "Grand Total" came from using ACTUAL with an alias (TOTACT) so that ACTUAL could be used one time with the ACROSS value and another time (with the alias value) for a Grand Total. The PROC REPORT code is below. PROC REPORT does not have the universal class variable ALL as TABULATE does, but you can do simple ACROSS reports with PROC REPORT -almost- as easy as you can with TABULATE.

cynthia
[pre]
ods listing;
options nodate nonumber nocenter;
ods html file='c:\temp\sumreg.html' style=sasweb;

proc report data=sashelp.prdsale nowd;
title 'Row Totals with Break Statements';
title2 'Column Total Using Alias Method';
column country product actual,region actual=totact;
define country / group;
define product / group;
define region / across 'Region Actual Sales';
define actual / sum ' ';
define totact / sum 'Grand Total';
break after country / summarize;
rbreak after / summarize;
compute after country;
line ' ';
endcomp;
compute after;
country = 'Total';
endcomp;
run;
ods html close;
[/pre]
Ask a Question
Discussion stats
  • 1 reply
  • 192 views
  • 0 likes
  • 2 in conversation