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

Reply
Occasional Contributor
Posts: 13

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

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?

Trusted Advisor
Posts: 1,203

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

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

Proc tabulate data=header;

class type account;

var charge;

table charge,sum;

run;

Occasional Contributor
Posts: 13

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

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:

Trusted Advisor
Posts: 1,203

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

Hi,

I think following should work for the desired output.

Table account all,type*charge;

Respected Advisor
Posts: 4,973

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

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.

Occasional Contributor
Posts: 13

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

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

Respected Advisor
Posts: 4,973

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

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.

Occasional Contributor
Posts: 13

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

That got it!  Thank you very much!

Ask a Question
Discussion stats
  • 7 replies
  • 413 views
  • 1 like
  • 3 in conversation