PROC TABULATE WITH TOTAL

Question

I am doing reports with proc tabulate, but unable to add total in a report.

Example

+--------+------+----------+--------+---+---+---+
|  Shop  | Year |  Month   | Family | A | B | C |
+--------+------+----------+--------+---+---+---+
| raoas  | 2006 | january  | TA12   | 5 | 6 | 0 |
| taba   | 2008 | january  | TS01   | 0 | 1 | 1 |
| suptop | 2008 | april    | TZ05   | 0 | 0 | 1 |
| taba   | 2006 | December | TA12   | 5 | 6 | 0 |
| raoas  | 2008 | january  | TA15   | 0 | 2 | 0 |
| sup    | 2008 | april    | TQ05   | 0 | 1 | 1 |
+--------+------+----------+--------+---+---+---+

code

proc tabulate data=REPORTDATA_T6 format=12.;
            CLASS YEAR;
            var A  C;
            table  (A  C)*SUM='',YEAR=''
            /box = 'YEAR';
        TITLE 'FORECAST SUMMARY';
  run;

output

YEAR    2006 2008 2009
A       800 766 813
C       854 832 812

I tried with... table(A C)*sum,year all... it will sum up for all the years but I want by year. I tried with all the possible ways and tried... table(A C)*sum all,year. It will give number of observations ie N.. Thanx JON CLEMENTS But I dont want to add as TOTAL VARIABLE in the table, becoz this is a sample data but the number of variables are more then 10, some time I need to change variables, So, every time i dont want to add new variable as total.

No, I am not getting what I want. Its giving sum(A) ,sum(B). — Kavitha
I would have expected someone would have answered this by now, but I'll give up pestering you... It's been about 10 years since I've used SAS and for the life of me I can't remember how to do this... (although it is straight-forward) and I don't have SAS anymore... good luck — Jon Clements

Dmitry Shopin Dmitry Shopin · Accepted Answer · 2014-03-05T18:51:39

I'm not sure if it's possible to do what you want in one step using only original data. Keyword ALL works only for summing up categories of CLASS-variables, but you want to sum up two different variables.

But it's easy enough with interim step, creating dataset where A, B, C variables will become categories of one variable:

data REPORTDATA_T6;
    input Shop $ Year Month $ Family $ A B C;
datalines;
raoas 2006 january TA12 5 6 0
taba 2008 january TS01 0 1 1
suptop 2008 april TZ05  0 0  1
taba 2006 December TA12 5 6 0
raoas 2008 january TA15 0 2 0
sup 2008 april TQ05 0 1 1
;
run;
proc sort data=REPORTDATA_T6; by Shop Year Month Family; run;

proc transpose data=REPORTDATA_T6 out=REPORTDATA_T6_long;
    var A B C;
    by Shop Year Month Family;
run;

proc tabulate data=REPORTDATA_T6_long;
    class _NAME_  YEAR;
    var COL1;
    table  (_NAME_ all)*COL1=' '*SUM=' ', YEAR=' '
    /box = 'YEAR';
    TITLE 'FORECAST SUMMARY';
run;

PROC TABULATE WITH TOTAL

1 Answers