topic Re: add a column for the row total using proc report in SAS Procedures

add a column for the row total using proc report

BeatriceWang — Tue, 20 Sep 2022 20:39:12 GMT

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

Re: add a column for the row total using proc report

PaigeMiller — Tue, 20 Sep 2022 21:24:50 GMT

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;

Re: add a column for the row total using proc report

BeatriceWang — Tue, 20 Sep 2022 22:21:25 GMT

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?

Re: add a column for the row total using proc report

Reeza — Tue, 20 Sep 2022 22:24:23 GMT

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

Re: add a column for the row total using proc report

BeatriceWang — Tue, 20 Sep 2022 23:54:55 GMT

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;

Re: add a column for the row total using proc report

FM_MF — Wed, 21 Sep 2022 07:49:12 GMT

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

Re: add a column for the row total using proc report

PaigeMiller — Wed, 21 Sep 2022 11:37:36 GMT

@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.

Re: add a column for the row total using proc report

BeatriceWang — Wed, 21 Sep 2022 12:12:30 GMT

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;

Re: add a column for the row total using proc report

PaigeMiller — Wed, 21 Sep 2022 12:45:47 GMT

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.

Re: add a column for the row total using proc report

BeatriceWang — Wed, 21 Sep 2022 13:10:51 GMT

Thanks Miller.

It works now.

You are awesome! 😊