Hi all,
I'm am putting together a proc report for output to ODS Excel, which contains several computed fields, for example, "Price per Tonne", which as you can probably guess, is "Revenue" divided by "Tonnes".
I want to replicate this in a summary row on my report, but using this:
* Grand totals;
rbreak after / summarize style(summary)={font_weight=bold backgroundcolor=gwh color=firebrick};
Gives a total of the "Price per Tonne" column, and not the computation of Total Revenue / Total Tonnes.
Is there an option to continue use of the computed field into the summary/grand total row?
Thanks
Jim
data have;
infile cards expandtabs ;
input Region & $20. Revenue Tonnes;
cards;
Region A 2033.9 171024.7
Region B 7236.7 626710.71
;
proc report data=have nowd;
column Region Revenue Tonnes want;
define Region/display;
define Revenue/analysis sum;
define Tonnes/analysis sum;
define want/computed;
compute want;
want=Tonnes.sum/ Revenue.sum;
endcomp;
compute after;
Region='Total';
endcomp;
rbreak after/summarize;
run;
Sure Kevin:
Region | Revenue | Tonnes | Have | Want |
Region A | 2033.9 | 171024.7 | 84.1 | 84.1 |
Region B | 7236.7 | 626710.71 | 86.6 | 86.6 |
Total | 9270.6 | 797735.41 | 170.7 | 86.1 |
In the above example, the 84,1 and 86.6 values are being computed. The "Have" column, shows my current output, the "Want" shows the desired output, which in Excel terms would be a copy-paste of the formula in the cell above.
data have;
infile cards expandtabs ;
input Region & $20. Revenue Tonnes;
cards;
Region A 2033.9 171024.7
Region B 7236.7 626710.71
;
proc report data=have nowd;
column Region Revenue Tonnes want;
define Region/display;
define Revenue/analysis sum;
define Tonnes/analysis sum;
define want/computed;
compute want;
want=Tonnes.sum/ Revenue.sum;
endcomp;
compute after;
Region='Total';
endcomp;
rbreak after/summarize;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.