topic Proc Report: Computed fields in "Grand Total" in SAS Programming

Proc Report: Computed fields in "Grand Total"

Jim_Ogilvie — Mon, 29 Jun 2020 10:53:41 GMT

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

Re: Proc Report: Computed fields in "Grand Total"

Ksharp — Mon, 29 Jun 2020 11:15:12 GMT

Can you give an example ?
Try

proc report data=sashelp.class nowd;
column name weight;
define name/display;
define weight/analysis sum;
compute after;
name='xxxx';
endcomp;
rbreak after/summarize;
run;

Re: Proc Report: Computed fields in "Grand Total"

Jim_Ogilvie — Mon, 29 Jun 2020 11:38:54 GMT

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.

Re: Proc Report: Computed fields in "Grand Total"

Ksharp — Mon, 29 Jun 2020 11:59:33 GMT

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;