BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
vnreddy
Quartz | Level 8

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:

vnreddy_0-1712458615579.png

 

Expected output:

vnreddy_1-1712458750661.png

 

Thanks,

vnreddy

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

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
Quartz | Level 8

 

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
Super User

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
Diamond | Level 26

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.

--
Paige Miller
vnreddy
Quartz | Level 8

I will try in proc summary

PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
vnreddy
Quartz | Level 8

 

@PaigeMiller Thank you 

Ksharp
Super User
/*
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;

Ksharp_0-1712543468867.png

 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1712593949873.png

  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

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 2249 views
  • 2 likes
  • 5 in conversation