topic Re: proc report add total of amount in the header in SAS Procedures

proc report add total of amount in the header

Nasser_DRMCP — Thu, 09 Nov 2017 13:55:21 GMT

hello,

this is a small


data have ;
input produit $ generation periode  measure $ value FLAG_REAL_PREV $ ;
datalines ;
A 2008 201710 nborder 810 R
A 2008 201710 sales 801000 R
A 2008 201709 nborder 809 R
A 2008 201709 sales 80900 R
A 2009 201710 nborder 910 R
A 2009 201710 sales 901000 R
A 2009 201709 nborder 909 R
A 2009 201709 sales 90900 R
A 2008 201710 nborder 8102 P
A 2008 201710 sales 8010002 P
A 2008 201709 nborder 8092 P
A 2008 201709 sales 809002 P
A 2009 201710 nborder 9102 P
A 2009 201710 sales 9010002 P
A 2009 201709 nborder 9092 P
A 2009 201709 sales 909002 P
;

proc report data= have ;
column produit generation measure periode , FLAG_REAL_PREV , value ;
define PRODUIT / group '' ;
	define generation / group order =  DATA ; 
	define measure / group '' style(column)={CELLWIDTH = 2.5in} style(header)={CELLWIDTH = 2.5in} format= $f_measure_label. ;
	define periode / across '' ;
	define FLAG_REAL_PREV / '' across  ;
		define value / '' ;
Run ;

dataset with dummy datas. And this is my proc report.

what I need is to add a total of the key figure "sales" and for columns "R" (real) and "P" (forecast).

thanks a lot in advance for your help

Nasser

Re: proc report add total of amount in the header

PaigeMiller — Thu, 09 Nov 2017 14:20:50 GMT

I would run PROC SUMMARY which creates the totals and the amounts in each cell.

Then I would use PROC REPORT to create the table from the PROC SUMMARY output.

Re: proc report add total of amount in the header

Nasser_DRMCP — Thu, 09 Nov 2017 14:39:26 GMT

Thanks Page,

I succeeded to create a new dataset that hold a column "valueTotal" (via proc summary)

but I don't know how to display it in the header of the report (via proc report)

thanks

Re: proc report add total of amount in the header

PaigeMiller — Thu, 09 Nov 2017 15:41:03 GMT

I don't know what you mean by "in the header"

To me, that's just the first row of the table.

If you did PROC SUMMARY properly, it does the calculations, including the "header" sum, and then PROC REPORT just takes the output from PROC SUMMARY and formats into a nice readable table.

Here's a simple example using SASHELP.CLASS

proc summary data=sashelp.class;
    class sex age;
	var height;
	output out=sums sum=sum_height;
run;

data sums;
	set sums;
	if missing(sex) then sex="A";
	if missing(age) then age=-999;
run;

proc format;
	value $sexf "A"="All";
	value agef -999="All";
run;

proc report data=sums;
	column sex age,sum_height;
	define sex/group order=internal format=$sexf.;
	define age/across order=internal format=agef.;
	define sum_height/analysis sum;
run;

Re: proc report add total of amount in the header

Nasser_DRMCP — Thu, 09 Nov 2017 16:56:19 GMT

Ok Paige

My need was not clearly specified. please take a look at my screen shot

I would like to obtain the detail (yellow ) and the total (in red)

thanks Paige

Re: proc report add total of amount in the header

Nasser_DRMCP — Thu, 09 Nov 2017 16:58:13 GMT

Re: proc report add total of amount in the header

PaigeMiller — Thu, 09 Nov 2017 18:04:59 GMT

My code produces a similar report using SASHELP.CLASS, you have to modify it for your exact needs.

Re: proc report add total of amount in the header

Nasser_DRMCP — Fri, 10 Nov 2017 09:16:32 GMT

Hello Paige,

thanks !

I have many products A, B and C. is it possible to have a sub total by product ?

thanks