Total and Average in Proc report

[vnreddy]

Hi,

I want Total and sum(P_Revenue)/sum(P_Volume) in Total section.

I was able to do the Total, but for the same in Total row i want to perform sum(P_Revenue)/sum(P_Volume) as Revenue total.  I tried to use mean for Revenue column, but i don't want to use mean, if i use mean i am getting a difference in my Total for my Revenue, so i want the calculation to be:  Revenue = sum(P_Revenue)/sum(P_Volume)

Could someone help me how can i so achieve this.

proc report data=test split="*";
columns Plant P_Volume P_Revenue Revenue ;
    define Plant / group 'Plant';
    define P_Volume / 'Volume' sum ' ' format=comma12.;
    define P_Revenue / 'P_Revenue' sum ' ' format=comma12. style(column)=[backgroundcolor=#F2F2F2 tagattr="FORMAT:##,##0;(##,##0)"];
	define Revenue / 'Revenue' format=comma12.2 style(column)=[backgroundcolor=#F2F2F2 tagattr="FORMAT:##,##0;(##,##0)"];
	rbreak after / summarize
        style(summary) = {font_weight=bold};
    compute after ;
        Plant = "Total";
    endcomp;

run;

my current output:

Expected output:

Thanks,

vnreddy

[Kurt_Bremser]

You need to provide some usable (DATA step with DATALINES) example data, so we get a picture what goes into PROC REPORT.

Is the dataset already cumulated per plant, or are the sums for each plant calculated in the REPORT procedure?

[vnreddy]

data test;
    input Plant $ P_Volume P_Revenue Revenue;
	informat P_Volume comma12.;
	informat P_Revenue comma12.;
    informat Revenue comma12.2;
	format P_Volume comma12.;
    format P_Revenue comma12.;
    format Revenue comma12.2;
    datalines;
A 448 53955 120.35
B 999 111863 111.95
B 1652 205770 124.54
C 436 57194 131.33
C 1744 197228 113.07
C 1591 188345 118.38
C 1425 191316 134.23
D 434 54753 126.30
I 762 87614 114.99
I 2492 288658 115.82
M 2149 245107 114.05
N 501 64550 128.74
N 880 101787 115.73
R 670 72555 108.32
S 991 113747 114.73
T 877 115802 132.10
;
run;

[Kurt_Bremser]

First of all, don't DEFINE plant as GROUP if you want the observations to stay separate. DEFINE it as DISPLAY.

Next, use the MEAN statistic to automatically calculate the average; also consider the WEIGHT= option.

Or calculate the value in the COMPUTE AFTER block.

[PaigeMiller]

Tables like this are one reason why I do the calculations in PROC SUMMARY and a DATA step, and then use PROC REPORT for simply outputting the results in a layout that I want. I don't ask PROC REPORT to do the calculations. I feel I am already very very very familiar with doing these calculations in PROC SUMMARY and/or a DATA step, it will take me 4.73 seconds to get it right. On the other hand, this certainly can be done all in PROC REPORT, but I am not as familiar with that "language" and it might take me 10 minutes of trial and error to get this right in PROC REPORT.

[vnreddy]

I will try in proc summary

[PaigeMiller · Accepted solution]

proc summary data=test;
	var p_volume p_revenue;
	output out=_test_sums_ sum=;
run;
proc format;
    value $plantf 'ZZZ'='Total';
run;
data report;
    set test _test_sums_;
    if missing(plant) then plant='ZZZ';
    revenue=p_revenue/p_volume;
    format plant $plantf.;
run;
	
proc report data=report;
    columns plant p_volume p_revenue revenue;
run;

[vnreddy]

@PaigeMiller Thank you 

[Ksharp]

/*
Once you post some data by sas code, that make things easy to code.
And you could make it happen in ONE proc report.
*/
data test;
    input Plant $ P_Volume P_Revenue Revenue;
	informat P_Volume comma12.;
	informat P_Revenue comma12.;
    informat Revenue comma12.2;
	format P_Volume comma12.;
    format P_Revenue comma12.;
    format Revenue comma12.2;
    datalines;
A 448 53955 120.35
B 999 111863 111.95
B 1652 205770 124.54
C 436 57194 131.33
C 1744 197228 113.07
C 1591 188345 118.38
C 1425 191316 134.23
D 434 54753 126.30
I 762 87614 114.99
I 2492 288658 115.82
M 2149 245107 114.05
N 501 64550 128.74
N 880 101787 115.73
R 670 72555 108.32
S 991 113747 114.73
T 877 115802 132.10
;
run;

proc report data=test split="*" nowd ;
columns Plant P_Volume P_Revenue Revenue ;
    define Plant / group 'Plant';
    define P_Volume / 'Volume' sum ' ' format=comma12.;
    define P_Revenue / 'P_Revenue' sum ' ' format=comma12. style(column)=[backgroundcolor=#F2F2F2 tagattr="FORMAT:##,##0;(##,##0)"];
	define Revenue / 'Revenue' format=comma12.2 style(column)=[backgroundcolor=#F2F2F2 tagattr="FORMAT:##,##0;(##,##0)"];
	rbreak after / summarize
        style(summary) = {font_weight=bold};
    compute after ;
        Plant = "Total"; Revenue.sum=P_Revenue.sum/P_Volume.sum;
    endcomp;

run;

[PaigeMiller]

I had a feeling that @Ksharp would jump in and provide an answer. I knew it isn't hard to do in PROC REPORT, but its still easier (to me, at least) doing the math in PROC SUMMARY/DATA step. But the good news is that there are many ways to do things in SAS.

[Cynthia_sas]

Hi:

  As others have stated, getting a sample of your input data file would be good. As it stands, now, the structure of your original data is going to determine how you generate the report you want. Here's why:

  With your current usage of GROUP for the PLANT variable, I would expect to see only 1 row for each unique value of PLANT, and this is what we see. In your desired output, you show multiple rows for PLANT B, C and N -- this is the type of report I would expect either if the usage of PLANT was ORDER or DISPLAY. Without seeing the input data for your WORK.TEST file, there's no way to "guess" the structure. I do not see any statistic or COMPUTE block for the REVENUE variable, so I uncertain whether you are calculating a value for REVENUE or just altering a value that is already in the data. At any rate, without data to test with your code, it is hard to make a constructive suggestion.

Cynthia