Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- add a column for the row total using proc report

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-20-2022 04:39 PM
(669 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--

Paige Miller

Paige Miller

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Miller.

It works now.

You are awesome! 😊

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.