BookmarkSubscribeRSS Feed
CraigKaercher
Calcite | Level 5

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?

7 REPLIES 7
stat_sas
Ammonite | Level 13

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

Proc tabulate data=header;

class type account;

var charge;

table charge,sum;

run;

CraigKaercher
Calcite | Level 5

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:

stat_sas
Ammonite | Level 13

Hi,

I think following should work for the desired output.

Table account all,type*charge;

Astounding
PROC Star

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.

CraigKaercher
Calcite | Level 5

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 760 views
  • 1 like
  • 3 in conversation