add a column for the row total using proc report

[BeatriceWang]

Dear Fellow SAS Procedure Users,

I have below proc report code I would like to produce the total for each row and append it to the "All Combined" column. For some reason I got blanks for the total. I would like you to advise where my code is wrong and how to fix it. Apologize if this is obvious.

Thanks in advance,

Beatrice

[PaigeMiller]

No data to test this on, so this is UNTESTED CODE

From now on, please provide code as text rather than screen capture, and by pasting it into the box that appears when you click on the "little running man" icon, like this:

proc report data=ds1;
    column idx label (var1,stat) stat=sumstat;
/* Your DEFINE statements go here, plus another DEFINE statement */
/* Do not include DEFINE statement for N */
    define sumstat/sum f=comma18.0 "All Combined";
run;

[BeatriceWang]

Thank you Miller for your suggestion.

I tried but I got the following error:

Please note stat itself is a variable in my input dataset and it is the sum I got from proc means.

Any further suggestion?

[Reeza]

Output from proc means should be numeric. Show your work please. As previously mentioned, not as images.

[BeatriceWang]

Thank you Reeza for your reply.

Here is my original code in text format. Please kindly further suggest.

ods results=off;
filename fout '/C/test.xls';
ods excel file=fout options(embedded_titles='yes') style=Excel;
ods excel options(sheet_name='Table9' absolute_column_width='21' flow='tables' frozen_headers='3');
ods listing close;
proc report data=ds1 split='~';
column idx label (var1, stat) n;
define idx / noprint group;
define label / '' group left;
define var1 / '' across nozero order=internal format=anagrpnfmt.;
define stat / '' center;
define n/computed f=comma18.0 "ALL Combined";
compute n;
n=sum(stat);
endcomp;

run;

ods excel close;
filename fout clear;
title;
ods results;

[PaigeMiller]

---

@BeatriceWang wrote:

Thank you Miller for your suggestion.

I tried but I got the following error:

Please note stat itself is a variable in my input dataset and it is the sum I got from proc means.

Any further suggestion?

---

STAT is not numeric. You cannot create totals from variables that are not numeric. Your claim that is it numeric is not correct. Show us the ENTIRE LOG from before PROC REPORT of the part of your code that creates the data set DS1.

Please STOP showing us screen captures of text. Text, whether code or log, MUST be presented as text, using the "little running man" icon or the </> icon (to the left of the "little running man"), respectively.

[BeatriceWang]

Hi Miller,
Below is my sas code with some changes from the suggestions. The total is still blank. 
Yes. My code defined stat to character type. 
But I am not sure how to fix it. Can you please suggest a solution?

proc means data=ds0 sum stackodsoutput;
     class var1;
var tot_phys_ov_cnt tot_ov_pts tot_hv_pts tot_all_phys_cnt tot_all_pts_cnt; 
     ods output summary=ds1(drop=_:);
run;

data colfmt;
    set ds1;
	by var1 notsorted;

	keep fmtname start label;

	fmtname = 'anagrpnfmt';
	if first.var1 then do;
        start = var1;
		label = cats(put(var1, anagrpnfmt.),'~(N=', put(nobs, comma5.0), ')');
		output;
	end;

run;

proc format cntlin=colfmt;
run;

data ds1;
    set ds1;
	by var1 notsorted;

	label = compress(label, '|');

	if first.var1 then idx = 0;
	idx + 1;
    
	length stat $32;
	if not missing(sum) 
    then stat = strip(put(sum, 12.0)); 

run;

ods results=off;
filename fout '/c/test.xls';
ods excel file=fout options(embedded_titles='yes') style=Excel;
ods excel options(sheet_name='Table9' absolute_column_width='21' flow='tables' frozen_headers='3');
title j=left h=13pt 'test';
ods listing close;
proc report data=ds1 split='~';
    column idx label (var1, stat) n;
	define idx / noprint group;
	define label / '' group left;
	define var1 / '' across nozero order=internal format=anagrpnfmt.;
	define stat / '' center;
define n/computed f=comma18.0 "ALL Combined";
	compute n;
	n=sum(stat);
	endcomp;
run;

ods excel close;
filename fout clear;
title;
ods results;

[PaigeMiller · Accepted solution]

Here:

data ds1;
    set ds1;
	by var1 notsorted;
	label = compress(label, '|');
	if first.var1 then idx = 0;
	idx + 1;  
	length stat $32;
	if not missing(sum) 
    then stat = strip(put(sum, 12.0)); 

run;

You have defined STAT as $32 which is character. Than later you try to assign it a character value from a numeric variable named SUM. Just use the numeric variable named SUM.

[BeatriceWang]

Thanks Miller.

It works now.

You are awesome! 😊

[FM_MF]

Agree with @PaigeMiller.

I supply some additional information about your code.

According to your think, you don't need to add a new computed column .

proc report data=ds1 split='~';
	column idx label (var1, stat) stat=n;
	define idx / noprint group;
	define label / '' group left;
	define var1 / '' across nozero order=internal format=anagrpnfmt.;
	define stat / '' center;
	define n / '' sum f=comma18.0 "ALL Combined";
run;

P.S. The error you got is that you can't use "sum()", you should key "n=stat.sum".

Hope this helps.

Thanks,

Fred