topic Re: Proc transpose add grand total row in SAS Procedures

Proc transpose add grand total row

sufiya — Wed, 20 May 2020 05:44:20 GMT

Hello,

Please if someone can help me and let me know how to add a total column to the existing Proc transpose below?

thank you in advance 🙂

this is what I have :

PLAN_TYPE	11-May-20	12-May-20	13-May-20	14-May-20	15-May-20
Heating	$33.17	$0.50	$10.36	$1.00	$18.17
Cooling	$33.17	$0.50	$10.36	$1.00	$18.17

proc transpose data = Summary_One out= summary_first (drop = _Name_) ;
BY Plan_type ;
id Date ;
var CREDIT_AMT ;

run;

This is what I want, to add a new row "Total" - summing the total of the 2 rows...

PLAN_TYPE	11-May-20	12-May-20	13-May-20	14-May-20	15-May-20
Total	$66.34	$1.00	$20.72	$2.00	$36.34
Heating	$33.17	$0.50	$10.36	$1.00	$18.17
Cooling	$33.17	$0.50	$10.36	$1.00	$18.17

Re: Proc transpose add grand total row

ed_sas_member — Wed, 20 May 2020 07:49:53 GMT

Hi @sufiya

Do you want to add this summary line in a report or in the dataset?

Here are a couple of approaches to do this:

data summary_first;
	infile datalines dlm="09"x;
	input PLAN_TYPE $ '11-May-20'n:dollar8.2 '12-May-20'n:dollar8.2 '13-May-20'n:dollar8.2 '14-May-20'n:dollar8.2 '15-May-20'n:dollar8.2;
	format '11-May-20'n '12-May-20'n '13-May-20'n '14-May-20'n '15-May-20'n dollar8.2;
	datalines;
Heating	$33.17	$0.50	$10.36	$1.00	$18.17
Cooling	$33.17	$0.50	$10.36	$1.00	$18.17
;
run;

/**** Grand total as summary row in a report*/

proc print data=summary_first noobs;
	sum _numeric_;
run;

/**** Grand total as an additional row in the dataset */

proc sql;
	create table want as
	
	select * from summary_first
	
	union all corr
	
	select 'Total' as PLAN_TYPE,
		   sum('11-May-20'n) as '11-May-20'n,
		   sum('12-May-20'n) as '12-May-20'n,
		   sum('13-May-20'n) as '13-May-20'n,
		   sum('14-May-20'n) as '14-May-20'n,
		   sum('15-May-20'n) as '15-May-20'n
	from summary_first;
quit;

	/* or */

proc means data=summary_first noprint;
	var '11-May-20'n '12-May-20'n'13-May-20'n '14-May-20'n '15-May-20'n;
	class PLAN_TYPE;
	output out=_want (drop=_:) sum='11-May-20'n '12-May-20'n'13-May-20'n '14-May-20'n '15-May-20'n;
run;

data want;
	set _want;
	if missing(PLAN_TYPE) then PLAN_TYPE='Total';
run;

Best,

Re: Proc transpose add grand total row

ed_sas_member — Wed, 20 May 2020 08:01:07 GMT

Hi @sufiya

Another approach could be to use directly the initial dataset (summary_one) to issue the report:

data summary_one;
	infile datalines dlm="09"x;
	input Date:date9. PLAN_TYPE $ CREDIT_AMT:dollar8.2;
	format date date9. CREDIT_AMT dollar8.2;
	datalines;
11-May-20	Cooling	$33.17
12-May-20	Cooling	$0.50
13-May-20 	Cooling	$10.36
14-May-20	Cooling	$1.00
15-May-20	Cooling	$18.17
11-May-20	Heating	$33.17
12-May-20	Heating	$0.50
13-May-20	Heating	$10.36
14-May-20	Heating	$1.00
15-May-20	Heating	$18.17
;
run;
proc report data=summary_one nowd;
	column PLAN_TYPE Date, CREDIT_AMT;
	define PLAN_TYPE / group '';
	define Date / across '';
	define CREDIT_AMT / analysis sum '';
	rbreak after / summarize;
	
	compute after;
		if PLAN_TYPE = "" then PLAN_TYPE = 'Total';
	endcomp;
run;

Re: Proc transpose add grand total row

ed_sas_member — Wed, 20 May 2020 08:05:02 GMT

... or to use PROC TABULATE:

data summary_one;
	infile datalines dlm="09"x;
	input Date:date9. PLAN_TYPE $ CREDIT_AMT:dollar8.2;
	format date date9. CREDIT_AMT dollar8.2;
	datalines;
11-May-20	Cooling	$33.17
12-May-20	Cooling	$0.50
13-May-20 	Cooling	$10.36
14-May-20	Cooling	$1.00
15-May-20	Cooling	$18.17
11-May-20	Heating	$33.17
12-May-20	Heating	$0.50
13-May-20	Heating	$10.36
14-May-20	Heating	$1.00
15-May-20	Heating	$18.17
;
run;

proc tabulate data=summary_one;
	class PLAN_TYPE Date;
	var CREDIT_AMT;
	tables PLAN_TYPE='' ALL='Total', Date=''*CREDIT_AMT=''*sum=''*f=dollar8.2;
run;

Re: Proc transpose add grand total row

sufiya — Wed, 20 May 2020 16:38:47 GMT

Hi @ed_sas_member - thank you so much 🙂 Greatly appreciate your guidance!!
Proc report worked perfectly.
I made one small change to get the "Total" to the top row :
1) rbreak after => rbreak before
2) compute after => compute before

proc report data=summary_one nowd;
column PLAN_TYPE Date, CREDIT_AMT;
define PLAN_TYPE / group '';
define Date / across '';
define CREDIT_AMT / analysis sum '';
rbreak before / summarize;

compute before;
if PLAN_TYPE = "" then PLAN_TYPE = 'Total';
endcomp;
run;

Re: Proc transpose add grand total row

sufiya — Wed, 20 May 2020 19:08:43 GMT

Hi @ed_sas_member ,

small problem, I am using this code=>

proc tabulate data=summary_one;
	class PLAN_TYPE Date;
	var CREDIT_AMT;
	tables PLAN_TYPE='' ALL='Total', Date=''*CREDIT_AMT=''*sum=''*f=dollar8.2;
run;

which results in this output :

########

13-May-20

########

15-May-20

Total

$33.17

$0.50

$10.36

$1.00

$18.17

Heating

$33.17

$0.50

$10.36

$1.00

$18.17

but when I setup an email output I lose the above format with total and it reverts back to the input query. As below:

ODS HTML BODY=MY_FILE STYLE=meadowprinter;

title "Service/Product Summary";
Proc report data=WORK.Summary_One;
run;

ODS HTML CLOSE;
quit;

Heating/Cooling Summary
JOUR_DTM	PLAN_TYPE	CREDIT_AMT
11-May-20	Heating	$33.17
12-May-20	Heating	$0.50
13-May-20	Heating	$10.36
14-May-20	Heating	$1.00
15-May-20	Heating	$18.17

Please can you let me know what the work around is??

thank you!! 😁

Re: Proc transpose add grand total row

sufiya — Wed, 20 May 2020 19:17:12 GMT

nm figured it out 😛