topic Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't? in SAS Programming

Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

CraigKaercher — Mon, 18 Aug 2014 20:06:38 GMT

I am trying to do a simple TABULATE on an external data set on the IBM mainframe. If I use the REPORT proc, the dollar amounts are reported correctly. However if I try to sum up the dollar amounts using TABULATE, the sum is incorrect. I have tried this twice. Here are the control statements:

Reports(work)

TABULATE(doesn't work)

The number as it appears in the file looks like +00000100.00

Why does the report come out correct but the summing does not?

Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

stat_sas — Mon, 18 Aug 2014 20:15:29 GMT

Not using charge variable for analysis in table statement. Try this.

Proc tabulate data=header;

class type account;

var charge;

table charge,sum;

run;

Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

Astounding — Mon, 18 Aug 2014 20:30:27 GMT

One possibility: Without the MISSING option, PROC TABULATE automatically removes any observation that has a missing value for a CLASS variable. So it could be throwing out observations before computing the sum.

Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

CraigKaercher — Tue, 19 Aug 2014 16:10:31 GMT

Thanks for the tip. I can report the numbers correctly using TABULATE, However, still having a little trouble. I would like my result table to look like below(or closest SAS equivalent):

____________________________________________________________________

| | CHARGE |

____________________________________________________________________

| | TYPE |

____________________________________________________________________

| | CHG | TAX | SUM |

___________________________________________________________________

| ACCOUNT | |

____________________________________________________________________

| 0000000001 | +100.00 | -110.00 | -10.00 |

____________________________________________________________________

| 0000000002 | +100.00 | +110.00 | +210.00 |

____________________________________________________________________

| 0000000003 | -100.00 | -110.00 | -210.00 | |

______________________________________________________

Given the following data(and assuming summing would work correctly if more than one line per acct/type):

I can kind of get what I want using below, but each account seems to get a separate table and the types line up vertically:

Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

CraigKaercher — Tue, 19 Aug 2014 16:12:13 GMT

Thanks for the tip. I don't think that's it but I will remember it for the future.

Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

stat_sas — Tue, 19 Aug 2014 16:25:00 GMT

Hi,

I think following should work for the desired output.

Table account all,type*charge;

Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

Astounding — Tue, 19 Aug 2014 16:25:37 GMT

It looks like your TABLE statement needs some work. This might be closer to what you need:

table account all, charge*type*sum=' ' charge*sum;

The comma is a key symbol in a TABLE statement. It means the definition of one dimension has ended and the definition of a new dimension begins. So with 2 commas, the first dimension is the page dimension, the second is the row dimension, and the third is the column dimension.

Re: Why will the Report Proc Report A Number Correctly While Proc Tabulate Won't?

CraigKaercher — Wed, 20 Aug 2014 20:03:12 GMT

That got it! Thank you very much!